Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jakub Wartak <jakub(dot)wartak(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: psql's FETCH_COUNT (cursor) is not being respected for CTEs
Date: 2023-01-12 12:27:32
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Tom Lane wrote:

> I agree that it seems like a good idea to try.
> There will be more per-row overhead, but the increase in flexibility
> is likely to justify that.

Here's a POC patch implementing row-by-row fetching.

If it wasn't for the per-row overhead, we could probably get rid of
ExecQueryUsingCursor() and use row-by-row fetches whenever
FETCH_COUNT is set, independently of the form of the query.

However the difference in processing time seems to be substantial: on
some quick tests with FETCH_COUNT=10000, I'm seeing almost a 1.5x
increase on large datasets. I assume it's the cost of more allocations.
I would have hoped that avoiding the FETCH queries and associated
round-trips with the cursor method would compensate for that, but it
doesn't appear to be the case, at least with a fast local connection.

So in this patch, psql still uses the cursor method if the
query starts with "select", and falls back to the row-by-row in
the main code (ExecQueryAndProcessResults) otherwise.
Anyway it solves the main issue of the over-consumption of memory
for CTE and update/insert queries returning large resultsets.

Best regards,

Daniel Vérité
Twitter: @DanielVerite

Attachment Content-Type Size
psql-fetchcount-single-row-mode.diff text/x-patch 11.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message 2023-01-12 12:34:05 RE: Perform streaming logical transactions by background workers and parallel apply
Previous Message Bharath Rupireddy 2023-01-12 12:07:40 Re: Add a new pg_walinspect function to extract FPIs from WAL records