From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Ayub Khan <ayub.hp@gmail.com>
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: slow performance with cursor
Date: Fri, 2 Jul 2021 16:30:13 +0200
Message-ID: <7a75ebb8-b5bc-81aa-ba9a-7c17abdeb706@enterprisedb.com> (raw)
In-Reply-To: <CAHdeyEJhf6+G7Q+hKLrWkhgCiw4ue4SwK165-DzFhvwKqggpPA@mail.gmail.com>
References: <CAHdeyE+0tG-TbUhfznXMu4cycK2U13zTa-qw-fZ7_PBQ_QW69g@mail.gmail.com>
<CAHdeyEJhf6+G7Q+hKLrWkhgCiw4ue4SwK165-DzFhvwKqggpPA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, ayub.hp@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: slow performance with cursor
In-Reply-To: <7a75ebb8-b5bc-81aa-ba9a-7c17abdeb706@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox