Hi Aleksander,
On 20/11/2025 16:56, Aleksander Alekseev wrote:
Thanks for the patch. Here are my two cents.
This is a follow-up on my earlier answers to your questions. Below is a
motivating example and a performance comparison with HEAD.
For database performance we want to ensure our working set fits in
memory (shared buffers and OS page cache).
Index leaf-page hit ratios allows a DBA to detect when actual index
lookups hit disk, which the overall ratio can mask, because the internal
pages are typically cached.
To see this in action, we can do the following. After applying the patch:
CREATE TABLE test_data_large (
id SERIAL PRIMARY KEY,
search_key INTEGER,
data_col TEXT
);
INSERT INTO test_data_large (search_key, data_col)
SELECT i, 'Data-' || i
FROM generate_series(1, 3200000) i;
CREATE INDEX idx_search_key_large ON test_data_large(search_key);
ANALYZE test_data_large;
SELECT pg_stat_reset();
This is our workload we'll run and collect stats for:
DO $$
BEGIN
FOR i IN 1..20000 LOOP
PERFORM 1
FROM test_data_large
WHERE search_key = ((i * 160) % 3200000)
LIMIT 1;
END LOOP;
END $$;
SELECT pg_stat_force_next_flush();
Now we can inspect the stats:
SELECT
ROUND(100.0 * idx_blks_hit / (idx_blks_read + idx_blks_hit), 2) ||
'%' AS "Overall hit ratio",
ROUND(100.0 * (idx_blks_hit - idx_metadata_blks) / ((idx_blks_read
+ idx_blks_hit) - idx_metadata_blks), 2) || '%' AS "Leaf hit ratio"
FROM pg_statio_all_indexes
WHERE indexrelname = 'idx_search_key_large';
This is the result:
Cache hit ratio | Leaf hit ratio
-------------+-----------
85.37% | 56.11%
(1 row)
The overall cache hit ratio looks healthy at ~85%, but the leaf-page hit
ratio is much lower at ~56%, indicating that a large share of index leaf
blocks are actually being read from disk. This suggests that internal
index pages remain cached (as expected, B-tree hierarchy) while the
working set of leaf pages does not fit in memory, leading to more disk
I/O during actual lookups.
Now showing the performance comparison of running this script [1] on my
laptop on HEAD and patch.
HEAD
====
Point Queries
-----------
+------+--------+
| Run | TPS |
+------+--------+
| 1 | 30098 |
| 2 | 30164 |
| 3 | 29903 |
| 4 | 30024 |
| 5 | 29898 |
| 6 | 30023 |
| 7 | 29952 |
| 8 | 29413 |
| 9 | 30062 |
| 10 | 29821 |
+------+--------+
Median: 29988
Range Scans
----------
+------+------+
| Run | TPS |
+------+------+
| 1 | 586 |
| 2 | 584 |
| 3 | 584 |
| 4 | 584 |
| 5 | 584 |
| 6 | 587 |
| 7 | 578 |
| 8 | 562 |
| 9 | 583 |
| 10 | 586 |
+------+------+
Median: 584
Mixed Load
----------
+------+--------+
| Run | TPS |
+------+--------+
| 1 | 16446 |
| 2 | 15842 |
| 3 | 16701 |
| 4 | 16293 |
| 5 | 16633 |
| 6 | 16292 |
| 7 | 16753 |
| 8 | 17047 |
| 9 | 17094 |
| 10 | 17078 |
+------+--------+
Median: 16667
Patch
===
Point Queries
-----------
+------+--------+
| Run | TPS |
+------+--------+
| 1 | 30335 |
| 2 | 30448 |
| 3 | 30372 |
| 4 | 30447 |
| 5 | 30478 |
| 6 | 30482 |
| 7 | 30428 |
| 8 | 30443 |
| 9 | 30433 |
| 10 | 30478 |
+------+--------+
Median: 30445
Range Scans
----------
+------+------+
| Run | TPS |
+------+------+
| 1 | 578 |
| 2 | 586 |
| 3 | 585 |
| 4 | 586 |
| 5 | 587 |
| 6 | 585 |
| 7 | 586 |
| 8 | 586 |
| 9 | 586 |
| 10 | 586 |
+------+------+
Median: 586
Mixed Load
---------
+------+--------+
| Run | TPS |
+------+--------+
| 1 | 17002 |
| 2 | 17078 |
| 3 | 17042 |
| 4 | 17046 |
| 5 | 17007 |
| 6 | 17023 |
| 7 | 17056 |
| 8 | 17071 |
| 9 | 17084 |
| 10 | 17068 |
+------+--------+
Median: 17051
HEAD vs Patch Summary
===============
+----------------------+-----------+-----------+
| Test | HEAD TPS | Patch TPS |
+----------------------+-----------+-----------+
| Point Queries Median | 29988 | 30445 |
| Range Scans Median | 584 | 586 |
| Mixed Load Median | 16667 | 17051 |
+----------------------+-----------+-----------+
Any feedback appreciated on the performance methodology/patch as a whole
welcome.
[1] https://gist.github.com/mcadariu/fc4a6d4eccd56b4447d1d9d05f9b5d79
--
Thanks,
Mircea Cadariu
