|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
|Next Messagefirstname.lastname@example.org||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|