MIME-Version: 1.0
References: <140214eb-a8ce-4285-afdd-7770c5a9d274@manitou-mail.org> <1335670.1736193981@sss.pgh.pa.us>
In-Reply-To: <1335670.1736193981@sss.pgh.pa.us>
From: Stijn Sanders <stijnsanders@gmail.com>
Date: Fri, 4 Apr 2025 13:41:17 +0200
Message-ID: <CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV=y0_XRTP_cUg9ZoUYx5cLA@mail.gmail.com>
Subject: Re: Will PQsetSingleRowMode get me results faster?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Verite <daniel@manitou-mail.org>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHYFsWXPuyzGQ8fWwB8hCJTyMcbV%3Dy0_XRTP_cUg9ZoUYx5cLA%40mail.gmail.com>
Precedence: bulk

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.

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'=
?