←Все статьи

Индексы PostgreSQL: B-tree, GIN, BRIN, частичные — и почему EXPLAIN врёт

Базы данных5 мин чтения
  • #postgresql
  • #indexes
  • #b-tree
  • #gin
  • #explain

«Добавил индекс, а запрос не ускорился» — классика, с которой сталкивается каждый, кто работает с PostgreSQL. Индексы — не «кнопка быстро», а набор инструментов под разные задачи, и выбирать их надо по характеру запросов. Разберём, какие индексы бывают, когда какой работает, и почему EXPLAIN ANALYZE надёжнее простого EXPLAIN.

Что вообще делает индекс

Без индекса PostgreSQL ищет строки полным сканированием (sequential scan) — читает всю таблицу от начала до конца. На маленькой таблице это мгновенно, на таблице в сто миллионов строк — катастрофа.

Индекс — это отдельная структура данных, которая позволяет по значению (или его части) быстро найти строки, где это значение встречается. Аналогия: предметный указатель в конце книги. Без него вы листаете все 800 страниц ради одного термина; с ним — открываете указатель, находите страницы, идёте прямо к ним.

Важно: индекс ускоряет точечные запросы (WHERE id = 5), но не ускоряет и даже замедляет всё, что затрагивает большую часть таблицы (потому что надо и в индекс зайти, и в таблицу). Полное сканирование иногда быстрее индекса — планировщик знает это и выбирает seq scan.

Типы индексов в PostgreSQL

B-tree (дефолт)

Сбалансированное дерево. Индекс по умолчанию, создаётся при CREATE INDEX без указания типа и для всех UNIQUE/PRIMARY KEY ограничений. Поддерживает:

  • точное равенство (=);
  • неравенства (<, >, BETWEEN);
  • IS NULL;
  • ORDER BY (может отдать данные уже отсортированными, без отдельного sort);
  • prefix-поиск по строкам (LIKE 'abc%' — только с префиксом).

Покрывает 90% случаев. Если не знаете, какой индекс — берите B-tree.

Hash

Только точное равенство (=). Ни неравенств, ни сортировки. До PostgreSQL 10 не переживал crash (не записывался в WAL), что делало его опасным; сейчас логируется, но применяется редко — B-tree делает то же и больше за сопоставимую цену.

GIN (Generalized Inverted Index)

«Перевёрнутый» индекс: для одного значения хранит список мест, где оно встречается. Заточен под структуры, где один документ содержит много элементов:

  • полнотекстовый поиск (tsvector, to_tsquery);
  • JSONB — индексация ключей и значений JSON-документов;
  • массивы — поиск по элементам массива.

Тяжёлый на запись (каждый insert/update перестраивает списки), быстрый на поиск. Для полнотекстового поиска альтернатив почти нет.

GiST (Generalized Search Tree)

Не конкретный алгоритм, а инфраструктура для специальных типов: геометрия (пересечения, containment), диапазоны (int4range), полнотекстовый поиск (через tsvector с триграммами). Применяется под специфические типы данных.

BRIN (Block Range Index)

Хранит минимальное и максимальное значение для блоков (диапазонов страниц) таблицы. Крошечный по размеру (килобайты вместо гигабайт у B-tree), но приблизительный — указывает на блок, где значение может быть, а дальше обычное сканирование блока.

Идеален для больших таблиц с физически упорядоченными данными: логи по времени, телеметрия, append-only таблицы. Если строки вставляются по возрастанию timestamp — BRIN на колонке created_at даёт почти B-tree-скорость при размере в тысячу раз меньше.

Продвинутые паттерны

Частичные индексы

Индексировать только строки, подходящие под условие WHERE:

CREATE INDEX orders_unfulfilled ON orders(created_at)
WHERE status = 'pending';

Этот индекс покрывает только незавершённые заказы — а это, как правило, малая доля от всех. Меньше места, быстрее запись для остальных строк, и планировщик его использует для запросов WHERE status = 'pending'.

Покрывающие индексы (INCLUDE)

Если запрос можно полностью обслужить из индекса, не заходя в таблицу — это index-only scan, он намного быстрее. INCLUDE добавляет в индекс колонки, которые не участвуют в поиске, но нужны в SELECT:

CREATE INDEX users_email_idx ON users(email) INCLUDE (name, avatar_url);

-- Этот запрос выполнится без обращения к таблице:
SELECT email, name, avatar_url FROM users WHERE email = 'a@b.com';

Функциональные индексы

Если запрос фильтрует по функции от колонки — обычный индекс не поможет:

-- WHERE LOWER(email) = 'a@b.com' — обычный индекс на email не сработает
CREATE INDEX users_email_lower ON users(LOWER(email));

Почему индекс не сработает

Распространённые причины, когда индекс есть, а seq scan всё равно:

  • Функция на колонке. WHERE LOWER(name) = ... — нужен функциональный индекс.
  • Несовместимый тип. WHERE id = '123', где id числовой, а '123' строка — планировщик может отказаться от индекса из-за неявного приведения.
  • OR с разными колонками. WHERE a = 1 OR b = 2 — каждый индекс по отдельности не покрывает всё условие; иногда нужен составной индекс или переписывание через UNION.
  • Устаревшая статистика. Планировщик оценивает число строк по статистике (собирается ANALYZE). Если статистика устарела, planner может решить, что индекс не селективен, и выбрать seq scan. Лечится ручным ANALYZE или автоваакуумом.

Как читать EXPLAIN

EXPLAIN показывает план запроса по оценкам планировщика. EXPLAIN ANALYZE реально выполняет запрос и добавляет фактическое время каждого узла и число строк. Только ANALYZE показывает правду:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

На что смотреть в выводе:

  • Seq Scan на большой таблице — плохо, если ожидалось использование индекса.
  • Index Scan / Index Only Scan — индекс используется. Index Only — лучший вариант.
  • Bitmap Heap Scan — гибрид: сначала по индексу строится битовая карта подходящих строк, потом они читаются пачками. Хорошо для умеренно селективных условий.
  • Rows Removed by Filter большое — фильтр отсекает много строк, возможно, не хватает индекса.
  • разница между estimated и actual rows большая — устаревшая статистика.

Простой EXPLAIN может обмануть: planner скажет, что выбрал индекс, а на деле время неудовлетворительное. Только ANALYZE показывает, где реально время.

Когда индексы вредят

Каждый индекс — это отдельная структура, которую надо поддерживать при каждой вставке/обновлении/удалении. Десять индексов на таблицу — десять обновлений на один INSERT. На write-heavy таблицах это замедляет запись и раздувает хранилище.

Правило: индексируете под конкретные запросы, которые тормозят, а не «на всякий случай». Удаляйте неиспользуемые индексы — PostgreSQL умеет показывать статистику использования (pg_stat_user_indexes, колонка idx_scan).

Короткое summary

PostgreSQL предлагает несколько типов индексов под разные задачи: B-tree — для большинства случаев (равенство, неравенства, сортировка), GIN — для полнотекстового поиска/JSONB/массивов, BRIN — для огромных упорядоченных таблиц. Частичные и покрывающие индексы дополнительно экономят место и ускоряют конкретные запросы. Главный инструмент диагностики — EXPLAIN ANALYZE, а не EXPLAIN (первый реально выполняет запрос и показывает фактическое время). Индекс не сработает при функции на колонке, несовместимом типе, плохом OR или устаревшей статистике. И индексы не бесплатны: каждый лишний тормозит запись, поэтому их добавляют под конкретные медленные запросы и удаляют неиспользуемые.

Что почитать