Tom Lane wrote: > > I should say that I've noticed significant latency improvements with > > FETCH_COUNT retrieving large resultsets, such that it would benefit > > non-interactive use cases. > > Do you have a theory for why that is? It's pretty counterintuitive > that it would help at all.
I've been thinking that it's a kind of pipeline/parallelism effect. When libpq accumulates all rows in one resultset, if the network or the server are not fast enough, it spends a certain amount of time waiting for the data to come in. But when it accumulates fewer rows and gives back control to the app to display intermediate results, during that time the network buffers can fill in, resulting, I assume, in less time waiting overall. I think the benefit is similar to what we get with \copy. In fact with the above-mentioned test, the execution times with FETCH_COUNT=1000 look very close to \copy of the same query. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
