pgbench -i psql -c "alter table pgbench_branches set (autovacuum_enabled=off)" psql -c "alter index pgbench_branches_pkey set (deduplicate_items=off)" psql -c "vacuum pgbench_branches;" echo "UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 1;" > g0.sql во второй сессии: begin; select pg_current_xact_id(); pgbench -n -T 100000 -P 30 -f g0.sql begin transaction isolation level repeatable read; EXPLAIN (analyze, buffers) select bbalance from pgbench_branches WHERE bid = 1; select pg_relation_size('pgbench_branches', 'main')/8192 table_size, pg_relation_size('pgbench_branches_pkey', 'main')/8192 index_size; select blkno, type, live_items, dead_items, avg_item_size, free_size from bt_multi_page_stats('pgbench_branches_pkey',1,-1); select itemoffset, ctid, itemlen, dead, left(data,2) data from bt_page_items('pgbench_branches_pkey',1) limit 5; rollback; BEGIN QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.12..8.14 rows=1 width=4) (actual time=5.355..5.359 rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=311 Planning Time: 0.046 ms Execution Time: 5.377 ms (5 rows) В индексе 2 блока (index_size), один метаданных, второй - дерево индекса. Дерево индекса состоит из одного листового блока, он же корневой. Всего ссылок на записи таблицы 310 (live_items), что равно числу блоков в таблице (table_size): Читалось 311 блоков: все блоки таблицы и блок индекса. Вопрос: ходил ли процесс по 310 ссылкам или взял первую ссылку и прошелся по цепочке в блоках таблицы, используя ctid в заголовке строк таблицы? Свободного места в блоке индекса немного (free_size) table_size | index_size ------------+------------ 310 | 2 (1 row) blkno | type | live_items | dead_items | avg_item_size | free_size -------+------+------------+------------+---------------+----------- 1 | l | 310 | 0 | 16 | 1948 (1 row) itemoffset | ctid | itemlen | dead | data ------------+-------+---------+------+------ 1 | (0,1) | 16 | f | 01 2 | (1,1) | 16 | f | 01 3 | (2,1) | 16 | f | 01 4 | (3,1) | 16 | f | 01 5 | (4,1) | 16 | f | 01 (5 rows) blkno | type | live_items | dead_items | avg_item_size | free_size -------+------+------------+------------+---------------+----------- 1 | l | 310 | 0 | 16 | 1948 (1 row) itemoffset | ctid | itemlen | dead | data ------------+-------+---------+------+------ 1 | (0,1) | 16 | f | 01 2 | (1,1) | 16 | f | 01 3 | (2,1) | 16 | f | 01 4 | (3,1) | 16 | f | 01 5 | (4,1) | 16 | f | 01 (5 rows) ------------------ если не отключить дедуплицирование: Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.12..8.14 rows=1 width=4) (actual time=7.219..7.223 rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=421 Planning: Buffers: shared hit=10 Planning Time: 0.128 ms Execution Time: 7.242 ms (7 rows) Читаются также все блоки таблицы плюс блок индекса. В первых двух записях индекса (itemoffset=1 и 2) стали храниться массивы ссылкок на строки таблицы. Те ссылки, которые не поместились в записи хранятся в недедуплицированном виде (их 15-2). table_size | index_size ------------+------------ 420 | 2 (1 row) blkno | type | live_items | dead_items | avg_item_size | free_size -------+------+------------+------------+---------------+----------- 1 | l | 15 | 0 | 179 | 5400 (1 row) itemoffset | ctid | itemlen | dead | data ------------+-----------+---------+------+------ 1 | (16,8414) | 1352 | f | 01 2 | (16,8377) | 1128 | f | 01 3 | (407,1) | 16 | f | 01 4 | (408,1) | 16 | f | 01 5 | (409,1) | 16 | f | 01 (5 rows) ------------------------ Через 360 секунд при tps: progress: 30.0 s, 825.4 tps, lat 1.209 ms stddev 0.683, 0 failed progress: 60.0 s, 388.9 tps, lat 2.569 ms stddev 0.839, 0 failed progress: 90.0 s, 287.4 tps, lat 3.477 ms stddev 0.938, 0 failed progress: 120.0 s, 246.3 tps, lat 4.057 ms stddev 0.905, 0 failed progress: 150.0 s, 215.2 tps, lat 4.643 ms stddev 0.811, 0 failed progress: 180.0 s, 192.6 tps, lat 5.188 ms stddev 0.865, 0 failed progress: 210.0 s, 176.7 tps, lat 5.656 ms stddev 1.010, 0 failed progress: 240.0 s, 165.6 tps, lat 6.034 ms stddev 1.111, 0 failed progress: 270.0 s, 156.7 tps, lat 6.379 ms stddev 0.575, 0 failed progress: 300.0 s, 146.3 tps, lat 6.832 ms stddev 1.033, 0 failed progress: 330.0 s, 140.1 tps, lat 7.134 ms stddev 0.911, 0 failed progress: 360.0 s, 133.0 tps, lat 7.518 ms stddev 0.950, 0 failed ^C блок индекса разделится и в дереве индекса будет 3 блока: корневой и два листовых. Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.27..8.29 rows=1 width=4) (actual time=7.304..7.308 rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=418 Planning Time: 0.052 ms Execution Time: 7.327 ms (5 rows) table_size | index_size ------------+------------ 415 | 4 (1 row) Читается 418 блоков: все блоки таблицы и все три блока индекса. blkno | type | live_items | dead_items | avg_item_size | free_size -------+------+------------+------------+---------------+----------- 1 | l | 391 | 0 | 16 | 320 2 | l | 25 | 0 | 16 | 7648 3 | r | 2 | 0 | 16 | 8108 (3 rows) левый листовой блок индекса: itemoffset | ctid | itemlen | dead | data ------------+------------+---------+------+------ 1 | (390,4097) | 24 | | 01 2 | (0,1) | 16 | f | 01 3 | (1,1) | 16 | f | 01 4 | (2,1) | 16 | f | 01 5 | (3,1) | 16 | f | 01 (5 rows) правый листовой блок индекса: select itemoffset, ctid, itemlen, dead, left(data,2) data from bt_page_items('pgbench_branches_pkey',2) limit 5; itemoffset | ctid | itemlen | dead | data ------------+---------+---------+------+------ 1 | (390,1) | 16 | f | 01 2 | (391,1) | 16 | f | 01 3 | (392,1) | 16 | f | 01 4 | (393,1) | 16 | f | 01 5 | (394,1) | 16 | f | 01 (5 rows) корневой блок индекса: select itemoffset, ctid, itemlen, dead, left(data,2) data from bt_page_items('pgbench_branches_pkey',3) limit 5; itemoffset | ctid | itemlen | dead | data ------------+----------+---------+------+------ 1 | (1,0) | 8 | | 2 | (2,4097) | 24 | | 01 (2 rows) ------------ Посмотрим данные плана, если использовать Bitmap Index Scan: set enable_indexscan = off; EXPLAIN (analyze, buffers) select bbalance from pgbench_branches WHERE bid = 1; Bitmap Heap Scan on pgbench_branches (cost=4.28..8.30 rows=1 width=4) (actual time=7.456..7.463 rows=1 loops=1) Recheck Cond: (bid = 1) Heap Blocks: exact=415 Buffers: shared hit=418 -> Bitmap Index Scan on pgbench_branches_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.167..0.168 rows=415 loops=1) Index Cond: (bid = 1) Buffers: shared hit=3 Planning Time: 0.053 ms Execution Time: 7.498 ms (9 rows) В трёх блоках индекса (Buffers: shared hit=3) было прочитано 415 записей (rows=415). Посмотрим, что было бы без индекса: set enable_bitmapscan = off; EXPLAIN (analyze, buffers) select bbalance from pgbench_branches WHERE bid = 1; Seq Scan on pgbench_branches (cost=0.00..420.19 rows=1 width=4) (actual time=5.675..5.679 rows=1 loops=1) Filter: (bid = 1) Buffers: shared hit=415 Planning Time: 0.051 ms Execution Time: 5.709 ms (5 rows) Без индекса Seq Scan: Execution Time: 5.709 ms Index Scan: Execution Time: 7.327 ms Bitmap Heap Scan: Execution Time: 7.498 ms Точное время выполнения (без обращений к счетчику времени): Без индекса Seq Scan: Execution Time: 5.612 ms Index Scan: Execution Time: 6.476 ms Bitmap Heap Scan: Execution Time: 7.472 ms Пропорции сохраняются. Время последовательного сканирования 415 блоков в 1,5 раза меньше, чем при использовании индекса из 3 блоков. Если бы процесс прошел по всем ссылкам (используя ctid в заголовке строки таблициы) как при Seq Scan и дошел до актуальной версии строки, то время было бы около 5.6ms. И можно сделать вывод, что серверный процесс просканировав по индексной ссылке цепочку HHU версий строк в блоке не стал переходить в следующий блок таблицы, стал сканировать следующую цепочку HHU в другом блоке таблицы. То есть можно создать оптимизацию, при которой серверный процесс мог бы пометить ссылку как непригодную для обновления. Это бы помогло последующим командам, выполняющим UPDATE строки. Если бы индексная ссылка вышла за горизонт базы, то серверный процесс смог бы выплнить очистку блока индекса (HOT cleanup), но индексаная запись может понадобиться транзакциям или запросам, удерживающим горизонт. Но из-за этого SELECT выполнялся бы медленнее. Может быть команде UPDATE такое бы имело смысл делать. Дело не в цепочках версий. HHU можно убрать добавив индекс по balance. Условие создания цепочки в блоке (HOT update) - отсутствие внесения изменений в любые индексы (добавление в UPDATE условия id=id не подходит). Достатчоно создать индекс по изменяющеиуся столбцу (bbalance) и на каждую версию строки в блоке будет создаваться индексная запись. Производительность при этом упадёт. EXPLAIN (analyze, buffers, timing off) select bbalance from pgbench_branches WHERE bid = 1; Seq Scan on pgbench_branches (cost=0.00..420.19 rows=1 width=4) (actual rows=1 loops=1) Filter: (bid = 1) Buffers: shared hit=415 Planning Time: 0.050 ms Execution Time: 5.612 ms (5 rows) reset enable_bitmapscan; reset enable_indexscan; EXPLAIN (analyze, buffers, timing off) select bbalance from pgbench_branches WHERE bid = 1; Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.27..8.29 rows=1 width=4) (actual rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=418 Planning Time: 0.048 ms Execution Time: 6.476 ms (5 rows) set enable_indexscan = off; EXPLAIN (analyze, buffers, timing off) select bbalance from pgbench_branches WHERE bid = 1; Bitmap Heap Scan on pgbench_branches (cost=4.28..8.30 rows=1 width=4) (actual rows=1 loops=1) Recheck Cond: (bid = 1) Heap Blocks: exact=415 Buffers: shared hit=418 -> Bitmap Index Scan on pgbench_branches_pkey (cost=0.00..4.28 rows=1 width=0) (actual rows=415 loops=1) Index Cond: (bid = 1) Buffers: shared hit=3 Planning Time: 0.048 ms Execution Time: 7.472 ms (9 rows) ----------------- Посмотрим план команды UPDATE: RESET all; begin transaction isolation level repeatable read; EXPLAIN (analyze, buffers) UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 1; select pg_relation_size('pgbench_branches', 'main')/8192 table_size, pg_relation_size('pgbench_branches_pkey', 'main')/8192 index_size; select blkno, type, live_items, dead_items, avg_item_size, free_size from bt_multi_page_stats('pgbench_branches_pkey',1,-1); select itemoffset, ctid, itemlen, dead, left(data,2) data from bt_page_items('pgbench_branches_pkey',1) limit 5; commit; Update on pgbench_branches (cost=0.27..8.29 rows=0 width=0) (actual time=7.309..7.314 rows=0 loops=1) Buffers: shared hit=420 -> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.27..8.29 rows=1 width=10) (actual time=7.259..7.263 rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=418 Planning Time: 0.048 ms Execution Time: 7.450 ms (7 rows) Читалось 418 блоков как и при SELECT (hit=418) и еще 2 раза к блокам был доступ (hit=420). Уровень изоляции и использование транзакции роли не играет. В индекс не вносились изменения (free_size не меняется при повторных выполнениях) table_size | index_size ------------+------------ 415 | 4 (1 row) blkno | type | live_items | dead_items | avg_item_size | free_size -------+------+------------+------------+---------------+----------- 1 | l | 391 | 0 | 16 | 320 2 | l | 25 | 0 | 16 | 7648 3 | r | 2 | 0 | 16 | 8108 (3 rows) itemoffset | ctid | itemlen | dead | data ------------+------------+---------+------+------ 1 | (390,4097) | 24 | | 01 2 | (0,1) | 16 | f | 01 3 | (1,1) | 16 | f | 01 4 | (2,1) | 16 | f | 01 5 | (3,1) | 16 | f | 01 (5 rows) set enable_indexscan = off; EXPLAIN (analyze, buffers) UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 1; После контрольной точки местами появляются dirtied=1: set enable_indexscan = off; EXPLAIN (analyze, buffers) UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 1; Update on pgbench_branches (cost=4.28..8.30 rows=0 width=0) (actual time=7.514..7.521 rows=0 loops=1) Buffers: shared hit=420 dirtied=1 -> Bitmap Heap Scan on pgbench_branches (cost=4.28..8.30 rows=1 width=10) (actual time=7.442..7.495 rows=1 loops=1) Recheck Cond: (bid = 1) Heap Blocks: exact=415 Buffers: shared hit=418 -> Bitmap Index Scan on pgbench_branches_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.168..0.170 rows=415 loops=1) Index Cond: (bid = 1) Buffers: shared hit=3 Planning Time: 0.055 ms Execution Time: 7.546 ms checkpoint; Update on pgbench_branches (cost=4.26..8.28 rows=0 width=0) (actual time=7.533..7.540 rows=0 loops=1) Buffers: shared hit=420 dirtied=1 -> Bitmap Heap Scan on pgbench_branches (cost=4.26..8.28 rows=1 width=10) (actual time=7.510..7.517 rows=1 loops=1) Recheck Cond: (bid = 1) Heap Blocks: exact=415 Buffers: shared hit=418 dirtied=1 -> Bitmap Index Scan on pgbench_branches_pkey (cost=0.00..4.26 rows=1 width=0) (actual time=0.164..0.165 rows=415 loops=1) Index Cond: (bid = 1) Buffers: shared hit=3 Planning Time: 0.054 ms Execution Time: 7.589 ms (11 rows) checkpoint; set enable_indexscan = on; EXPLAIN (analyze, buffers) UPDATE pgbench_branches SET bbalance = bbalance + 1 WHERE bid = 1; Update on pgbench_branches (cost=0.25..8.27 rows=0 width=0) (actual time=7.157..7.161 rows=0 loops=1) Buffers: shared hit=420 dirtied=1 -> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.25..8.27 rows=1 width=10) (actual time=7.121..7.125 rows=1 loops=1) Index Cond: (bid = 1) Buffers: shared hit=418 dirtied=1 Planning Time: 0.048 ms Execution Time: 7.182 ms (7 rows)