Цель этого конспекта — понять не только отдельные термины, а всю цепочку целиком:
SQL → Planner → Scan → Index/TID → Heap → Visibility → ResultТекст построен вокруг практического взгляда разработчика приложения. Внутренний C API PostgreSQL намеренно не разбирается глубоко; важнее понимать, что происходит с запросом и почему PostgreSQL выбирает тот или иной план.
- Самая главная картинка
- Что делает индекс
- TID: как индекс указывает на строку
- Индекс — не сама таблица
- Что такое Heap в PostgreSQL
- Почему индекс ускоряет поиск
- Почему индекс не бесплатный
- HOT update
- Planner / Optimizer
- Что означает Scan
- Seq Scan
- Почему Seq Scan иногда лучше индекса
- Selectivity — селективность
- EXPLAIN и Index Scan
- Index Scan подробно
- Почему Index Scan становится дорогим
- Bitmap Scan
- Bitmap Index Scan vs Bitmap Heap Scan
- Почему Bitmap может быть лучше Index Scan
- Когда обычно выбираются Seq / Index / Bitmap
- Lossy Bitmap и Recheck Cond
- BitmapAnd и BitmapOr
- Correlation
- seq_page_cost и random_page_cost
- Index Only Scan
- Visibility Map
- Heap Fetches
- VACUUM и Index Only Scan
- Covering Index
- INCLUDE
- NULL и индексы
- Многоколоночные индексы
- Порядок колонок в B-tree
- Skip Scan в современных версиях PostgreSQL
- Несколько индексов или один составной
- Индексы по выражениям
- Статистика для выражений
- Partial Index
- Как PostgreSQL понимает, что partial index подходит
- Сортировка и индексы
- B-tree + ORDER BY + LIMIT
- CREATE INDEX CONCURRENTLY
- INVALID index
- Access Method и общий механизм индексации
- Что planner хочет знать об индексе
- Главная схема всей работы
- Тип индекса и способ Scan — это разные вещи
- Полезные практические примеры
- Как читать EXPLAIN на практике
- Чего не надо делать
- Главная mental model
- Краткая памятка
- Что реально стоит запомнить backend-разработчику
- Источники и актуализация
Представим обычную таблицу:
users
id name age
---------------------
1 Timur 25
2 Alex 31
3 Bob 19
4 Kate 42
5 John 25
...
Если индекса на age нет, PostgreSQL может последовательно прочитать таблицу и проверить каждую строку:
TABLE
┌─────┬────────┬─────┐
│ id │ name │ age │
├─────┼────────┼─────┤
│ 1 │ Timur │ 25 │ ← проверить
│ 2 │ Alex │ 31 │ ← проверить
│ 3 │ Bob │ 19 │ ← проверить
│ 4 │ Kate │ 42 │ ← проверить
│ 5 │ John │ 25 │ ← проверить
│ ... │ ... │ ... │
└─────┴────────┴─────┘
↓
прочитать таблицу
↓
проверить age = 25
↓
вернуть подходящие строки
В EXPLAIN такой план называется:
Seq Scan
Sequential Scan = последовательное сканирование таблицы.
Главная мысль:
Seq Scanне означает «плохой план». Он означает только, что PostgreSQL выбрал последовательное чтение таблицы. Для запроса, который возвращает большую долю таблицы, это часто именно то, что нужно.
Создадим индекс:
CREATE INDEX idx_users_age
ON users(age);Теперь существует отдельная структура индекса.
Упрощённо её можно представить так:
TABLE
┌─────┬────────┬─────┐
│ id │ name │ age │
├─────┼────────┼─────┤
│ 1 │ Timur │ 25 │
│ 2 │ Alex │ 31 │
│ 3 │ Bob │ 19 │
│ 4 │ Kate │ 42 │
│ 5 │ John │ 25 │
└─────┴────────┴─────┘
INDEX ON age
age → TID
-----------------------
19 → (block, item)
25 → (block, item)
25 → (block, item)
31 → (block, item)
42 → (block, item)
Индекс помогает быстро найти записи, соответствующие условию, а затем сообщить PostgreSQL, где находятся соответствующие tuple в таблице.
Это ключевая идея:
INDEX
key → TID
│
↓
TABLE / HEAP
│
↓
actual tuple
TID = Tuple Identifier.
Это идентификатор tuple в таблице.
Для встроенного heap table access method PostgreSQL TID состоит из двух частей:
TID = (block number, item number)
То есть условно:
TID = (12, 4)
можно представить как:
TABLE
block/page 12
┌───────────────────┐
│ item 1 │
│ item 2 │
│ item 3 │
│ item 4 ← target │
│ item 5 │
└───────────────────┘
Важно: TID — это не Transaction ID. Это указатель на физическое местоположение tuple в конкретной table access method; для стандартной PostgreSQL
heapэто номер блока и номер элемента внутри страницы.
Индекс обычно отвечает на вопрос:
«Где лежит подходящий tuple?»
а не:
«Дай мне всю таблицу».
Поэтому типичная цепочка выглядит так:
SQL
↓
INDEX
↓
TID
↓
TABLE / HEAP
↓
TUPLE
Это одна из самых важных вещей для понимания индексов.
Начинающий разработчик иногда представляет индекс как «вторую таблицу с нужными данными». Это слишком упрощённая модель.
Правильнее думать так:
INDEX
┌──────────────────┐
│ key → TID │
│ key → TID │
│ key → TID │
└────────┬─────────┘
│
│ TID
↓
TABLE / HEAP
┌──────────────────┐
│ actual tuple │
│ actual tuple │
│ actual tuple │
└──────────────────┘
Поэтому обычный Index Scan обычно включает два логических шага:
1. найти подходящие записи в индексе;
2. по TID получить соответствующие tuple из таблицы.
Официальная документация: при обычном index scan индекс сообщает расположение подходящих строк, после чего PostgreSQL обращается к таблице/heap, чтобы получить сами строки и проверить их видимость в текущем снимке MVCC.
Это фундаментальное отличие обычного
Index ScanотIndex Only Scan: обычному сканированию в общем случае нужен heap, а index-only scan старается получить всё необходимое из индекса и обращаться к heap только при необходимости.
Здесь очень легко запутаться из-за слова heap.
В алгоритмах и структурах данных heap часто означает специальную структуру данных — например, binary heap, на которой реализуют priority queue.
Минимальный пример на Python:
import heapq
numbers = [5, 1, 8, 2]
heapq.heapify(numbers)
print(heapq.heappop(numbers)) # 1
print(heapq.heappop(numbers)) # 2Здесь heapq — это именно структура данных для эффективного получения минимального элемента.
Визуально такая структура может выглядеть как дерево:
1
/ \
2 8
/
5
Это НЕ то, что PostgreSQL называет heap table.
В PostgreSQL термин heap в контексте таблицы означает стандартный table access method — способ хранения и доступа к tuple обычной таблицы.
То есть когда мы говорим:
heap table
heap tuple
heap page
heap fetch
речь идёт о хранении самой таблицы, а не о binary heap.
Упрощённо:
users
│
↓
HEAP TABLE
│
┌──────────┼──────────┐
↓ ↓ ↓
page 0 page 1 page 2
│ │ │
tuple tuple tuple
tuple tuple tuple
tuple tuple tuple
Каждая таблица и каждый индекс хранятся в отдельных файлах/отношениях, а страницы таблицы содержат физически размещённые tuple. Стандартная встроенная table access method называется heap.
Официальная документация PostgreSQL: текущая документация отдельно описывает встроенный
heaptable access method. Для обычных таблиц PostgreSQL использует страницы, а TID стандартного heap состоит из номера блока и номера элемента. PostgreSQL допускает другие table access methods, поэтому словоheapотносится именно к конкретному способу хранения, а не к универсальному закону всех возможных таблиц.
Потому что индекс и таблица решают разные задачи.
Таблица хранит данные строки:
id
name
age
email
created_at
...
Индекс хранит структуру, удобную для поиска:
ключ поиска → ссылка на tuple
Например:
INDEX ON age
19 → TID
20 → TID
25 → TID
25 → TID
31 → TID
42 → TID
А heap содержит саму строку:
TID
↓
(id=5, name='John', age=25, email='...')
Это позволяет использовать разные типы индексов для разных задач, сохраняя общую таблицу как источник исходных данных.
В русских переводах можно увидеть:
heap = куча
Но для разработчика PostgreSQL полезнее мысленно произносить:
heap table = стандартное физическое хранилище tuple таблицы
а не:
heap = binary heap
Это разные понятия.
Допустим, в таблице:
10 000 000 строк
и запрос:
SELECT *
FROM users
WHERE id = 123456;PostgreSQL может идти по таблице:
1
2
3
4
5
6
...
123456
...
10 000 000
Потенциально нужно проверить огромное количество строк.
INDEX
│
└── 123456
│
↓
TID
│
↓
HEAP
│
↓
нужный tuple
Поэтому индекс особенно полезен, когда условие позволяет быстро сократить пространство поиска.
Очень распространённая ошибка:
«Если индекс ускоряет SELECT, значит индекс всегда полезен».
Нет.
Индекс нужно:
- хранить;
- строить;
- читать;
- изменять;
- обслуживать.
Например:
INSERT INTO users ...;Без индекса:
INSERT
↓
TABLE
С индексом age:
INSERT
├──→ TABLE
│
└──→ INDEX ON age
То есть одна операция изменения данных может требовать обновления и таблицы, и индекса.
Если индекс есть:
CREATE INDEX idx_users_age
ON users(age);и мы делаем:
UPDATE users
SET age = 30
WHERE id = 1;изменяется и индексируемое значение:
UPDATE
↓
TABLE
↓
INDEX(age)
Но если мы делаем:
UPDATE users
SET name = 'Alice'
WHERE id = 1;индекс по age напрямую не меняет ключ поиска.
HOT = Heap-Only Tuple.
Это оптимизация PostgreSQL для некоторых обновлений.
Если изменяется колонка, которая не участвует в индексе, PostgreSQL в подходящих условиях может не создавать новую индексную запись.
Например, есть:
INDEX(age)
и:
UPDATE users
SET name = 'Timur'
WHERE id = 1;Логически можно представить:
HEAP
old tuple
↓
new tuple
INDEX(age)
не создаём новую индексную запись только из-за изменения name
Это уменьшает стоимость некоторых UPDATE и снижает рост индексов.
Важно: HOT — это не гарантия для любого UPDATE. PostgreSQL должен удовлетворить условиям, необходимым для HOT update, в том числе иметь возможность разместить новую версию tuple на той же странице. Поэтому правильная формулировка — «может применить HOT», а не «обязательно применяет HOT».
Когда мы пишем:
SELECT *
FROM users
WHERE age = 25;PostgreSQL не говорит:
«Есть индекс → обязательно используем индекс».
Он сначала формирует план выполнения.
Упрощённо:
SQL query
│
↓
Query Planner
│
┌───────────┼───────────┐
↓ ↓ ↓
Seq Scan Index Scan Bitmap Scan
│ │ │
└───────────┴───────────┘
↓
estimate cost
Planner оценивает доступные варианты и выбирает один из них по своей модели стоимости.
Например, условно:
Seq Scan cost = 100
Index Scan cost = 12
Bitmap Scan cost = 25
Это не реальные универсальные числа; это просто иллюстрация идеи.
Главная мысль: индекс существует физически, но способ его использования выбирает planner.
В этом контексте Scan означает примерно:
способ прочитать/получить нужные данные.
Главные узлы, которые тебе нужно узнавать в EXPLAIN:
Seq Scan
Index Scan
Bitmap Index Scan
Bitmap Heap Scan
Index Only Scan
Это не типы индекса. Это способы выполнения чтения.
Пример:
SELECT *
FROM users
WHERE age = 25;Если planner выбрал Seq Scan, логика примерно такая:
HEAP TABLE
│
↓
read page 1
↓
read page 2
↓
read page 3
↓
...
↓
read last page
│
↓
check WHERE for rows
На каждой подходящей строке:
age = 25 ?
│
┌┴───────┐
↓ ↓
YES NO
↓ ↓
return skip
EXPLAIN (COSTS OFF)
SELECT *
FROM users
WHERE age = 25;может показать:
Seq Scan on users
Filter: (age = 25)
Представим:
users = 10 000 000 rows
и условие:
WHERE active = trueа active = true у:
8 000 000 строк
Тогда через индекс может получиться:
INDEX
↓
миллионы индексных записей
↓
миллионы TID
↓
HEAP
↓
миллионы строк
В таком случае последовательное чтение таблицы может оказаться дешевле:
HEAP
↓
прочитать страницы последовательно
Поэтому грубая, но полезная модель такая:
мало результатов
→ Index Scan часто привлекателен
среднее количество результатов
→ Bitmap Scan часто привлекателен
очень много результатов
→ Seq Scan часто привлекателен
Но это не закон. Реальный выбор зависит от оценки стоимости, статистики, физического расположения данных, cache и других факторов.
Selectivity можно понимать как степень, с которой условие уменьшает число строк, которые нужно вернуть.
Пример 1:
WHERE id = 123Из:
10 000 000 rows
получаем:
1 row
Это очень селективное условие.
Пример 2:
WHERE gender = 'male'может вернуть:
5 000 000 rows
Пример 3:
WHERE age >= 18может вернуть:
9 500 000 rows
Чем больше данных подходит условию, тем менее привлекательным может становиться обычный index scan.
Пример:
EXPLAIN (COSTS OFF)
SELECT *
FROM users
WHERE id = 42;может показать:
Index Scan using idx_users_id on users
Index Cond: (id = 42)
Читаем сверху вниз:
Index Scan
↓
взяли индекс idx_users_id
↓
нашли записи, где id = 42
↓
получили TID
↓
получили tuple из heap
Пусть есть:
CREATE INDEX idx_users_id
ON users(id);и запрос:
SELECT *
FROM users
WHERE id = 42;Упрощённая схема:
INDEX
┌──────────────────┐
│ id = 42 │
└────────┬─────────┘
│
↓
TID
│
↓
HEAP TABLE
│
↓
target tuple
│
↓
result
По шагам:
1. Найти ключ 42 в индексе.
2. Получить TID.
3. По TID найти физический tuple в таблице.
4. Проверить, допустима ли эта версия tuple для текущей MVCC snapshot.
5. Вернуть строку.
6. Продолжить, если есть ещё подходящие записи.
Официальная документация PostgreSQL: обычный index scan не считается полным обходом таблицы только потому, что индекс найден. Индекс возвращает ссылки на соответствующие tuple, а table/heap access method получает сами tuple. Поэтому
Index Scanобычно означает работу сразу с двумя структурами: индексом и таблицей.
У тебя есть:
INDEX
age=25 → TID
Но тебе нужен результат:
id=5
name='John'
age=25
email='john@example.com'
Если индекс хранит только age + ссылку, то name, email и другие данные нужно получить из heap.
Представим, индекс нашёл 100 000 подходящих tuple.
Но эти tuple разбросаны по страницам:
TID #1 → page 8
TID #2 → page 900
TID #3 → page 42
TID #4 → page 7000
TID #5 → page 123
...
Тогда доступ выглядит условно так:
INDEX
↓
page 8
↓
page 900
↓
page 42
↓
page 7000
↓
page 123
↓
...
Это random access — случайный доступ к страницам. Sequential = Читаем страницы подряд: 1 → 2 → 3 → 4 → 5 = Очень быстро; Random = Читаем страницы вразброс: 8 → 9421 → 17 → 5500 = Гораздо медленнее. Если подходящих строк много, такой сценарий может быть дороже, чем более организованное чтение страниц. Здесь и становится особенно интересен bitmap scan.
Создание битмапа:
СУБД смотрит в индекс и строит в памяти строку из 8 бит, где 1 — страница содержит сотрудника IT, а 0 — нет:
Битмап: [ 1, 0, 1, 1, 0, 0, 1, 0 ]
Страница: 1 2 3 4 5 6 7 8
Главная идея:
Сначала найти множество подходящих TID, затем собрать информацию о том, какие страницы таблицы нужно читать, и после этого обработать эти страницы более организованно.
Вместо:
найти TID
↓
сразу идти в heap
↓
найти следующий TID
↓
снова идти в heap
делаем:
INDEX
↓
найти подходящие TID
↓
построить bitmap
↓
определить нужные heap pages
↓
прочитать страницы
↓
получить tuple
Это два разных узла.
Работает с индексом:
INDEX
↓
найти matching entries
↓
получить TID
↓
записать TID в bitmap
Пример:
Bitmap Index Scan on idx_users_age
Index Cond: (age <= 30)
Работает уже с самой таблицей/heap:
BITMAP
↓
какие страницы нужны?
↓
прочитать страницы
↓
получить tuple
Поэтому типичный план:
Bitmap Heap Scan on users
Recheck Cond: (age <= 30)
-> Bitmap Index Scan on idx_users_age
Index Cond: (age <= 30)
Читать его удобно снизу вверх:
Bitmap Index Scan
↓
создаёт bitmap
↓
Bitmap Heap Scan
↓
читает таблицу
Официальная документация: bitmap index scan может добавить найденные TID в
TIDBitmap. Затем bitmap heap scan посещает фактические строки таблицы. При объединении нескольких индексов отдельные bitmap могут быть объединены операциямиAND/OR.
Допустим, индекс нашёл:
page 10 row 1
page 10 row 7
page 10 row 13
page 10 row 25
page 500 row 3
page 500 row 9
page 500 row 20
При концептуально наивном index scan можно представить много обращений к одной и той же странице:
INDEX
↓
page 10
↓
page 10
↓
page 10
↓
page 10
↓
page 500
↓
page 500
↓
page 500
Bitmap позволяет сначала заметить:
нужны страницы 10 и 500
и затем организовать чтение так, чтобы страницы обрабатывались более эффективно:
page 10 → прочитать
page 500 → прочитать
В реальности planner учитывает стоимость всего плана; bitmap не является «всегда более быстрым Index Scan».
Удобная учебная модель:
| Ситуация | Часто подходящий вариант |
|---|---|
| Очень мало совпадений | Index Scan |
| Совпадений уже заметно больше, особенно на разных страницах | Bitmap Heap Scan + Bitmap Index Scan |
| Нужно прочитать большую долю таблицы | Seq Scan |
| Все нужные данные уже есть в индексе | Возможен Index Only Scan |
Но planner не работает по формуле «10 строк → Index Scan, 1000 → Bitmap».
Он сравнивает стоимость планов.
На решение влияют:
- оценка количества строк;
- стоимость чтения страниц;
- физическая локальность данных;
- статистика;
- наличие других индексов;
- ORDER BY;
- LIMIT;
- возможность index-only scan;
- cache;
- стоимость CPU;
- work_mem и особенности bitmap.
Это один из самых сложных моментов статьи.
Представим, что bitmap способен помнить точные tuple:
page 10:
tuple 3
tuple 7
tuple 8
page 20:
tuple 1
tuple 4
Это очень полезно: мы знаем не только страницу, но и потенциальные позиции tuple.
Размер используемой памяти ограничен, в частности, параметром:
work_mem
Если кандидатов очень много, bitmap может стать большим.
В некоторых случаях PostgreSQL переключается на lossy representation.
Вместо точной информации:
page 10:
rows 3, 7, 8
можно хранить более грубую информацию:
page 10 = potentially relevant
То есть теперь мы знаем:
«На этой странице потенциально есть подходящие строки».
Но уже не знаем точно, какие именно.
PostgreSQL читает всю соответствующую страницу и повторно проверяет условие:
BITMAP
↓
page 10
↓
прочитать page 10
↓
проверить строки
├── row 1 → подходит
├── row 2 → не подходит
├── row 3 → подходит
├── row 4 → не подходит
└── ...
Поэтому мы видим:
Recheck Cond: (...)
Наличие Recheck Cond в выводе bitmap plan не означает, что обязательно каждая строка фактически потребовала дорогой повторной проверки в обычной точной bitmap-ситуации. Эта строка является частью описания узла и условий recheck; фактическая необходимость повторной проверки зависит от того, насколько точным оказался bitmap и от требований access method.
Одно из преимуществ bitmap scans — возможность объединять несколько индексов.
Пусть есть:
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_b ON t(b);и запрос:
SELECT *
FROM t
WHERE a <= 100
AND b = 'a';Возможна схема:
idx_a
↓
bitmap A
\
\
AND → final bitmap
/
/
bitmap B
↑
idx_b
В EXPLAIN это может выглядеть примерно так:
Bitmap Heap Scan on t
Recheck Cond: ((a <= 100) AND (b = 'a'))
-> BitmapAnd
-> Bitmap Index Scan on idx_a
Index Cond: (a <= 100)
-> Bitmap Index Scan on idx_b
Index Cond: (b = 'a')
Для AND нужен набор строк, который есть в обоих bitmap.
bitmap A:
1 1 0 1 0 1
bitmap B:
1 0 0 1 1 1
AND:
1 0 0 1 0 1
Для OR нужен набор строк, который есть хотя бы в одном bitmap.
bitmap A:
1 1 0 1 0 0
bitmap B:
0 0 1 1 0 1
OR:
1 1 1 1 0 1
Официальная документация: PostgreSQL может сканировать несколько индексов, формировать для них bitmap и затем объединять bitmap через
ANDиOR. При этом итоговый порядок строк исходных индексов теряется, потому что строки таблицы посещаются в физическом порядке.
Теперь важная статистика — correlation.
Она показывает, насколько порядок значений столбца связан с физическим порядком tuple в таблице.
Посмотреть статистику можно через pg_stats:
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 't';Представим:
id
1
2
3
4
5
6
7
8
9
10
и физически строки тоже размещены примерно в таком порядке:
page 1: 1 2 3
page 2: 4 5 6
page 3: 7 8 9
page 4: 10
Тогда:
значение id растёт
↓
физические страницы в среднем тоже идут вперёд
Correlation будет близка по абсолютному значению к 1.
Если данные лежат хаотично:
page 1: 984, 17, 502
page 2: 3, 777, 201
page 3: 89, 44, 900
Correlation может быть близка к 0.
Потому что index scan возвращает TID, а TID указывает на физическую страницу.
Если соседние ключи индекса часто указывают на соседние страницы таблицы:
INDEX
↓
page 10
↓
page 11
↓
page 12
↓
page 13
то доступ более локальный.
Если они разбросаны:
INDEX
↓
page 900
↓
page 3
↓
page 750
↓
page 20
доступ более случайный.
Поэтому correlation участвует в оценке стоимости индексного доступа.
Planner различает стоимость последовательного и случайного чтения страниц.
Упрощённо:
SEQUENTIAL ACCESS
page 1
page 2
page 3
page 4
page 5
против:
RANDOM ACCESS
page 1
↓
page 900
↓
page 40
↓
page 700
Для этой модели стоимости используются, в частности:
seq_page_cost
random_page_cost
Их значения можно изменить, а также настроить на уровне tablespace.
Важно: на SSD разница между последовательным и случайным чтением обычно меньше, чем на HDD, но она не становится автоматически нулевой. Кроме того, planner учитывает не только эти два параметра.
Теперь один из самых интересных сценариев.
Представим:
CREATE INDEX idx_users_id_age
ON users(id, age);и запрос:
SELECT age
FROM users
WHERE id = 42;Нам нужны:
id
age
и индекс уже содержит эти значения.
Тогда потенциально можно не читать heap для получения самих данных:
INDEX
┌──────────────────┐
│ id = 42 │
│ age = 25 │
│ TID = ... │
└────────┬─────────┘
│
↓
данные уже здесь
│
↓
RESULT
Такой план называется:
Index Only Scan
Возникает проблема: видима ли эта версия tuple для текущей транзакции?
Индекс сам по себе не является полной заменой MVCC visibility information, необходимой для решения этого вопроса.
Поэтому PostgreSQL использует Visibility Map для таблицы.
Упрощённо можно представить:
HEAP PAGES
page 1 → all visible
page 2 → not all visible
page 3 → all visible
page 4 → all visible
page 5 → not all visible
или:
1 1 0 1 1 0
где 1 означает, что соответствующая страница известна как all-visible.
В стандартной heap table visibility map хранится отдельно от основного файла таблицы.
INDEX
↓
нашли нужный index entry
↓
узнали TID
↓
определили heap page
↓
проверили Visibility Map
┌──────────────────────┐
│ page all-visible? │
└──────────┬───────────┘
│
┌────────┴────────┐
↓ ↓
YES NO
↓ ↓
не нужно читать heap идём в heap
│ │
└────────┬────────┘
↓
RESULT
Поэтому термин Index Only Scan означает:
PostgreSQL пытается получить возвращаемые данные из индекса и избежать heap fetch там, где это безопасно.
Он не означает математическую гарантию «heap никогда не читается».
При:
EXPLAIN (ANALYZE)
SELECT age
FROM users
WHERE id = 42;можно увидеть:
Index Only Scan using idx_users_id_age on users
Index Cond: (id = 42)
Heap Fetches: 0
Heap Fetches: 0 означает, что для выполнения этого index-only scan фактических обращений к heap не понадобилось.
А если:
Heap Fetches: 8000
это означает, что PostgreSQL был вынужден обратиться к heap для проверки/получения необходимой информации для большого числа кандидатов.
Практически:
Heap Fetches → 0
обычно хорошо для сценария, ради которого создавали index-only scan.
VACUUM поддерживает информацию, которая используется для visibility map.
Упрощённая цепочка:
VACUUM
↓
обновление/поддержка visibility information
↓
больше страниц могут быть известны как all-visible
↓
Index Only Scan
↓
меньше heap fetches
Поэтому для хорошо подходящих read-heavy таблиц регулярное обслуживание может влиять не только на очистку мёртвых tuple, но и на эффективность index-only scans.
Covering index — практическое название индекса, который содержит всё, что необходимо конкретному запросу для его эффективного выполнения без чтения основной таблицы, когда это разрешает visibility map.
Пример запроса:
SELECT id, name
FROM users
WHERE email = 'a@example.com';Индекс только:
CREATE INDEX idx_users_email
ON users(email);содержит:
email ✅
id ❌
name ❌
Поэтому для результата нужны дополнительные данные из heap.
Можно использовать:
CREATE INDEX idx_users_email
ON users(email)
INCLUDE (id, name);Упрощённо:
INDEX
search key:
email
payload:
id
name
Здесь:
email— индексный ключ поиска;idиname— дополнительные данные для покрытия запроса.
INCLUDE-колонки не становятся дополнительными ключами поиска и не участвуют в уникальности.
CREATE UNIQUE INDEX idx_users_email
ON users(email)
INCLUDE (name);Уникальность определяется по:
email
а не по:
email + name
Поэтому:
email = 'a@example.com', name = 'Timur'
email = 'a@example.com', name = 'Bob'
всё равно конфликтуют с точки зрения UNIQUE, потому что ключ один:
UNIQUE KEY = email
INCLUDE = name
Официальная документация:
INCLUDEпозволяет хранить неключевые колонки в индексе для поддержки index-only scans, не превращая их в часть поискового ключа.
NULL в SQL означает отсутствие/неизвестность значения и ведёт себя не как обычное конкретное значение.
Например:
WHERE age = NULL— не тот способ, которым проверяют NULL.
Используют:
WHERE age IS NULL;и:
WHERE age IS NOT NULL;Для каждого access method нужно определить, как он работает с NULL.
Planner должен знать, способен ли данный индекс поддержать условия:
IS NULL
IS NOT NULL
В PostgreSQL access methods сообщают свои возможности planner, в том числе поддержку поиска по NULL.
Поэтому при чтении документации по конкретному типу индекса важно смотреть не только на «умеет ли он =», но и на поддержку NULL и других операторов.
Можно создать один индекс сразу по нескольким колонкам:
CREATE INDEX idx_t_a_b
ON t(a, b);Это один составной индекс, а не два независимых индекса.
Упрощённо B-tree (a, b) можно представить как лексикографический порядок:
(a, b)
(1, 'a')
(1, 'b')
(1, 'c')
(2, 'a')
(2, 'd')
(3, 'a')
(3, 'b')
...
Для PostgreSQL 18 многоколоночные индексы поддерживаются, в частности, B-tree, GiST, GIN и BRIN, но поведение при выборе конкретной колонки зависит от access method.
Самое важное для обычного мышления:
В B-tree ведущие (leftmost / leading) колонки особенно важны.
Для индекса:
CREATE INDEX idx_t_a_b
ON t(a, b);естественно подходят условия вроде:
WHERE a = 10и:
WHERE a = 10
AND b = 20Почему?
Потому что B-tree физически организует ключ как:
(a, b)
то есть сначала группировка по a, а внутри неё — по b.
Фраза:
«индекс
(a,b)вообще бесполезен дляWHERE b = 20»
слишком категорична для современных PostgreSQL.
В PostgreSQL 18 у B-tree появился/поддерживается skip scan — planner в определённых случаях может повторно выполнять логические поиски по ведущей колонке и пропускать большие участки индекса, даже если у запроса нет обычного условия на первую колонку.
Но это не отменяет фундаментальную идею:
для обычного B-tree ведущие колонки очень важны,
а skip scan используется только когда planner ожидает реальную выгоду.
Пусть есть индекс:
CREATE INDEX idx_t_ab
ON t(a, b);и запрос:
SELECT *
FROM t
WHERE b = 20;Раньше полезно было мыслить так:
нет условия по a
→ индекс `(a,b)` трудно использовать эффективно
Теперь модель точнее:
planner
↓
если distinct(a) мало
↓
может выполнить повторные поиски:
a = value1 AND b = 20
a = value2 AND b = 20
a = value3 AND b = 20
...
Если значений a очень много, такой подход может быть дороже Seq Scan и не использоваться.
Поэтому опять решает planner.
Допустим, есть:
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_b ON t(b);и запрос:
SELECT *
FROM t
WHERE a = 10
AND b = 20;PostgreSQL может использовать оба индекса через bitmap:
idx_a
↓
bitmap A
\
\
BitmapAnd
/
/
bitmap B
↑
idx_b
Другой вариант:
CREATE INDEX idx_ab
ON t(a, b);Нет универсального ответа.
Составной индекс:
(a, b)
может быть очень хорош для конкретного семейства запросов.
Два отдельных индекса:
(a)
(b)
дают больше гибкости для независимых запросов и могут комбинироваться через bitmap.
Официальная документация: PostgreSQL прямо рассматривает это как trade-off — иногда лучше multicolumn index, иногда несколько single-column indexes с последующим объединением bitmap.
Представим:
CREATE INDEX idx_users_name
ON users(name);и запрос:
SELECT *
FROM users
WHERE lower(name) = 'timur';Индекс построен на:
name
а запрос требует поиска по:
lower(name)
Поэтому PostgreSQL не может автоматически считать это обычным name = constant.
Можно сделать:
CREATE INDEX idx_users_lower_name
ON users(lower(name));Теперь логическая форма совпадает:
QUERY
lower(name) = 'timur'
│
↓
INDEX
lower(name)
Упрощённо:
TABLE
name
------
Timur
TIMUR
timur
Alice
Bob
↓ lower()
INDEX
lower(name)
------------
alice
bob
timur
timur
timur
Planner нужен не только сам индекс, но и статистика о распределении индексируемых значений.
Для:
lower(name)
распределение может отличаться от исходного:
name
Например, имена:
Timur
TIMUR
timur
после lower() превращаются в одинаковый ключ.
Planner должен понимать, насколько условие селективно.
Поэтому выражение индекса связано с собственной статистикой, которую PostgreSQL может использовать при планировании.
Представим:
1 000 000 orders
из них:
950 000 = completed
50 000 = pending
Если нас постоянно интересуют только pending, можно индексировать не всю таблицу.
CREATE INDEX idx_pending_orders
ON orders(status)
WHERE status = 'pending';Теперь индекс содержит только подмножество строк, удовлетворяющее predicate.
Упрощённо:
TABLE
──────────────────────────────
950 000 completed
50 000 pending
──────────────────────────────
PARTIAL INDEX
────────────────
50 000 pending
────────────────
Плюсы потенциально:
меньше размер индекса
↓
меньше работы с индексом
↓
может быть дешевле чтение
Особенно интересен такой подход, когда нужная часть данных небольшая и запросы стабильно обращаются именно к ней.
Создадим:
CREATE INDEX idx_pending
ON orders(id)
WHERE status = 'pending';Запрос:
SELECT *
FROM orders
WHERE id = 123;не содержит информации:
status = 'pending'
Поэтому planner не может просто предположить, что строка входит в partial index.
А вот:
SELECT *
FROM orders
WHERE status = 'pending'
AND id = 123;уже логически подразумевает predicate индекса.
Получается:
query condition
↓
status = pending
↓
predicate partial index
↓
index potentially applicable
Практическое правило: partial index работает не потому, что «planner знает, что это полезный редкий индекс», а потому, что он может доказать, что условие запроса совместимо с predicate индекса.
Запрос:
SELECT *
FROM users
ORDER BY age;Без подходящего индекса возможен план:
Seq Scan
↓
получить строки
↓
Sort
↓
готовый результат
В EXPLAIN:
Sort
Sort Key: age
-> Seq Scan on users
Если есть:
CREATE INDEX idx_users_age
ON users(age);B-tree хранит ключи в упорядоченной форме.
Упрощённо:
19
20
21
22
23
24
25
26
...
Поэтому PostgreSQL может получить строки в нужном порядке напрямую из индекса:
INDEX
↓
19
20
21
22
23
...
без отдельной операции Sort.
В актуальной документации PostgreSQL 18 прямо указано: из поддерживаемых index types именно B-tree может выдавать matching rows в отсортированном порядке для обычного
ORDER BY.
Это особенно полезно для:
SELECT *
FROM users
ORDER BY age
LIMIT 10;Без подходящего индекса потенциально нужно:
прочитать много строк
↓
отсортировать
↓
взять первые 10
С подходящим B-tree planner может сделать концептуально:
INDEX
↓
первые значения в нужном порядке
↓
1
2
3
...
10
↓
STOP
Это очень сильный сценарий: LIMIT позволяет не проходить остаток индекса/таблицы, если нужный порядок уже предоставляется индексом.
Обычное создание индекса:
CREATE INDEX idx_users_age
ON users(age);и конкурентное создание:
CREATE INDEX CONCURRENTLY idx_users_age
ON users(age);решают одну задачу, но с разными требованиями к блокировкам и процессу построения.
При построении обычного индекса PostgreSQL берёт сильную блокировку, ограничивающую конкурентные изменения таблицы во время ключевой части построения.
Упрощённо для приложения это означает:
ACTIVE TABLE
↓
CREATE INDEX
↓
изменения таблицы могут ждать
На большой production-таблице это может быть неприятно.
CREATE INDEX CONCURRENTLY idx_users_age
ON users(age);Он предназначен для сценариев, когда нужно уменьшить блокирование обычных операций с таблицей.
Упрощённая идея:
CONCURRENTLY
readers → продолжают работать
writers → продолжают работать в допустимых рамках
index build → работает сложнее и дольше
Это не означает «вообще без блокировок» и не означает «бесплатно».
Построение требует более сложного процесса; PostgreSQL делает несколько фаз/проходов и должен учитывать конкурентные транзакции.
Поэтому это trade-off:
обычный CREATE INDEX
→ проще и обычно быстрее построить
→ сильнее влияет на обычную работу таблицы
CREATE INDEX CONCURRENTLY
→ меньше блокирования обычной работы
→ сложнее и обычно дольше
Также CREATE INDEX CONCURRENTLY нельзя запускать внутри обычного transaction block.
Если построение индекса, особенно concurrent index build, завершается нештатно, может остаться индекс, помеченный как INVALID.
Проверить такие индексы можно:
SELECT
indexrelid::regclass AS index_name,
indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;Упрощённо:
INVALID
↓
индексная структура физически может существовать
↓
но planner не должен считать её нормальным действующим индексом
Такой объект нужно расследовать и обычно удалить/перестроить в зависимости от ситуации.
В начале исходной статьи есть важная архитектурная идея.
PostgreSQL отделяет:
- общий механизм работы с индексами;
- конкретный index access method — способ организации самого индекса.
Упрощённо:
PostgreSQL
│
┌─────────┴─────────┐
↓ ↓
общий index machinery Planner
│
↓
конкретный access method
│
┌─────┼─────┬─────┐
↓ ↓ ↓ ↓
B-tree Hash GIN GiST ...
Он работает с общими понятиями:
TID
tuple visibility
heap/table access
bitmap
scan execution
Access method отвечает за свою внутреннюю механику:
как построить индекс
как хранить индексные страницы
как искать
какие операторы поддерживаются
можно ли делать plain index scan
можно ли bitmap scan
можно ли вернуть данные для index-only scan
как оценивать стоимость
как работать с блокировками
как записывать WAL
Официальный интерфейс PostgreSQL позволяет access method поддерживать обычные index scans, bitmap scans или оба варианта.
Planner должен понимать возможности конкретного access method.
Например:
Можно ли искать по этому оператору?
Можно ли делать range search?
Поддерживаются ли NULL-поиски?
Можно ли делать plain Index Scan?
Можно ли делать Bitmap Scan?
Можно ли вернуть данные для Index Only Scan?
Можно ли вернуть строки в определённом порядке?
Поддерживаются ли несколько колонок?
Поддерживается ли UNIQUE?
Поддерживается ли INCLUDE?
В PostgreSQL эти свойства являются частью информации о свойствах index access method.
Это важно, потому что два индекса могут существовать физически, но давать planner совершенно разные возможности.
Пусть есть:
SELECT name
FROM users
WHERE age = 25;и индекс:
CREATE INDEX idx_users_age
ON users(age);Упрощённо весь процесс можно представить так:
SQL
│
↓
Planner
│
┌────────────┼────────────┐
↓ ↓ ↓
Seq Scan Index Scan Bitmap plan
│ │ │
│ ↓ ↓
│ INDEX Bitmap Index Scan
│ │ │
│ ↓ ↓
│ TID TID Bitmap
│ │ │
│ └────┬───────┘
│ ↓
└────────────→ HEAP ←────────────┐
│ │
↓ │
visibility │
│ │
↓ │
result ←─────────┘
Если данные для результата уже находятся в индексе и visibility map позволяет избежать heap fetch, появляется другой путь:
SQL
↓
Planner
↓
Index Only Scan
↓
INDEX
↓
Visibility Map
↓
RESULT
Это очень частая путаница.
Например:
B-tree
Hash
GIN
GiST
SP-GiST
BRIN
Например:
Seq Scan
Index Scan
Bitmap Index Scan
Bitmap Heap Scan
Index Only Scan
Поэтому корректно говорить:
B-tree index + Index Scan
или:
B-tree index + Bitmap Index Scan
Нельзя приравнивать:
Index = Index Scan
Это разные уровни абстракции.
Таблица:
CREATE TABLE users (
id bigint,
name text,
age integer,
active boolean
);Индекс:
CREATE INDEX idx_users_id
ON users(id);Запрос:
SELECT *
FROM users
WHERE id = 42;Если условие очень селективно, Index Scan часто оказывается естественным кандидатом:
INDEX
↓
TID
↓
HEAP
↓
1 row
SELECT *
FROM users
WHERE age <= 40;Если совпадений уже много, planner может предпочесть bitmap plan:
Bitmap Index Scan
↓
bitmap
↓
Bitmap Heap Scan
↓
many rows
SELECT *
FROM users
WHERE age >= 18;Если подходит большая часть таблицы, planner может выбрать:
Seq Scan
Это не ошибка — это может быть дешевле.
Запрос:
SELECT *
FROM users
WHERE lower(email) = lower($1);Индекс:
CREATE INDEX idx_users_lower_email
ON users(lower(email));Здесь index expression соответствует форме поиска.
Пусть:
orders = 10 000 000
pending = 200 000
completed = 9 800 000
Частый запрос:
SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;Можно рассмотреть partial index:
CREATE INDEX idx_pending_orders
ON orders(created_at DESC)
WHERE status = 'pending';Запрос:
SELECT *
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;Один из естественных кандидатов для исследования:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);Почему это интересно:
user_id = 100
↓
нужная часть индекса
↓
created_at уже упорядочен
↓
LIMIT 20
↓
можно остановиться рано
Но конечное решение всё равно следует подтверждать EXPLAIN (ANALYZE, BUFFERS) на реальных данных.
Запрос:
SELECT id, status
FROM orders
WHERE user_id = 100;Можно исследовать:
CREATE INDEX idx_orders_user
ON orders(user_id)
INCLUDE (id, status);Потенциальная цель:
INDEX
├── user_id ← search key
├── id ← payload
└── status ← payload
и при подходящей visibility map:
Index Only Scan
Начни не с цифр, а со структуры дерева.
Index Scan using idx_users_id on users
Index Cond: (id = 42)
Читаем:
1. Использован индекс.
2. Условие поиска — id = 42.
3. Индекс возвращает позиции tuple.
4. Таблица используется для получения строк/проверки visibility.
Bitmap Heap Scan on users
Recheck Cond: (age <= 30)
-> Bitmap Index Scan on idx_users_age
Index Cond: (age <= 30)
Читаем снизу вверх:
idx_users_age
↓
найдены TID
↓
bitmap
↓
Bitmap Heap Scan
↓
heap pages
↓
result
Seq Scan on users
Filter: (age <= 40)
Это значит:
таблица читается последовательно,
а Filter применяется к строкам.
Index Only Scan using idx_users_id_age on users
Index Cond: (id = 42)
Heap Fetches: 0
Это означает:
данные доступны из индекса
и heap fetch в этом выполнении не потребовался.
Нет.
Проверяй:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;Нет.
Если запросу нужна большая часть таблицы, последовательное чтение может быть правильным решением.
Нет.
Больше индексов означает:
больше места
+
больше стоимости INSERT
+
больше стоимости UPDATE
+
больше стоимости DELETE
+
больше работы при обслуживании
Нет.
Bitmap нужен не для того, чтобы быть «более продвинутым индексом», а для другого паттерна доступа.
Нет.
Он может читать heap, если это необходимо для visibility.
Нет.
Для B-tree порядок колонок определяет организацию ключей и сильно влияет на полезность индекса.
Запомни одну цепочку:
QUERY
│
↓
PLANNER
│
┌──────────────┼───────────────┐
↓ ↓ ↓
Seq Scan Index Scan Bitmap Scan
│ │ │
│ ↓ ↓
│ INDEX Bitmap Index Scan
│ │ │
│ ↓ ↓
│ TID BITMAP
│ │ │
└──────────┐ │ ┌───────────┘
↓ ↓ ↓
HEAP TABLE
│
↓
VISIBILITY
│
↓
RESULT
А для покрывающего сценария:
QUERY
↓
PLANNER
↓
INDEX ONLY SCAN
↓
INDEX
↓
Visibility Map
↓
RESULT
TABLE → проверить строки
Часто подходит, когда нужна большая доля таблицы.
INDEX → TID → HEAP → row
Часто привлекателен для небольшого числа результатов.
INDEX → TIDs → BITMAP → HEAP PAGES → rows
Часто полезен для большего набора результатов и для объединения нескольких индексов.
INDEX → Visibility Map → RESULT
Heap может не понадобиться, если все необходимые данные есть в индексе и visibility map позволяет безопасно не читать heap.
Хороший кандидат для:
=
<
<=
>
>=
BETWEEN
ORDER BY
и часто используется как универсальный индекс для обычных запросов.
Для запросов вроде:
WHERE lower(email) = ...можно создать:
CREATE INDEX ...
ON users(lower(email));Когда нужен только кусок таблицы:
CREATE INDEX ...
ON orders(created_at)
WHERE status = 'pending';Когда нужно хранить дополнительные данные в индексе, но не делать их частью ключа:
CREATE INDEX ...
ON orders(user_id)
INCLUDE (id, status);Тебе пока не нужно держать в голове весь внутренний C API PostgreSQL.
Гораздо важнее уметь ответить на следующие вопросы.
Например:
WHERE user_id = 1231 строка?
100 строк?
1 000 000 строк?
простая колонка?
несколько колонок?
выражение?
частичное подмножество?
Это может сильно изменить ценность B-tree.
Если запрос читает мало колонок и индекс может их покрыть, INCLUDE иногда очень полезен.
Проверяй:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;Вот это уже уровень понимания индексов.
Основные утверждения про современные возможности PostgreSQL сверены с официальной документацией PostgreSQL 18.
- Indexes and
ORDER BY - Combining Multiple Indexes / Bitmap Scans
- Multicolumn Indexes
- Indexes on Expressions
- Partial Indexes
- Index-Only Scans and Covering Indexes
- Index Access Method Functions
- Table Access Method Interface Definition
- Database File Layout
- Using EXPLAIN
Исходный материал исторически привязан к PostgreSQL 9.6 и старше современных версий. Основные идеи остаются полезными, но современные версии расширили возможности PostgreSQL.
Особенно важно помнить:
- Поведение многоколоночных B-tree нельзя больше описывать абсолютной фразой «без первой колонки индекс вообще не используется»: PostgreSQL 18 умеет применять skip scan в подходящих случаях.
- Возможности конкретных index access methods нужно сверять с документацией версии PostgreSQL, которую использует проект.
- Для анализа реального приложения лучше использовать
EXPLAIN (ANALYZE, BUFFERS), а не делать вывод только по теории.
SQL QUERY
│
↓
PLANNER
│
┌────────────────┼────────────────┐
↓ ↓ ↓
Seq Scan Index Scan Bitmap Scan
│ │ │
│ ↓ ↓
│ INDEX Bitmap Index Scan
│ │ │
│ ↓ ↓
│ TID BITMAP
│ │ │
└────────────────┼────────────────┘
↓
HEAP TABLE
│
↓
VISIBILITY
│
↓
RESULT
И ещё одна связка:
INDEX TYPE ≠ SCAN TYPE
B-tree / GIN / GiST / BRIN / Hash
│
↓
конкретный access method
│
↓
Index Scan / Bitmap Scan / Index Only Scan
Самая полезная мысль для разработчика:
Индекс — это не «ускоритель вообще». Это структура, которая помогает planner быстрее находить нужные tuple. Дальше PostgreSQL решает, как именно их читать: напрямую через Index Scan, сначала собрать TID в bitmap, прочитать всю таблицу через Seq Scan или, при подходящих условиях, получить результат почти целиком из самого индекса через Index Only Scan.