On Fri, 2025-04-04 at 13:41 +0200, Stijn Sanders wrote: > On Mon, Jan 6, 2025 at 9:06 PM Tom Lane <t...@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'?
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
