public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Stijn Sanders <[email protected]>
To: Tom Lane <[email protected]>
Cc: Daniel Verite <[email protected]>
Cc: [email protected]
Subject: Re: Will PQsetSingleRowMode get me results faster?
Date: Mon, 07 Apr 2025 10:14:14 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV=y0_XRTP_cUg9ZoUYx5cLA@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV=y0_XRTP_cUg9ZoUYx5cLA@mail.gmail.com>

On Fri, 2025-04-04 at 13:41 +0200, Stijn Sanders wrote:
> On Mon, Jan 6, 2025 at 9:06 PM Tom Lane <[email protected]> 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

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Will PQsetSingleRowMode get me results faster?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox