I did some more testing. I initialized a database with 1 million rows with indexes and joins to test against and ran pgbench with a few different settings for % to return. I started with a base query not utilizing the new functionality. The queries used are similar to my prior examples, code at [1].
createdb bench_test
psql -d bench_test -f init/reporting.sql -v scale=10
The following provided 3.21 TPS and an average latency of 623. The "per_change_" columns in the table below use those values.
pgbench -c 2 -j 2 -T 600 -P 60 -s 10 \
-f tests/reporting1.sql bench_test
The remainder of the tests use the following, only adjusting fetch_percent value:
pgbench -c 2 -j 2 -T 600 -P 60 -s 10 \
--define=fetch_percent=1 \
-f tests/reporting_fetch_percent.sql \
bench_test
Returning 1% it runs well. By 10% the TPS drops by 30% while the average latency increases by 43%. When returning 95% of the table latency has increased by 548%.
fetch_percent | tps | latency_avg_ms | per_change_tps | per_change_latency
---------------+------+----------------+----------------+--------------------
1 | 3.37 | 593 | 0.05 | -0.05
5 | 2.85 | 700 | -0.11 | 0.12
10 | 2.24 | 891 | -0.30 | 0.43
25 | 1.40 | 1423 | -0.56 | 1.28
45 | 0.93 | 2147 | -0.71 | 2.45
95 | 0.49 | 4035 | -0.85 | 5.48
I manually tested the inner select queries without the outer aggregation thinking it might be a different story with a simple select and no CTE. Unfortunately it showed the same overall characteristics. 1% returns in about 550 ms, 45% took 1950, and 95% took 4050.
[1]
https://github.com/rustprooflabs/pgbench-tests
