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 mdcal...@gmail.com
