Один «странный» случай индексного сканирования / forpes.ru

Главная
Один «странный» случай индексного сканирования

Один «странный» случай индексного сканирования +13

12.02.2026 14:02

drema201 5 7400 Источник

Эта история началась с исследования проблем производительности на высоконагруженной базе данных Postgres. Табличка, которая была предметом исследования, довольно небольшая (~100,000 записей), но очень активно используемая.
В процессе исследования я увидел, что Postgres использует индексный доступ по абсолютно неселективному критерию, фактически это был "INDEX FULL SCAN" в терминологии Oracle. Интуиция, наработанная на другой промышленной базе, вопила: "что-то здесь не так!"

Но что?

Сейчас попробуем воспроизвести проблему в "рафинированном" (очищенном от ненужных деталей) случае.

Создаём табличку 4 колонки:
u_id фактически уникальное поле;
domain_id - будет содержать ровно 1 значение;
name - какая то полезная нагрузка;
p_id - довольно селективный, но не уникальный атрибут.

create TABLE test_idxscan
(domain_id int8 not null,
 u_id      uuid not null, 
 name      VARCHAR(255) not null,
 p_id      int8 not null,
 constraint pk_test_idxscan primary KEY (u_id, domain_id));

insert into test_idxscan
select 
 0 as domain_id, 
 uuid_generate_v4() as u_id, 
 'somename some name name some' as name, 
 mod(g,100) as p_id  from pg_catalog.generate_series(1,10000) g;

Отключим параллельное сканирование для нашей сессии, чтобы не усложнять процесс (в реальной ПРОМ системе его и не наблюдается для данной крохотной таблички).

set min_parallel_table_scan_size=100000;

Выполним наш целевой запрос.

explain analyse select * from test_idxscan where domain_id=0 and  p_id=2;

QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Seq Scan on test_idxscan  (cost=0.00..264.00 rows=100 width=61) (actual time=0.011..1.132 rows=100 loops=1)|
  Filter: ((domain_id = 0) AND (p_id = 2))                                                                 |
  Rows Removed by Filter: 9900                                                                             |
Planning Time: 0.147 ms                                                                                    |
Execution Time: 1.176 ms                                                                                   |

Пока никаких чудес, выбран SeqScan, как и ожидалось.

Но давайте теперь симулируем рабочую нагрузку: цель - получить достаточно большое количество версий строк (поэтому изменяем ключевое поле u_id, чтобы избежать hot update).
Реальная ПРОМ нагрузка включает комбинации: delete, insert, update - которые ведут к похожему эффекту.

Итак, в цикле апдейтим по 5000 строк (какие попадутся), пробегаем 300 итераций.

do
$$
begin
  for i in 1..300 loop
   with cte as  (select u_id from test_idxscan limit 5000)
   update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
  end loop;
end$$;

Выполняем опять наш запрос:

explain analyse select * from test_idxscan where domain_id=0 and  p_id=2;

QUERY PLAN                                                                                                                       |
---------------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on test_idxscan  (cost=2046.64..10194.81 rows=100 width=61) (actual time=3.562..5.707 rows=100 loops=1)         |
  Recheck Cond: (domain_id = 0)                                                                                                  |
  Filter: (p_id = 2)                                                                                                             |
  Rows Removed by Filter: 9900                                                                                                   |
  Heap Blocks: exact=115                                                                                                         |
  ->  Bitmap Index Scan on pk_test_idxscan  (cost=0.00..2046.61 rows=10000 width=0) (actual time=3.524..3.524 rows=10000 loops=1)|
        Index Cond: (domain_id = 0)                                                                                              |
Planning Time: 0.269 ms                                                                                                          |
Execution Time: 5.790 ms                                                                                                         |

И вуаля: мы переключились на IndexBitmapScan c Index Cond: (domain_id = 0).
Данный Index Cond абсолютно неселективный, у нас единственное значение в данном поле.

В чём причина такого поведения?

Для начала проверим проводились ли autovacuum и autoanalyse для данной таблички.

select now(), last_autovacuum , last_autoanalyze,  
      n_dead_tup ,n_live_tup ,
      schemaname, relname from pg_stat_user_tables where relname='test_idxscan';

now                          |last_autovacuum              |last_autoanalyze             |n_dead_tup|n_live_tup|schemaname|relname     |
-----------------------------+-----------------------------+-----------------------------+----------+----------+----------+------------+
2026-02-04 14:42:46.041 +0300|2026-02-04 14:42:17.764 +0300|2026-02-04 14:42:17.824 +0300|         0|     10000|public    |test_idxscan|

