Re: benchmark results comparing versions 15.2 and 16

I ran sysbench on Postgres 15.2, 15.3 and 16 prebeta at git sha 1c006c0
(built on May19).
The workload was in-memory on a small server (8 cores, 16G RAM) and the
workload had 1 connection (no concurrency).
For some details on past benchmarks like this see:
http://smalldatum.blogspot.com/2023/03/searching-for-performance-regressions.html

My focus is on changes >= 10%, so a value <= 0.90 or >= 1.10.
I used 3 builds of Postgres that I call def, o2_nofp, o3_nofp and ran the
benchmark once per build. The results for each build are similar
and I only share the o2_nofp results there.

Good news, that I have not fully explained ...

One of the microbenchmarks gets ~1.5X more transactions/second (TPS) in PG
16 prebeta vs 15.2 and 15.3 for a read-only transaction that does:
* 2 select statements that scans 10k rows from an index (these are Q1 and
Q3 below and are slower in PG 16)
* 2 select statements that scans 10k rows from an index and does
aggregation (these are Q2 and Q4 below are are a lot faster in PG 16)

The speedup for Q2 and Q4 is larger than the slowdown for Q1/Q3 so TPS is
~1.5X more for PG 16.
Query plans don't appear to have changed. I assume some code got slower and
some got faster for the same plan.

The microbenchmarks are read-only_range=10000 and read-only.pre_range=10000
show.
Each of these microbenchmarks run a read-only transaction with 4 SQL
statements. The statements are here:
https://github.com/mdcallag/mytools/blob/master/bench/sysbench.lua/lua/oltp_common.lua#LL301C1-L312C21

read-only.pre_range runs before a large number of writes, so the b-tree
will be more read-friendly.
read-only.range runs after a large number of writes.

The =10000 means that each SQL statement processes 10000 rows. Note that
the microbenchmarks are also run for =100 and =10
and for those the perf with PG16 is similar to 15.x rather than ~1.5X
faster.

---

This table shows throughput relative to the base case. The base case is PG
15.2 with the o2_nofp build.
Throughput relative < 1.0 means perf regressed, > 1.0 means perf improved

col-1 : PG 15.3 with the o2_nofp build
col-2 : PG 16 prebeta build on May 19 at git sha 1c006c0 with the o2_nofp
build

col-1 col-2
0.99 1.03 hot-points_range=100
1.02 1.05 point-query.pre_range=100
1.06 1.10 point-query_range=100
0.97 1.01 points-covered-pk.pre_range=100
0.98 1.02 points-covered-pk_range=100
0.98 1.01 points-covered-si.pre_range=100
1.00 1.00 points-covered-si_range=100
1.00 1.01 points-notcovered-pk.pre_range=100
1.00 1.01 points-notcovered-pk_range=100
1.01 1.03 points-notcovered-si.pre_range=100
1.01 1.01 points-notcovered-si_range=100
1.00 0.99 random-points.pre_range=1000
1.00 1.02 random-points.pre_range=100
1.01 1.01 random-points.pre_range=10
1.01 1.00 random-points_range=1000
1.01 1.01 random-points_range=100
1.02 1.01 random-points_range=10
1.00 1.00 range-covered-pk.pre_range=100
1.00 1.00 range-covered-pk_range=100
1.00 0.99 range-covered-si.pre_range=100
1.00 0.99 range-covered-si_range=100
1.03 1.01 range-notcovered-pk.pre_range=100
1.02 1.00 range-notcovered-pk_range=100
1.01 1.01 range-notcovered-si.pre_range=100
1.01 1.01 range-notcovered-si_range=100
1.04 1.54 read-only.pre_range=10000 <<<<<<<<<<
1.00 1.00 read-only.pre_range=100
1.01 1.01 read-only.pre_range=10
1.03 1.45 read-only_range=10000 <<<<<<<<<<
1.01 1.01 read-only_range=100
1.04 1.00 read-only_range=10
1.00 0.99 scan_range=100
1.00 1.02 delete_range=100
1.01 1.02 insert_range=100
1.01 1.00 read-write_range=100
1.01 0.98 read-write_range=10
1.01 1.01 update-index_range=100
1.00 1.00 update-inlist_range=100
1.02 1.02 update-nonindex_range=100
1.03 1.03 update-one_range=100
1.02 1.02 update-zipf_range=100
1.03 1.03 write-only_range=10000

---

The read-only transaction has 4 SQL statements. I ran explain analyze for
each of them assuming the range scan fetches 10k rows and then 100k rows.
The 10k result is similar to what was done above, then I added the 100k
result to see if the perf difference changes with more rows.

In each case there are two "Execution Time" entries. The top one is from PG
15.2 and the bottom from PG 16 prebeta

Summary:
* Queries that do a sort show the largest improvement in PG 16 (Q2, Q4)
* Queries that don't do a sort are slower in PG 16 (Q1, Q3)

Q1.10k: explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN 10000000 AND
10010000;

Execution Time: 4.222 ms

Execution Time: 6.243 ms

Q1.100k: explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN 10000000
AND 10100000;

Execution Time: 36.508 ms

Execution Time: 49.344 ms

Q2.10k: explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN 10000000 AND
10010000 order by c;

Execution Time: 38.224 ms

Execution Time: 15.700 ms

Q2.100k: explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN 10000000
AND 10100000 order by c;

Execution Time: 392.380 ms

Execution Time: 219.022 ms

