On 7/1/21 10:25 PM, Ayub Khan wrote: > Justin, > > Below is the stored procedure, is there any scope for improvement? > Hard to say, based on just the stored procedure source code. The queries are not too complex, but we don't know which of them gets selected for each cursor, and which of them is the slow one. I suggest you identify which of the cursors is the most problematic one, and focus on investigating it alone. Show us the explain analyze for that query with different cursor_tuple_fraction values and without the cursort, and so on. As Tom said, for a cursor the optimizer may be picking a plan with low startup cost, on the basis that that's good for a cursor. But if you're always consuming all the tuples, that may be inefficient. It's often an issue for queries with LIMIT, but none of the queries you include that clause, so who knows ... Try identifying which of the cursors is causing the issues, show us the explain analyze for that query (with and without the cursor), and that should tell us more. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
