Re: Will PQsetSingleRowMode get me results faster?

On Fri, 2025-04-04 at 13:41 +0200, Stijn Sanders wrote:
> On Mon, Jan 6, 2025 at 9:06 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > So in principle, you might get best results by defining your query
> > with DECLARE CURSOR and then using PQsetSingleRowMode on the FETCH.
> > But it'd really depend on the particular query whether this gives
> > any benefit.
>
> That's a really nice suggestion, and it took me some time to set up a
> suitable test environment to see if it would work, but using separate
> PQsendquery/PQexec calls for:
>
> start transaction read only
> declare cr1 no scroll cursor for select (and the rest of my query)
> fetch next in cr1
>
> it seems like the fetch instruction still takes about as much time as
> the full 'normal' select would, I tried a few different queries, but
> I'm still suspecting PostgreSQL's internals is waiting for the data to
> all 'be ready' before it can send any data over, even if these
> PQgetResult's for each fetch will have a PQntuples of 1.
> (I even tried with "fetch 8", and PQntuples neatly serves 8 at a time,
> but still after about the same time PQsendquery(,'select... would
> take)
> Or could there still be something that I'm doing that prevents 'firehosing'?

That looks like you are doing everything right, but there just isn't
a "fast start" execution plan, and calculating the first row already
is taking a lot of time.

Perhaps you can tell PostgreSQL to optimize for a fast start plan more
aggressively by lowering "cursor_tuple_fraction" inside the transaction:

SET LOCAL cursor_tuple_fraction = 0.001;

Yours,
Laurenz Albe