Q3.10k: explain analyze SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000;

Execution Time: 3.660 ms

Execution Time: 3.994 ms

Q3.100k: explain analyze SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN
10000000 AND 10100000;

Execution Time: 35.917 ms

Execution Time: 39.055 ms

Q4.10k: explain analyze SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000 ORDER BY c;

Execution Time: 29.998 ms

Execution Time: 18.877 ms

Q4.100k: explain analyze SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000 ORDER BY c;

Execution Time: 29.272 ms

Execution Time: 18.265 ms

---

Finally, the queries with full explain analyze output. Each section has two
results -- first for PG 15.3, second for PG 16

--- Q1.10k : explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86 rows=8971
width=121) (actual time=0.061..3.676 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.034 ms
Execution Time: 4.222 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80 rows=10068
width=121) (actual time=0.094..5.456 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.063 ms
Execution Time: 6.243 ms

--- Q1.100k : explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10100000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5206.44 rows=89700
width=121) (actual time=0.017..31.166 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.024 ms
Execution Time: 36.508 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5845.86 rows=100671
width=121) (actual time=0.029..42.285 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.061 ms
Execution Time: 49.344 ms

--- Q2.10k : explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000 order by c;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1114.85..1137.28 rows=8971 width=121) (actual
time=36.561..37.429 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 2025kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86
rows=8971 width=121) (actual time=0.022..3.776 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.059 ms
Execution Time: 38.224 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1259.19..1284.36 rows=10068 width=121) (actual
time=14.419..15.042 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 1713kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80
rows=10068 width=121) (actual time=0.023..3.473 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.049 ms
Execution Time: 15.700 ms

--- Q2.100k : explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN
10000000 AND 10100000 order by c;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=18413.03..18637.28 rows=89700 width=121) (actual
time=300.717..385.193 rows=100001 loops=1)
Sort Key: c
Sort Method: external merge Disk: 12848kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5206.44
rows=89700 width=121) (actual time=0.028..29.590 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.048 ms
Execution Time: 392.380 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=20749.26..21000.94 rows=100671 width=121) (actual
time=154.969..211.572 rows=100001 loops=1)
Sort Key: c
Sort Method: external merge Disk: 12240kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5845.86
rows=100671 width=121) (actual time=0.026..34.278 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.034 ms
Execution Time: 219.022 ms

--- Q3.10k : explain analyze SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN
10000000 AND 10010000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=548.29..548.30 rows=1 width=8) (actual time=3.645..3.646
rows=1 loops=1)
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86
rows=8971 width=4) (actual time=0.024..2.587 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.036 ms
Execution Time: 3.660 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=614.97..614.98 rows=1 width=8) (actual time=3.980..3.980
rows=1 loops=1)
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80
rows=10068 width=4) (actual time=0.024..2.993 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.038 ms
Execution Time: 3.994 ms

--- Q3.100k : explain analyze SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN
10000000 AND 10100000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5430.69..5430.70 rows=1 width=8) (actual
time=35.901..35.902 rows=1 loops=1)
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5206.44
rows=89700 width=4) (actual time=0.017..25.256 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.032 ms
Execution Time: 35.917 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6097.53..6097.55 rows=1 width=8) (actual
time=39.034..39.035 rows=1 loops=1)
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..5845.86
rows=100671 width=4) (actual time=0.018..29.291 rows=100001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10100000))
Planning Time: 0.051 ms
Execution Time: 39.055 ms

--- Q4.10k : explain analyze SELECT DISTINCT c FROM sbtest1 WHERE id
BETWEEN 10000000 AND 10010000 ORDER BY c;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1114.85..1159.71 rows=8971 width=121) (actual
time=26.335..29.435 rows=10001 loops=1)
-> Sort (cost=1114.85..1137.28 rows=8971 width=121) (actual
time=26.333..27.085 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 2025kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86
rows=8971 width=121) (actual time=0.021..2.968 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.052 ms
Execution Time: 29.998 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1259.19..1309.53 rows=10068 width=121) (actual
time=14.203..18.318 rows=10001 loops=1)
-> Sort (cost=1259.19..1284.36 rows=10068 width=121) (actual
time=14.200..14.978 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 1713kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80
rows=10068 width=121) (actual time=0.030..3.475 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.053 ms
Execution Time: 18.877 ms

--- Q4.100k : explain analyze SELECT DISTINCT c FROM sbtest1 WHERE id
BETWEEN 10000000 AND 10010000 ORDER BY c;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1114.85..1159.71 rows=8971 width=121) (actual
time=25.567..28.709 rows=10001 loops=1)
-> Sort (cost=1114.85..1137.28 rows=8971 width=121) (actual
time=25.565..26.320 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 2025kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86
rows=8971 width=121) (actual time=0.025..2.926 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.052 ms
Execution Time: 29.272 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1259.19..1309.53 rows=10068 width=121) (actual
time=13.620..17.714 rows=10001 loops=1)
-> Sort (cost=1259.19..1284.36 rows=10068 width=121) (actual
time=13.618..14.396 rows=10001 loops=1)
Sort Key: c
Sort Method: quicksort Memory: 1713kB
-> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80
rows=10068 width=121) (actual time=0.024..3.478 rows=10001 loops=1)
Index Cond: ((id >= 10000000) AND (id <= 10010000))
Planning Time: 0.039 ms
Execution Time: 18.265 ms

--
Mark Callaghan
mdcallag(at)gmail(dot)com