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:

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)

---

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