Thread: Cursor with hold for select
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.
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