pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCursor with hold for select
2+ messages / 2 participants
[nested] [flat]
* Cursor with hold for select
@ 2024-03-22 16:24 Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bindra Bambharoliya @ 2024-03-22 16:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Cursor with hold for select
@ 2024-03-23 13:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-03-23 13:33 UTC (permalink / raw)
To: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-03-23 13:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-22 16:24 Cursor with hold for select Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
2024-03-23 13:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
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