Видно, что autovacuum и autoanalyze успели отработать. Dead tuples вроде бы были "подчищены", но если посмотреть на реальный размер:

select relpages, reltuples, relname from pg_class where relname like '%test_idxscan%';

relpages|reltuples|relname        |
--------+---------+---------------+
   17160|  10000.0|test_idxscan   |
    1913|  10000.0|pk_test_idxscan|

Видно, что наша табличка test_idxscan стала сильно разреженной (количество страниц - 17160 у нас даже больше чем кол-во записей - 10000).

Почему вакуум так "не дорабатывает"? Возможно, это предмет для следующей публикации.
В итоге, Postgres оценивает стоимость SeqScan выше: 17310.00 vs 10194.81.

set enable_bitmapscan = false;
set enable_indexscan = false;
explain analyse select * from test_idxscan where domain_id=0 and  p_id=2;

QUERY PLAN                                                                                                     |
---------------------------------------------------------------------------------------------------------------+
Seq Scan on test_idxscan  (cost=0.00..17310.00 rows=100 width=61) (actual time=15.559..16.768 rows=100 loops=1)|
  Filter: ((domain_id = 0) AND (p_id = 2))                                                                     |
  Rows Removed by Filter: 9900                                                                                 |
Planning Time: 0.089 ms                                                                                        |
Execution Time: 16.812 ms                                                                                      |

И, в общем-то, прав.

В заключении хочу сказать, что«интуиция» наработанная в чтении планов выполнения на других базах данных, может давать сбой при наличии специфических особенностей Postgres.

P.S. Для решения проблем данного конкретного SELECT'a, естественно, можно построить индекс по полю p_id, (с соответствующей ценой для UPDATE, INSERT, DELETE-операций), но данный пост совсем не об этом элементарном действии.

Комментарии (5)

OlegIct

12.02.2026 17:53

#29521766

приятно читать статью с качественным примером

Почему вакуум так "не дорабатывает"? Возможно, это предмет для следующей публикации.

Анонимный блок работал в одной транзакции. Если транзакция единственная, то автовакуум не вакуумировал таблицу, так как накопительная статистика публикуется после завершения транзакции. Если бы даже автовакуум ее вакуумировал, то не смог бы ничего очистить, так транзакция удерживала горизонт очистки базы данных.

Пока транзакция работала, таблица разрослась в 100 раз. После выполнения анонимного блока, транзакция зафиксировалась, автовакуум отработал и успешно очистил старые версии строк, но файл таблицы уже разросся до 17160блоков, которые и будут сканироваться при Sec Scan и, при использовании Sec Scan, запрос будет выполняться дольше.

Проблема решается добавлением commit; перед end loop;

erogov

12.02.2026 17:53

#29522032

К сожалению, не решается. Пока весь DO не отработает, статистика не обновится (даже если внутри много транзакций, а не одна).

OlegIct

12.02.2026 17:53

#29522234

да, лучше обновлять не в одном блоке. Без обновления накопительной статистики, если другие сессии не меняли таблицу, то автовакуум не будет отрабатывать, пока весь DO не отработает.

Однако, даже в этом примере без вакуума индекс станет в 10 раз меньше (1144 kB вместо 14 MB) и сканирование по индексу станет быстрее полного сканирования:

postgres=# do                        
$$
begin
  for i in 1..300 loop
   with cte as  (select u_id from test_idxscan limit 5000)
   update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
  end loop;
end$$;
DO
Time: 108458.838 ms (01:48.459)

postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on test_idxscan  (cost=0.00..17310.00 rows=100 width=61) (actual rows=100 loops=1)
   Filter: ((domain_id = 0) AND (p_id = 2))
   Rows Removed by Filter: 9900
   Buffers: shared hit=17160
 Planning Time: 0.059 ms
 Execution Time: 8.139 ms
(6 rows)

postgres=# set enable_seqscan = off;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using pk_test_idxscan on test_idxscan  (cost=0.41..38237.34 rows=100 width=61) (actual rows=100 loops=1)
   Index Cond: (domain_id = 0)
   Filter: (p_id = 2)
   Rows Removed by Filter: 9900
   Buffers: shared hit=11675
 Planning Time: 0.062 ms
 Execution Time: 7.340 ms
(7 rows)

postgres=# \dt+ test_idxscan 
                                       List of relations
 Schema |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description 
--------+--------------+-------+----------+-------------+---------------+--------+-------------
 public | test_idxscan | table | postgres | permanent   | heap          | 134 MB | 
(1 row)

postgres=# \di+ pk_test_idxscan 
                                               List of relations
 Schema |      Name       | Type  |  Owner   |    Table     | Persistence | Access method | Size  | Description 
