Hi,
Here's a new version to improve the performance of FETCH_COUNT
and extend the cases when it can be used.
Patch 0001 adds a new mode in libpq to allow the app to retrieve
larger chunks of results than the single row of the row-by-row mode.
The maximum number of rows per PGresult is set by the user.
Patch 0002 uses that mode in psql and gets rid of the cursor
implementation as suggested upthread.
The performance numbers look good.
For a query retrieving 50M rows of about 200 bytes:
select repeat('abc', 200) from generate_series(1,5000000)
/usr/bin/time -v psql -At -c $query reports these metrics
(medians of 5 runs):
version | fetch_count | clock_time | user_time | sys_time | max_rss_size
(kB)
-----------+-------------+------------+-----------+----------+-------------------
16-stable | 0 | 6.58 | 3.98 | 2.09 |
3446276
16-stable | 100 | 9.25 | 4.10 | 1.90 |
8768
16-stable | 1000 | 11.13 | 5.17 | 1.66 |
8904
17-patch | 0 | 6.5 | 3.94 | 2.09 |
3442696
17-patch | 100 | 5 | 3.56 | 0.93 |
4096
17-patch | 1000 | 6.48 | 4.00 | 1.55 |
4344
Interestingly, retrieving by chunks of 100 rows appears to be a bit faster
than the default one big chunk. It means that independently
of using less memory, FETCH_COUNT implemented that way
would be a performance enhancement compared to both
not using it and using it in v16 with the cursor implementation.
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite