⛔"Index Only Scan" vs "Bitmap Index Scan": Почему рекомендации нейросетей нужно проверять в боевых условиях.

Алгоритм не видит контекста. Эксперимент — видит.

Предисловие

Нейросети для оптимизации баз данных часто предлагают математически верные, но практически проигрышные решения. В статье показано, как попытка применить покрывающий индекс (Index Only Scan) для таблицы с 1 миллионом строк и всего 685 уникальными значениями обернулась падением производительности на 7% под нагрузкой. Этот пример доказывает: для данных с низкой кардинальностью и высокой конкуренцией за ресурсы эффективность простых индексов или даже полного сканирования может быть выше. Все гипотезы, даже от ИИ, должны проходить экспериментальную проверку.

ℹ️Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

Предыдущие статьи цикла, посвященного исследованиям индексов PostgreSQL :

Тестовая таблица

CREATE TABLE pgbench_test

(

aid integer PRIMARY KEY ,

bid integer,

abalance integer,

filler character(84)

);

INSERT INTO pgbench_test ( aid , bid , abalance , filler )

SELECT

id ,

floor(random() * 685 ) + 1 ,

floor(random() * (68500000 - 1 + 1)) + 1 ,

md5(random()::text)

FROM generate_series(1,1000000) id;

Индекс

CREATE INDEX pgbench_test_idx ON pgbench_test ( bid );

Тестовый запрос

select test.abalance

into test_rec

from pgbench_accounts acc

join pgbench_test test on (test.bid = acc.bid )

where acc.aid = current_aid ;

План выполнения тестового запроса

Nested Loop (cost=14.51..1546.55 rows=1460 width=4) (actual time=0.894..366.050 rows=1468 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.217..0.227 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Bitmap Heap Scan on pgbench_test test (cost=13.94..1529.17 rows=1460 width=8) (actual time=0.669..365.312 rows=1468 loops=1)

Recheck Cond: (bid = acc.bid)

Heap Blocks: exact=1407

-> Bitmap Index Scan on pgbench_test_idx (cost=0.00..13.57 rows=1460 width=0) (actual time=0.355..0.356 rows=1468 loops=1)

Index Cond: (bid = acc.bid)

Planning Time: 2.634 ms

Execution Time: 366.419 ms

ℹ️Метод доступа = Bitmap Index Scan on pgbench_test_idx

Рекомендация нейросети "Ask Postgres"

Рекомендация нейросети DeepSeek

Покрывающий индекс

Дополнительный индекс

CREATE INDEX CONCURRENTLY idx_pgbench_test_bid_abalance ON pgbench_test(bid) INCLUDE (abalance);

Новый план выполнения тестового запроса

Nested Loop (cost=0.99..48.86 rows=1460 width=4) (actual time=0.771..1.186 rows=1432 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.736..0.737 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Index Only Scan using idx_pgbench_test_bid_abalance on pgbench_test test (cost=0.42..31.47 rows=1460 width=8) (actual time=0.029..0.289 rows=1432 loops=1)

Index Cond: (bid = acc.bid)

Heap Fetches: 0

Planning Time: 4.949 ms

Execution Time: 1.302 ms

(8 rows)

💥Стоимость плана выполнения = 48.86

💥Снижение стоимости плана выполнения 96%

ℹ️Метод доступа = Index Only Scan using idx_pgbench_test_bid_abalance

Сравнение операционной скорости в Эксперимент-2(обычный индекс) и Эксперимент-3(покрывающий индекс)

Эксперимент-2

Операционная скорость

График изменения операционной скорости для эксперимента-2(SPEED-2) и эксперимента-3(SPEED-3)

Результаты

1. До нагрузки 15 соединений производительность СУБД при использовании покрывающего индекса(эксперимент-3) ниже производительности СУБД с использованием простого индекса (эксперимент-2) в среднем на 7%.
2. С ростом нагрузки после 15 соединений производительность СУБД при использовании покрывающего индекса(эксперимент-3) выше производительности СУБД с использованием простого индекса (эксперимент-2) в среднем на 22%.

Причины снижения производительности при использовании покрывающего индекса в эксперименте-3

1. Конкуренция за ресурсы блокировок

Покрывающий индекс значительно больше по размеру, чем простой индекс на bid

При Index Only Scan требуется проверка visibility map для каждого блока индекса

В условиях конкурентного доступа возникают блокировки на уровне:

• Буферного кэша
• Страниц индекса
• Visibility map

2. Проблемы с Visibility Map

При конкурентном доступе PostgreSQL должен постоянно обновлять visibility map

До 15 сессий конкуренция за обновление visibility map создает дополнительную нагрузку

В эксперименте-2 Bitmap Heap Scan менее чувствителен к актуальности visibility map

3. Эффект "теплого" кэша

В эксперименте-2 данные распределены между:

• Индексными страницами (меньший размер)
• Страницами таблицы

Это позволяет лучше использовать параллелизм на уровне кэша

В эксперименте-3 все данные сосредоточены в одном большом индексе, что создает "горячие точки" доступа

4. Статистика планировщика

Planning Time: 2.634 ms  -- эксперимент-2

Planning Time: 4.949 ms  -- эксперимент-3 (на 88% больше!)

Усложнение индекса требует больше времени на планирование

При множественных параллельных сессиях это накладные расходы суммируются.

5. Пороговый эффект

До 15 сессий:

• Накладные расходы на поддержание покрывающего индекса превышают выгоду
• Конкуренция за visibility map доминирует

После 15 сессий:

• Выгода от исключения обращений к heap перевешивает накладные расходы
• Система достигает точки безубыточности

6. Сравнение планов выполнения

✅ Вывод по одиночному запросу:

Эксперимент-3 в 280 раз быстрее - покрывающий индекс работает идеально для одного запроса.

Расчёт кардинальности для таблицы 'pgbench_test' по столбцу bid

pgbench_db=# select attname , n_distinct from pg_stats where tablename = 'pgbench_test' and attname ='bid' ;

attname | n_distinct

---------+------------

bid    |       685

(1 row)

pgbench_db=# select reltuples from pg_class where relname = 'pgbench_test';

reltuples

-----------

    1e+06

(1 row)

⚠️Выводы:

1. При низкой и средней нагрузке накладные расходы на поддержание сложной структуры данных могут перевешивать ее преимущества.
2. Покрывающие индексы полезны при высокой кардинальности и редких запросах — не при низкой кардинальности и высокой параллельности.
3. Иногда "оптимизация" — это деградация.

⚠️Снижение стоимости запроса — это полезный индикатор потенциального улучшения производительности в рамках неизменной среды выполнения, но он не является ни необходимым (производительность можно повысить "мимо" планировщика), ни достаточным (планировщик может ошибаться) условием.