From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Cursor with hold for select
Date: Sat, 23 Mar 2024 14:33:39 +0100
Message-ID: <ea2d4ef840e4288ce87331300d3eaf492e34e841.camel@cybertec.at> (raw)
In-Reply-To: <CAPLVUMAwOBS1aB6h78bTBP+2EBB=Yq1ape2JsViRSV4tJGPSnQ@mail.gmail.com>
References: <CAPLVUMAwOBS1aB6h78bTBP+2EBB=Yq1ape2JsViRSV4tJGPSnQ@mail.gmail.com>
On Fri, 2024-03-22 at 21:54 +0530, Bindra Bambharoliya wrote:
> I am facing issue with postgresql 11.17 version.
> When I use normal select it executes within 15 seconds.
> When use same query with begin; cursor "sql1" hold for select....
> Fetch 100 to "sql1" it takes 3 minutes and sometime not executing even in 10 minutes. And I need to kill it.
> What could be the root cause. And how can we over one form this.
The reason could be that PostgreSQL plans statements in a cursor so that
the first 10% of the result are returned as fast as possible, rather than
for the shortest time to retrieve the whole result set.
But WITH HOLD cursors are materialized at COMMIT, so the whole result set
gets calculated.
You may experience better performance if you set "cursor_tuple_fraction = 1.0".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, bindra.bambharoliya@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Cursor with hold for select
In-Reply-To: <ea2d4ef840e4288ce87331300d3eaf492e34e841.camel@cybertec.at>
* 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