(ложный прогноз)"Index Only Scan" vs "Bitmap Index Scan"- Нейросеть "Ask Postgres".

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

Дополнение к статье

Вопрос нейросети "Ask Postgres"

Как можно оптимизировать SQL запрос

"select test.abalance into test_rec from pgbench_accounts acc join pgbench_test test on (test.bid = acc.bid ) where acc.aid = current_aid ;"

используя определение таблицы

"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 );"

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

"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"

Ответ нейросети

🔍 Анализ текущей ситуации

1. Структура таблиц

• pgbench_accounts — предположительно имеет первичный ключ aid (по плану: Index Scan using pgbench_accounts_pkey).
• pgbench_test:aid — PK (уникальный)
• bid — внешний ключ (по значению из pgbench_accounts)
• abalance — целевое поле
• filler — 84-символьный заполнитель (не используется в запросе)

2. Индекс

• CREATE INDEX pgbench_test_idx ON pgbench_test (bid); — одностроковый индекс по bid.
• Это правильный индекс для условия JOIN ON test.bid = acc.bid, но недостаточный для эффективного доступа к abalance.

3. План выполнения

• Nested Loop — используется, потому что pgbench_accounts возвращает 1 строку (rows=1), и для каждой из них выполняется сканирование pgbench_test.
• Bitmap Heap Scan на pgbench_test — возвращает 1468 строк (очень много!) для одного значения bid.
• Время выполнения: 366 мс — высокое, и основная нагрузка — на чтение 1407 блоков кучи (Heap Blocks: exact=1407).

💡 Проблема: Индекс pgbench_test_idx на bid позволяет быстро найти строки с нужным bid, но не покрывает поле abalance. Поэтому PostgreSQL должен:Найти строки по bid через индекс.

Перейти к куче (heap) и прочитать 1407 блоков, чтобы получить abalance.

Это покрытие индекса (covering index) отсутствует — главная причина высокого времени.

✅ Рекомендации по оптимизации

✅ 1. Создать покрывающий индекс (Covering Index)

Замените текущий индекс на составной индекс, включающий bid и abalance:

DROP INDEX pgbench_test_idx;

CREATE INDEX pgbench_test_bid_abalance_idx ON pgbench_test (bid, abalance);

✅ Почему это работает: Запрос выбирает только abalance и фильтрует по bid.

Индекс (bid, abalance) позволяет полностью удовлетворить запрос без обращения к куче (Index Only Scan).

Устраняется Bitmap Heap Scan и чтение 1407 блоков.

✅ 2. Проверить, что current_aid — это параметр, а не столбец

Убедитесь, что current_aid — это параметр PL/pgSQL (например, аргумент функции), а не столбец таблицы. Если это столбец — запрос некорректен. В вашем случае, судя по плану, это параметр, и всё в порядке.

✅ 3. Убедиться, что pgbench_accounts имеет индекс на aid

План показывает Index Scan using pgbench_accounts_pkey, значит, индекс есть — это хорошо. Не трогать.

✅ 4. (Опционально) Рассмотреть материализованное представление или денормализацию

Если запрос выполняется часто, и данные редко меняются, можно создать материализованное представление:

CREATE MATERIALIZED VIEW mv_account_balance AS

SELECT acc.aid, test.abalance

FROM pgbench_accounts acc

JOIN pgbench_test test ON test.bid = acc.bid;

CREATE INDEX mv_account_balance_aid_idx ON mv_account_balance (aid);

И использовать:

SELECT abalance INTO test_rec FROM mv_account_balance WHERE aid = current_aid;

⚠️ Но это избыточно, если данные часто обновляются. Для pgbench (тестовая БД) — не рекомендуется.

✅ 5. Проверить статистику и обновить ANALYZE

Убедитесь, что статистика актуальна:

ANALYZE pgbench_test;

ANALYZE pgbench_accounts;

Если статистика устарела, оптимизатор может ошибочно выбрать Bitmap Heap Scan вместо Index Only Scan.

📈 Ожидаемый результат после оптимизации

После создания индекса (bid, abalance):

• План выполнения должен измениться на:Nested Loop
• -> Index Scan on pgbench_accounts_pkey (aid = ...)
• -> Index Only Scan on pgbench_test_bid_abalance_idx (bid = acc.bid)
• Время выполнения снизится с ~366 мс до < 5–10 мс (в зависимости от кэширования).
• Heap Blocks = 0 — никаких чтений кучи.
• Память и I/O значительно сокращаются.

✅ Итоговая рекомендация

Выполните:

DROP INDEX IF EXISTS pgbench_test_idx;

CREATE INDEX pgbench_test_bid_abalance_idx ON pgbench_test (bid, abalance);

ANALYZE pgbench_test;

✅ Это — самая эффективная и простая оптимизация для вашего запроса.

✅ Покрывающий индекс — ключ к устранению высокой задержки.