Индексы в PostgreSQL: как работают и почему не используются

Зачем нужен индекс и как он устроен

Индекс — это отдельная структура данных, которая хранит значения проиндексированного столбца вместе со ссылками на строки таблицы (ctid — физический адрес строки в heap). Без индекса PostgreSQL делает Seq Scan — читает всю таблицу целиком, страницу за страницей. С индексом можно сразу перейти к нужным строкам, минуя полный перебор.

По умолчанию PostgreSQL создаёт B-tree индекс. Это сбалансированное дерево, где на каждом уровне узлы содержат диапазоны значений и указатели на дочерние узлы. Поиск идёт за O(log n) сравнений: спустились с корня до листа — нашли указатель на строку. B-tree отлично работает для операций равенства и сравнения (=, <, >, BETWEEN), а также для сортировки, потому что значения в дереве упорядочены.

Кроме B-tree есть и другие типы: Hash — только для равенства, GIN — для полнотекстового поиска и массивов, GiST — для геометрии и диапазонов, BRIN — для больших таблиц с физически упорядоченными данными типа временных рядов. На собеседовании чаще всего спрашивают именно про B-tree, потому что это индекс по умолчанию и рабочая лошадка.

Как индекс приводит к чтению строки

Индекс хранит не сами данные, а указатель на строку в heap-таблице. После того как индекс нашёл нужный ctid, база всё равно должна сходить в heap, чтобы прочитать актуальную строку — проверить видимость по MVCC, достать остальные столбцы. Это называется heap fetch, и именно из-за него индекс не бесплатен: для каждой найденной записи может понадобиться отдельный случайный доступ к диску.

Index-only scan: когда поход в heap не нужен

Если все нужные для запроса столбцы есть прямо в индексе (например, вы делаете SELECT id FROM users WHERE id > 100, а индекс по id), PostgreSQL может обойтись Index Only Scan и не читать heap вовсе — но только если страница в visibility map отмечена как «все строки видимы», иначе всё равно придётся проверять видимость по heap.

Почему планировщик игнорирует индекс

Самая частая причина боли на практике: индекс есть, а запрос всё равно делает Seq Scan. Планировщик PostgreSQL — стоимостной (cost-based), он не следует правилам, а считает предполагаемую стоимость каждого плана и выбирает дешевейший.

• Таблица маленькая. Если в таблице пара тысяч строк, Seq Scan дешевле, чем случайные обращения по индексу — диск и так прочитает всё за один проход.
• Низкая селективность. Если условие отбирает 30-40% строк, читать через индекс дороже, чем просто пройти таблицу целиком, потому что каждая строка индекса даёт отдельный случайный I/O.
• Устаревшая статистика. Планировщик оценивает селективность по статистике из pg_stats, которую обновляет автовакуум. Если статистика устарела после массовой загрузки данных, оценка неверна — нужен ANALYZE.
• Несовпадение типов или функция над столбцом. WHERE lower(email) = 'x' не использует обычный индекс по email — нужен индекс по выражению lower(email).
• LIKE с ведущим wildcard. LIKE '%text%' не может использовать обычный B-tree, потому что диапазон неизвестен — тут нужен GIN с триграммами (pg_trgm).
• OR вместо составного условия иногда мешает планировщику выбрать эффективный план по каждому индексу отдельно.

Как проверить, что реально произошло

Единственный надёжный способ — смотреть в EXPLAIN (ANALYZE, BUFFERS), а не гадать. Он показывает выбранный план, реальное количество строк, время выполнения и число прочитанных буферов.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'PAID';

Если видите Seq Scan там, где ожидали Index Scan — проверьте селективность условия, актуальность статистики и типы данных в условии сравнения.

Составные индексы и порядок столбцов

В составном индексе (a, b) порядок столбцов критичен: индекс эффективен для условий по a и по a, b вместе, но бесполезен для запроса только по b — B-tree упорядочен сначала по a, внутри — по b, поэтому без фиксации a нет смысла лезть в дерево.

Ещё разборы