Message-ID: <589a474cc1f5e804bad53467238bb15187ba38a4.camel@cybertec.at>
Subject: Re: Will PQsetSingleRowMode get me results faster?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Stijn Sanders <stijnsanders@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Verite <daniel@manitou-mail.org>, 
	pgsql-general@lists.postgresql.org
Date: Mon, 07 Apr 2025 10:14:14 +0200
In-Reply-To: <CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV=y0_XRTP_cUg9ZoUYx5cLA@mail.gmail.com>
References: <140214eb-a8ce-4285-afdd-7770c5a9d274@manitou-mail.org>
	 <1335670.1736193981@sss.pgh.pa.us>
	 <CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV=y0_XRTP_cUg9ZoUYx5cLA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/589a474cc1f5e804bad53467238bb15187ba38a4.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-04-04 at 13:41 +0200, Stijn Sanders wrote:
> On Mon, Jan 6, 2025 at 9:06=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.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.
>=20
> 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:
>=20
> start transaction read only
> declare cr1 no scroll cursor for select (and the rest of my query)
> fetch next in cr1
>=20
> 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 'firehosin=
g'?

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 =3D 0.001;

Yours,
Laurenz Albe