--------+-----------------+-------+----------+--------------+-------------+---------------+-------+-------------
 public | pk_test_idxscan | index | postgres | test_idxscan | permanent   | btree         | 14 MB | 
(1 row)

========================

postgres=# do                       
$$
begin
  for i in 1..300 loop
   with cte as  (select u_id from test_idxscan limit 5000)
   update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
  commit; end loop;
end$$;
DO
Time: 16853.510 ms (00:16.854)


postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on test_idxscan  (cost=0.00..15916.00 rows=100 width=61) (actual rows=100 loops=1)
   Filter: ((domain_id = 0) AND (p_id = 2))
   Rows Removed by Filter: 9900
   Buffers: shared hit=15766
 Planning Time: 0.093 ms
 Execution Time: 7.483 ms
(6 rows)

postgres=# set enable_seqscan = off;

postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using pk_test_idxscan on test_idxscan  (cost=0.29..31169.27 rows=100 width=61) (actual rows=100 loops=1)
   Index Cond: (domain_id = 0)
   Filter: (p_id = 2)
   Rows Removed by Filter: 9900
   Buffers: shared hit=10055
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.135 ms
 Execution Time: 6.167 ms
(9 rows)

postgres=# \dt+ test_idxscan 
                                       List of relations
 Schema |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description 
--------+--------------+-------+----------+-------------+---------------+--------+-------------
 public | test_idxscan | table | postgres | permanent   | heap          | 123 MB | 
(1 row)

postgres=# \di+ pk_test_idxscan 
                                                List of relations
 Schema |      Name       | Type  |  Owner   |    Table     | Persistence | Access method |  Size   | Description 
--------+-----------------+-------+----------+--------------+-------------+---------------+---------+-------------
 public | pk_test_idxscan | index | postgres | test_idxscan | permanent   | btree         | 1144 kB | 
(1 row)

При увеличении числа итераций с 300 до 650 разница заметнее: Seq Scan 14.867 ms вместо 19.750 ms, Bitmap Index Scan 3.605 ms вместо 5.830 ms. Размер таблицы 183 MB вместо 290 MB, индекс 1176 kB вместо 34 MB.

do                  
$$
begin
  for i in 1..650 loop
   with cte as  (select u_id from test_idxscan limit 5000)
   update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
  end loop;
end$$;
DO
Time: 429789.611 ms (07:09.790)


postgres=# \dt+ test_idxscan 
                                       List of relations
 Schema |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description 
--------+--------------+-------+----------+-------------+---------------+--------+-------------
 public | test_idxscan | table | postgres | permanent   | heap          | 290 MB | 
(1 row)

postgres=# \di+ pk_test_idxscan 
                                               List of relations
 Schema |      Name       | Type  |  Owner   |    Table     | Persistence | Access method | Size  | Description 
--------+-----------------+-------+----------+--------------+-------------+---------------+-------+-------------
 public | pk_test_idxscan | index | postgres | test_idxscan | permanent   | btree         | 34 MB | 
(1 row)

postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_idxscan  (cost=17298.16..39608.30 rows=95 width=61) (actual rows=100 loops=1)
   Recheck Cond: (domain_id = 0)
   Filter: (p_id = 2)
   Rows Removed by Filter: 9900
   Heap Blocks: exact=116
   Buffers: shared hit=4400
   ->  Bitmap Index Scan on pk_test_idxscan  (cost=0.00..17298.13 rows=9830 width=0) (actual rows=10000 loops=1)
         Index Cond: (domain_id = 0)
         Buffers: shared hit=4284
 Planning:
   Buffers: shared hit=32
 Planning Time: 0.215 ms
 Execution Time: 5.830 ms
(13 rows)

postgres=# set enable_seqscan = on;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on test_idxscan  (cost=0.00..37193.45 rows=95 width=61) (actual rows=100 loops=1)
   Filter: ((domain_id = 0) AND (p_id = 2))
   Rows Removed by Filter: 9900
   Buffers: shared hit=37046
 Planning Time: 0.081 ms
 Execution Time: 19.750 ms
(6 rows)

=================

do                       
$$
begin
  for i in 1..650 loop
   with cte as  (select u_id from test_idxscan limit 5000)
   update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
  commit;
  end loop;
end$$;

DO
Time: 37388.723 ms (00:37.389)

postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_idxscan  (cost=663.31..19140.55 rows=100 width=61) (actual rows=100 loops=1)
   Recheck Cond: (domain_id = 0)
   Filter: (p_id = 2)
   Rows Removed by Filter: 9900
   Heap Blocks: exact=333
   Buffers: shared hit=479
   ->  Bitmap Index Scan on pk_test_idxscan  (cost=0.00..663.28 rows=10000 width=0) (actual rows=10000 loops=1)
         Index Cond: (domain_id = 0)
         Buffers: shared hit=146
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.158 ms
 Execution Time: 3.605 ms
(13 rows)

postgres=# set enable_bitmapscan = off;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and  p_id=2;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on test_idxscan  (cost=0.00..23515.00 rows=100 width=61) (actual rows=100 loops=1)
   Filter: ((domain_id = 0) AND (p_id = 2))
   Rows Removed by Filter: 9900
   Buffers: shared hit=23365
 Planning Time: 0.065 ms
 Execution Time: 14.867 ms
(6 rows)

postgres=# \di+ pk_test_idxscan 
                                                List of relations
 Schema |      Name       | Type  |  Owner   |    Table     | Persistence | Access method |  Size   | Description 
--------+-----------------+-------+----------+--------------+-------------+---------------+---------+-------------
 public | pk_test_idxscan | index | postgres | test_idxscan | permanent   | btree         | 1176 kB | 
(1 row)

postgres=# \dt+ test_idxscan 
                                       List of relations
 Schema |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description 
--------+--------------+-------+----------+-------------+---------------+--------+-------------
 public | test_idxscan | table | postgres | permanent   | heap          | 183 MB | 
(1 row)

Главное то, что индекс не растет, а значит время запроса при увеличении числа итераций не будет расти и останется ~4мс

drema201 Автор
12.02.2026 17:53
#29524078
Добрый день,
Спасибо за внимание к моему посту. В данном "синтетическом" случае данная ситуация с VACUUM сыграла мне на руку, мне нужно было как-то "раздуть" таблицу, чтобы воспроизвести ситуацию на ПРОМ системе. Там ситуация даже "одиознее" - соотношение pages/tuples>10, т.е. сплошные дырки, причём там одиночные UPDATE,DELETE,INSERT. Вот и хотелось поисследовать, как мы дошли до жизни такой, ладно VACUUM допустим не может ужать таблицу, т.к. пустые страницы не попадают в конец файла, но почему свободное место в страницах не переиспользуется, это вопрос.
1. OlegIct
  12.02.2026 17:53
  #29524772
  почему свободное место в страницах не переиспользуется, это вопрос
  
  потому, что оно не свободно, а используется старыми версиями строк, которые удерживаются: 1) долгими запросами, 2) долгими транзакциями, 3) запросами на репликах с включенной обратной связью (поэтому обратная связь по умолчанию отключена). Длительность удержания старых версий строк можно узнать в столбце secs запросом:
  
  postgres=# select extract(epoch from (clock_timestamp()-xact_start)) secs, datname database, state from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc limit 10; secs | database | state -----------+----------+-------- 94.414306 | postgres | active 0.000924 | postgres | active (2 rows)
  
  Это пример запроса, в процессе выполнения анонимного блока DO вашего теста.
  
  Если старые версии строк не удерживаются горизонтом базы данных или обратной связью, то автовакуум очистит место и оно будет использоваться. Пример:
  
  После выполнения анонимного блока DO размер таблицы 134Мб, блоки полупустые:
  
  postgres=# \dt+ test_idxscan List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------------+-------+----------+-------------+---------------+--------+------------- public | test_idxscan | table | postgres | permanent | heap | 134 MB | (1 row)
  
  Вставляем 99тыс. строк:
  
  postgres=# insert into test_idxscan select 0 as domain_id, uuid_generate_v4() as u_id, 'somename some name name some' as name, mod(g,100) as p_id from pg_catalog.generate_series(1,99000) g; INSERT 0 99000 Time: 2927.706 ms (00:02.928)
  
  Все строки будут вставлены в сущестующие полупустые блоки,размер таблицы не увеличится и останется таким же, 134Мб:
  
  postgres=# \dt+ test_idxscan List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------------+-------+----------+-------------+---------------+--------+------------- public | test_idxscan | table | postgres | permanent | heap | 134 MB | (1 row)
  
  Вы можете проверить длительность удержания горизонта на проблемной базе. Если горизонт удерживают долгие запросы, то перенести их на реплику. Обратную связь на репликах не стоит включать, для защиты от snapshot too old используют другие параметры. Если удерживают транзакции, посмотреть не простаивают ли они (столбец state в приведённом запросе).

Один «странный» случай индексного сканирования +13

Комментарии (5)

OlegIct

erogov

OlegIct

drema201 Автор

OlegIct