Re: Cursor with hold for select

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