Индексы и планы выполнения: B-tree, hash, GIN, покрывающие индексы

Индекс — это единственный инструмент в реляционной СУБД, который меняет асимптотику. Всё остальное — буферы, параллелизм, более быстрый диск, более мощный процессор — уменьшает константу: было 40 секунд, стало 8. Индекс превращает O(N) в O(log N): было 40 секунд на десяти миллионах строк, стало 0,3 миллисекунды, и на ста миллионах строк будет 0,35 миллисекунды. Именно поэтому разговор о производительности базы почти всегда сводится к разговору об индексах и о том, догадался ли планировщик их использовать.

Обратная сторона столь же существенна и почти всегда недооценивается. Индекс — это вторая копия части данных, которую надо синхронно поддерживать при каждой записи, которая занимает место, которая попадает в бэкапы, которая распухает и которую надо перестраивать. Таблица с восемью индексами делает запись примерно в четыре-шесть раз медленнее той же таблицы с одним. Правильный вопрос не «какие индексы добавить», а «какой минимальный набор индексов покрывает реальные запросы».

Эта статья — про обе стороны. Мы разберём, как индексы устроены физически, почему B-tree выиграл у всех остальных структур для дисковых баз, что умеют GIN, GiST и BRIN, как планировщик считает стоимость и выбирает путь доступа, и как читать EXPLAIN (ANALYZE, BUFFERS) так, чтобы ответ был «вот здесь оценка разошлась с реальностью в 300 раз», а не «что-то тормозит». Основные примеры — на PostgreSQL, потому что его планировщик лучше всего документирован и наблюдаем; отличия MySQL, Oracle, MS SQL и колоночных движков разбираются отдельно. Внутреннее устройство PostgreSQL мы уже трогали в статье PostgreSQL: возможности, индексы, MVCC — здесь мы идём глубже именно в индексы и планы.

Физика: почему считаем страницы, а не строки

Прежде чем говорить о структурах данных, нужно принять одну вещь: база данных не читает строки. Она читает страницы (в PostgreSQL — 8 КБ, в InnoDB — 16 КБ, в Oracle — настраиваемый блок). Даже если вам нужна одна строка на 60 байт, с диска придёт вся страница целиком. Вся стоимостная модель планировщика построена на подсчёте страниц, а не строк.

Второе: страницы читаются двумя принципиально разными способами.

На HDD разрыв между последовательным и случайным чтением был стократным — отсюда исторические значения seq_page_cost = 1.0 и random_page_cost = 4.0 в PostgreSQL. На NVMe разрыв — от полутора до четырёх раз, и оставленный по умолчанию random_page_cost = 4 систематически отпугивает планировщик от индексов. Это одна из двух-трёх настроек, которые надо поменять на любой современной установке:

-- Для NVMe/SSD: случайное чтение почти не дороже последовательного
ALTER SYSTEM SET random_page_cost = 1.1;
-- Сколько данных реально доступно в кэше ОС + shared_buffers.
-- Не выделяет память, это подсказка планировщику: обычно 50–75% RAM.
ALTER SYSTEM SET effective_cache_size = '48GB';
SELECT pg_reload_conf();

Третье: почти всё, что вы делаете, — это попытка уменьшить число прочитанных страниц. Индекс полезен ровно тогда, когда страницы_индекса + страницы_кучи < страницы_всей_таблицы. Когда запрос возвращает половину таблицы, это неравенство ложно, и полный скан честно быстрее.

Рабочая схема для примеров

Дальше всё будет на одной схеме — интернет-магазин, где заказы и события копятся во времени.

CREATE TABLE orders (
    id           bigserial PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers(id),
    status       text   NOT NULL,          -- new/paid/shipped/cancelled/refunded
    total_amount numeric(12,2) NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    attrs        jsonb NOT NULL DEFAULT '{}'
);
-- 50 млн строк, ~120 байт на строку → ~7,5 ГБ кучи, ~950 000 страниц

Полный скан такой таблицы на NVMe — примерно 3–6 секунд в один поток. Всё, что мы будем делать дальше, — это попытки не читать 950 000 страниц.

B-tree: почему он выиграл

Формально в PostgreSQL, InnoDB, Oracle и SQL Server используется B+-дерево: ключи и указатели — во внутренних узлах, а сами данные (пары «ключ → указатель на строку») — только в листьях, и листья связаны в двусвязный список. Три свойства делают его почти безальтернативным для дисковых баз.

Fanout. Узел = страница. В 8 КБ помещается порядка 300–400 записей bigint-ключа с указателем. Значит, высота дерева h ≈ log_f(N): для ста миллионов строк h ≈ 3. Три-четыре чтения вместо миллиона. Причём корень и первый уровень практически всегда сидят в буферном кэше — реально с диска приходит одна-две страницы.

Сбалансированность. Все листья на одной глубине, дерево балансируется при вставках расщеплением узлов. Худший случай равен среднему — это критично для предсказуемости latency, в отличие от хеш-таблиц с их коллизиями и рехешированием.

Упорядоченность листьев. Связный список листьев означает, что диапазонный запрос — это спуск к началу диапазона плюс последовательное чтение. Отсюда же берётся бесплатный ORDER BY: если запрос сортирует по тем же колонкам в том же порядке, что и индекс, сортировки не нужно вовсе.

Что B-tree умеет обслуживать:

Последние две строки — самая частая причина «индекс есть, а не используется» в реальных инцидентах.

Селективность: когда индекс вреден

Индексный доступ дешёвый на строку, но дорогой на строку дважды: сначала читаем страницу индекса, потом прыгаем в кучу за самой строкой. Если строк много, эти прыжки складываются в тысячи случайных чтений, и последовательный скан выигрывает.

Точка перелома зависит от корреляции физического порядка строк с порядком индекса. Грубые ориентиры для PostgreSQL на SSD:

Отсюда практическое следствие: индекс по колонке с тремя значениями почти всегда бесполезен. Индекс по orders.status, где 92% строк — shipped, не поможет ни одному запросу про shipped, но поможет запросу про status = 'refunded' (0,3% строк). Правильный ответ здесь — частичный индекс, о нём ниже.

Проверить реальную корреляцию можно так:

SELECT attname, n_distinct, correlation, null_frac,
       array_length(most_common_vals, 1) AS mcv_count
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

correlation близко к 1 или −1 — физический порядок совпадает с логическим (типично для created_at в append-only таблице), индексный скан дёшев и хорошо подходит BRIN. Около нуля — данные разбросаны, каждая строка в своей странице.

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

Составной индекс (a, b, c) — это дерево, отсортированное лексикографически по кортежу. Из этого механически следует правило самого левого префикса: индекс работает для (a), (a, b), (a, b, c), но не для (b) или (b, c).

Более тонкое и более важное правило — сначала равенства, потом диапазон. Как только в списке колонок встретился предикат-диапазон, все колонки правее него перестают сужать поиск и работают только как фильтр внутри уже прочитанного диапазона.

-- Запрос: заказы клиента за период, в определённых статусах
SELECT * FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01'
  AND status = 'paid';

-- ПЛОХО: диапазон в середине
CREATE INDEX ix_bad  ON orders (customer_id, created_at, status);
-- ХОРОШО: оба равенства слева, диапазон последним
CREATE INDEX ix_good ON orders (customer_id, status, created_at);

С ix_bad движок спускается к (42, '2026-01-01') и сканирует все заказы клиента с этой даты, отбрасывая не-paid уже после чтения. С ix_good он спускается к (42, 'paid', '2026-01-01') и читает ровно нужное. На клиенте с 50 000 заказов разница — примерно двадцать раз.

В EXPLAIN это видно напрямую: строка Index Cond — то, что сузило спуск; строка Filter — то, что отбросили уже после чтения. Всё, что оказалось в Filter рядом с большим Rows Removed by Filter, — кандидат на перенос в индекс.

Ещё два практических соображения:

• Направление сортировки имеет значение только для многоколоночных ORDER BY с разными направлениями: ORDER BY a ASC, b DESC требует индекса (a ASC, b DESC) — обычный индекс сканируется в обе стороны, но не «наполовину назад».
• Индекс (a, b) делает индекс (a) избыточным. Лишние индексы стоят места и записи — регулярно ищите дубликаты по префиксу.

Пути доступа: Index Scan, Bitmap, Index Only Scan

Наличие индекса ещё не определяет, как он будет использован. В PostgreSQL есть три существенно разных пути.

Index Scan — прочитали запись индекса, сразу прыгнули в кучу за строкой, повторили. Порядок индекса сохраняется, ORDER BY бесплатен, LIMIT останавливает работу немедленно. Плата — случайный доступ и возможность прочитать одну и ту же страницу кучи много раз.

Bitmap Index Scan + Bitmap Heap Scan — сначала собираем в память битовую карту нужных страниц, затем читаем кучу одним проходом по возрастанию номера блока. Каждая страница читается ровно один раз, доступ близок к последовательному. Плата — теряется порядок (значит, ORDER BY потребует отдельной сортировки) и LIMIT не спасает: карта строится целиком. Именно битовые карты позволяют комбинировать несколько индексов через BitmapAnd / BitmapOr — это ответ на вопрос «почему СУБД использует только один индекс на таблицу»: в PostgreSQL это неправда.

Важный нюанс: если карта не влезает в work_mem, она деградирует до lossy — вместо конкретных строк запоминаются только номера страниц, и все строки этих страниц перепроверяются заново. В плане это видно как Heap Blocks: exact=1200 lossy=48000 плюс большое Rows Removed by Index Recheck. Лечится увеличением work_mem.

Index Only Scan — самый дешёвый путь: все нужные колонки есть в самом индексе, и в кучу ходить не надо вовсе. Это и есть покрывающий индекс.

-- Запрос читает только три колонки
SELECT customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01';

-- Вариант 1: все колонки в ключе — работает, но индекс шире и сортировка по amount не нужна
CREATE INDEX ix_cov1 ON orders (customer_id, created_at, total_amount);

-- Вариант 2 (PostgreSQL 11+): INCLUDE — колонка хранится только в листьях,
-- не участвует в поиске и не раздувает внутренние узлы
CREATE INDEX ix_cov2 ON orders (customer_id, created_at) INCLUDE (total_amount);

INCLUDE — правильный выбор, когда колонка нужна на выходе, но не для поиска и не для сортировки. Дополнительный бонус: INCLUDE позволяет сделать покрывающим уникальный индекс, не расширяя условие уникальности:

CREATE UNIQUE INDEX ix_email ON customers (email) INCLUDE (country, created_at);

Ловушка Index Only Scan в PostgreSQL: индекс не хранит информацию о видимости версий строк (MVCC-снимок живёт в куче). Чтобы не ходить в кучу, движок сверяется с картой видимости — битовой картой страниц, где все версии видны всем. Карту обновляет VACUUM. Если таблица активно пишется, а автовакуум отстаёт, Heap Fetches в плане растёт, и «покрывающий» индекс тихо вырождается в обычный. Это одна из немногих оптимизаций, которая зависит от настроек обслуживания:

-- Для больших горячих таблиц вакуум должен срабатывать по абсолютному порогу,
-- а не по 20% от 50 миллионов строк
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor  = 0.01,
    autovacuum_vacuum_threshold     = 10000,
    autovacuum_analyze_scale_factor = 0.005
);

В InnoDB (MySQL) устройство иное: таблица — это сам первичный индекс (кластеризованный), а вторичный индекс хранит не физический адрес, а значение первичного ключа. Поэтому «покрывающий индекс» в MySQL получается автоматически, если запросу нужны только колонки индекса плюс PK, — в EXPLAIN это Extra: Using index. Подробности различий — в статье про MySQL и MariaDB.

Таксономия индексов PostgreSQL

B-tree решает задачу «упорядоченные скалярные значения». Огромный класс задач — поиск по элементам внутри значения, по пересечению диапазонов, по геометрии, по векторам — B-tree не решает вообще. Отсюда шесть встроенных методов доступа.

Сравнение по существу:

GIN: индекс по содержимому значения

GIN (Generalized Inverted Index) — это инвертированный индекс: для каждого элемента внутри значения хранится отсортированный список идентификаторов строк (posting list), которые его содержат. Ровно так устроены поисковые движки вроде Lucene — только внутри реляционной базы.

-- Полнотекстовый поиск
CREATE INDEX ix_prod_fts ON products
    USING gin (to_tsvector('russian', title || ' ' || coalesce(description, '')));

SELECT id, title FROM products
WHERE to_tsvector('russian', title || ' ' || coalesce(description,'')) @@ plainto_tsquery('russian', 'беспроводные наушники');

-- jsonb: путь + значение
CREATE INDEX ix_orders_attrs ON orders USING gin (attrs);              -- операторы @> ? ?& ?|
CREATE INDEX ix_orders_attrs_path ON orders USING gin (attrs jsonb_path_ops); -- только @>, вдвое меньше и быстрее

SELECT * FROM orders WHERE attrs @> '{"channel": "mobile", "promo": true}';

-- Подстрочный поиск через триграммы — то, чего B-tree не умеет принципиально
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_prod_title_trgm ON products USING gin (title gin_trgm_ops);
SELECT * FROM products WHERE title ILIKE '%наушник%';   -- индекс работает!

-- Массивы
CREATE INDEX ix_prod_tags ON products USING gin (tags);
SELECT * FROM products WHERE tags @> ARRAY['sale','new'];

Главный подводный камень GIN — стоимость записи. Одна вставка документа с сотней токенов означает обновление сотни posting-списков. Поэтому в GIN есть pending list: новые записи складываются в неупорядоченный буфер и переносятся в основную структуру пачкой при VACUUM или при переполнении буфера.

-- fastupdate=on (по умолчанию): запись быстрая, но чтение вынуждено сканировать pending list
-- Симптом: время поиска «плавает» от 2 мс до 400 мс без видимой причины
ALTER INDEX ix_prod_fts SET (fastupdate = off);
-- Компромисс: оставить fastupdate, но ограничить буфер
ALTER INDEX ix_prod_fts SET (gin_pending_list_limit = '4MB');

Второй камень — GIN не хранит позиции и не умеет ORDER BY. Ранжирование ts_rank считается уже после выборки: если условию удовлетворяет миллион документов, будет прочитан миллион, отранжирован и отброшен. Для «топ-10 релевантных» из большого корпуса это ломается, и тогда либо RUM-индекс (расширение, хранящее позиции и умеющее отдавать результат в порядке ранга), либо выделенный поисковый движок — см. Временные ряды и поиск.

GiST, SP-GiST, BRIN

GiST — это каркас, а не структура: сбалансированное дерево, в узлах которого лежат предикаты вида «всё под этим узлом лежит внутри такой-то области». Он обслуживает то, что не сводится к линейному порядку: пересечение диапазонов, пространственные отношения PostGIS, поиск ближайших соседей.

-- Ни один интервал бронирования не пересекается с другим для той же комнаты.
-- Это ограничение целостности, реализованное индексом — B-tree такого не умеет.
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- kNN: ORDER BY по расстоянию, обслуживаемый индексом (без сортировки всей таблицы)
CREATE INDEX ix_shops_geo ON shops USING gist (location);
SELECT id, name FROM shops ORDER BY location <-> ST_Point(37.62, 55.75) LIMIT 10;

Тот же оператор <-> лежит в основе векторного поиска через pgvector — об этом в статье про векторные базы.

BRIN — самый недооценённый индекс. Он не индексирует строки: он хранит минимум и максимум по каждому диапазону из 128 страниц (pages_per_range). Запрос отбрасывает целые зоны, в которых искомого значения быть не может, и сканирует остаток.

CREATE INDEX ix_events_brin ON order_events USING brin (occurred_at) WITH (pages_per_range = 64);

Цифры для таблицы order_events на 500 млн строк (~60 ГБ):

BRIN в семь тысяч раз меньше и всего в три с половиной раза медленнее — при условии, что данные физически упорядочены по времени. Если строки перемешаны (correlation около 0), BRIN бесполезен: каждая зона содержит весь диапазон значений, и отбросить нельзя ничего. Проверяйте correlation в pg_stats до того, как поставите BRIN.

Hash: почему он почти не нужен

Хеш-индекс поддерживает только =, не поддерживает уникальность, сортировку, многоколоночность и диапазоны. До PostgreSQL 10 он ещё и не писался в WAL (то есть не переживал крах и не реплицировался). Сегодня он корректен и иногда компактнее B-tree на очень длинных ключах (URL, длинные текстовые идентификаторы), но выигрыш редко оправдывает потерю универсальности. Практическое правило: если не измерили и не увидели разницу — берите B-tree.

Частичные индексы и индексы по выражению

Две техники, дающие непропорционально большой эффект.

Частичный индекс индексирует только подмножество строк. Он меньше, лучше живёт в кэше и дешевле в обслуживании (строки вне условия его вообще не трогают).

-- Очередь: активных заказов 0,2% от таблицы, но именно к ним ходят каждую секунду
CREATE INDEX ix_orders_active ON orders (created_at)
    WHERE status IN ('new', 'paid');
-- 50 млн строк → индекс на 100 тыс. строк: ~2 МБ вместо ~1,1 ГБ

-- Уникальность только для живых записей (частая задача с мягким удалением)
CREATE UNIQUE INDEX ux_customers_email_alive ON customers (email)
    WHERE deleted_at IS NULL;

-- Индексируем только «интересные» значения, не тратя место на 92% shipped
CREATE INDEX ix_orders_problem ON orders (customer_id, created_at)
    WHERE status IN ('refunded', 'cancelled');

Условие в WHERE запроса должно быть логически выводимо планировщиком из условия индекса. WHERE status = 'new' подойдёт под индекс с status IN ('new','paid'); WHERE status = ANY($1) с параметром-массивом — нет, потому что на этапе планирования значение неизвестно. Это регулярный источник сюрпризов при переходе с литералов на параметры.

Индекс по выражению спасает там, где над колонкой стоит функция:

CREATE INDEX ix_customers_email_lower ON customers (lower(email));
SELECT * FROM customers WHERE lower(email) = lower($1);   -- выражение должно совпадать точь-в-точь

CREATE INDEX ix_orders_day ON orders (date_trunc('day', created_at));
CREATE INDEX ix_orders_promo ON orders ((attrs ->> 'promo_code'));

Важно: функция обязана быть IMMUTABLE. date_trunc('day', created_at) для timestamptz зависит от часового пояса сессии и потому STABLE — такой индекс создать не дадут; надо либо приводить к конкретной зоне (date_trunc('day', created_at AT TIME ZONE 'UTC')), либо хранить timestamp.

Побочный эффект, который многие упускают: индекс по выражению заставляет ANALYZE собирать отдельную статистику по этому выражению. Даже если индекс не используется для доступа, оценки селективности для этого выражения становятся точными — иногда одного этого достаточно, чтобы починить план.

Как планировщик принимает решение

PostgreSQL использует стоимостной планировщик: он перебирает варианты планов, оценивает каждый в абстрактных единицах стоимости и выбирает минимальный. Оценка строится из констант и из статистики.

Стоимость последовательного скана считается почти буквально:

cost(Seq Scan) = relpages × seq_page_cost + reltuples × cpu_tuple_cost
               + reltuples × cpu_operator_cost × (число предикатов)

Для нашей таблицы: 950 000 × 1.0 + 50 000 000 × 0.01 + ... ≈ 1 020 000. Стоимость индексного скана — высота дерева плюс страницы листьев плюс обращения в кучу, где число обращений умножается на random_page_cost и корректируется по корреляции. Абсолютные значения бессмысленны, значение имеет только сравнение между планами.

Статистика собирается ANALYZE по случайной выборке (default_statistics_target = 100 означает выборку в 30 000 строк) и содержит:

• n_distinct — число уникальных значений (или отрицательная доля от общего числа строк);
• most_common_vals / most_common_freqs — топ частых значений и их частоты;
• histogram_bounds — границы равнонаполненных корзин для оценки диапазонов;
• correlation — корреляция логического и физического порядка;
• null_frac — доля NULL.

Где планировщик систематически ошибается

Главное упрощение стоимостной модели — предположение о независимости колонок. Селективность A AND B считается как произведение селективностей. В реальных данных колонки коррелируют, и произведение занижает оценку на порядки.

-- Города и страны жёстко связаны: 'Москва' встречается только с 'RU'
SELECT * FROM customers WHERE city = 'Москва' AND country = 'RU';
-- Планировщик: 0,01 × 0,3 = 0,003 → ожидает 3 000 строк
-- Реальность: 300 000 строк. Ошибка в 100 раз → выбран nested loop вместо hash join

С PostgreSQL 10 это лечится расширенной статистикой:

CREATE STATISTICS st_cust_geo (dependencies, ndistinct, mcv)
    ON city, country FROM customers;
ANALYZE customers;

Три вида: dependencies — функциональные зависимости, ndistinct — число уникальных комбинаций (критично для GROUP BY по нескольким колонкам), mcv — частые комбинации значений.

Другие типичные слепые зоны:

Чтение EXPLAIN как ремесло

Ключевая команда:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT o.id, o.total_amount, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'paid'
ORDER BY o.total_amount DESC
LIMIT 100;

ANALYZE реально выполняет запрос (для INSERT/UPDATE оборачивайте в транзакцию с ROLLBACK), BUFFERS показывает попадания в кэш — без него анализ наполовину слепой. Плохой план:

Limit  (cost=1245830.11..1245830.36 rows=100 width=48) (actual time=8214.339..8214.361 rows=100 loops=1)
  Buffers: shared hit=12043 read=938211
  ->  Sort  (cost=1245830.11..1246947.34 rows=446892 width=48) (actual time=8214.337..8214.348 rows=100 loops=1)
        Sort Key: o.total_amount DESC
        Sort Method: top-N heapsort  Memory: 41kB
        ->  Hash Join  (cost=68421.00..1228766.02 rows=446892 width=48) (actual time=1204.11..8102.55 rows=441038 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=12043 read=938211
              ->  Seq Scan on orders o  (cost=0.00..1145992.00 rows=446892 width=26)
                                        (actual time=0.42..7401.19 rows=441038 loops=1)
                    Filter: ((created_at >= (now() - '7 days'::interval)) AND (status = 'paid'::text))
                    Rows Removed by Filter: 49558962
              ->  Hash  (cost=41221.00..41221.00 rows=2000000 width=30) ...
Planning Time: 0.412 ms
Execution Time: 8214.502 ms

Как это читать по шагам.

1. Снизу вверх, изнутри наружу. Нижние узлы выполняются первыми.
2. Rows Removed by Filter: 49 558 962 — прочитали 50 миллионов строк, чтобы отдать 441 тысячу. Это диагноз: нет подходящего индекса.
3. read=938211 — почти миллион страниц пришёл с диска мимо кэша. Умножаем на 8 КБ: 7,3 ГБ ввода-вывода ради ста строк на выходе.
4. cost против actual. Здесь оценка строк точная (446 892 против 441 038) — планировщик не ошибся, у него просто не было выбора.
5. loops. В узлах внутри nested loop все actual значения даны на одну итерацию; реальная работа = actual time × loops. Самая частая ошибка чтения планов.

Исправление:

CREATE INDEX CONCURRENTLY ix_orders_paid_recent
    ON orders (created_at DESC) INCLUDE (customer_id, total_amount)
    WHERE status = 'paid';

Limit  (actual time=18.402..18.511 rows=100 loops=1)
  Buffers: shared hit=3401 read=112
  ->  Sort  (actual time=18.400..18.404 rows=100 loops=1)
        Sort Method: top-N heapsort  Memory: 41kB
        ->  Nested Loop  (actual time=0.071..14.882 rows=441038 loops=1)
              ->  Index Only Scan using ix_orders_paid_recent on orders o (actual time=0.041..3.902 rows=441038 loops=1)
                    Index Cond: (created_at >= (now() - '7 days'::interval))
                    Heap Fetches: 0
              ->  Index Scan using customers_pkey on customers c (actual time=0.002..0.002 rows=1 loops=441038)
Execution Time: 18.63 ms

С 8214 мс до 18,6 мс — в 440 раз. Heap Fetches: 0 подтверждает, что покрывающий индекс действительно работает; read=112 вместо 938 211 — что диск больше не при делах.

Чек-лист признаков беды в плане:

Полезные инструменты: explain.dalibo.com и explain.depesz.com визуализируют план и подсвечивают узлы, где оценка разошлась с реальностью. Плюс auto_explain — чтобы ловить планы медленных запросов в проде, а не воспроизводить их вручную:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.05   -- 5% запросов: ANALYZE не бесплатен

Индексы и соединения

Индексы влияют не только на доступ к таблице, но и на выбор алгоритма соединения.

Классический продовый инцидент: планировщик недооценил внешнюю сторону в сто раз, выбрал nested loop, и запрос, который должен был идти 50 мс, идёт 40 секунд, потому что внутренняя часть выполнилась не 200 раз, а 200 000. Симптом в плане — огромный loops при расхождении rows с actual rows на уровень ниже. Лечение — не SET enable_nestloop = off (это затыкание симптома), а починка оценки: CREATE STATISTICS, поднятие default_statistics_target на конкретной колонке, переписывание запроса.

-- Точечно повысить детальность статистики по «трудной» колонке
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

Жизненный цикл индекса в проде

Ключевые операции:

-- Создание без блокировки записи: два прохода по таблице, дольше, но приложение живо.
-- Нельзя внутри транзакции. При падении оставляет INVALID-индекс — его надо снести.
CREATE INDEX CONCURRENTLY ix_orders_active ON orders (created_at) WHERE status = 'new';

-- Найти неудавшиеся индексы
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

-- Перестроить распухший индекс без долгой блокировки (PG 12+)
REINDEX INDEX CONCURRENTLY ix_orders_active;

Диагностические запросы, которые стоит держать под рукой:

-- 1. Неиспользуемые индексы (кандидаты на удаление)
SELECT s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan < 50 AND NOT i.indisunique
  AND pg_relation_size(s.indexrelid) > 10 * 1024 * 1024
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- 2. Дублирующиеся индексы (один — префикс другого)
SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS dupes
FROM pg_index GROUP BY indrelid, indkey HAVING count(*) > 1;

-- 3. Топ по времени: где вообще искать проблему
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       shared_blks_hit + shared_blks_read AS blocks,
       left(query, 100) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- 4. Доля индексного доступа по таблицам
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 1) AS idx_pct
FROM pg_stat_user_tables WHERE seq_scan + idx_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20;

Сбрасывайте pg_stat_user_indexes перед наблюдением (pg_stat_reset()) и ждите полный бизнес-цикл — недельные и месячные отчёты используют индексы, которые в понедельник выглядят мёртвыми. И не забывайте про реплики: индекс может быть не нужен на мастере, но обслуживать всю аналитику на standby, где счётчики свои.

Стоимость: чем вы платите за индексы

Измерение, которое стоит провести самому (таблица на 10 млн строк, NVMe, synchronous_commit = off, вставка пачками по 1000):

Последняя строка объясняет, почему GIN по jsonb на горячей OLTP-таблице — решение, которое надо принимать осознанно. Порядок величин у вас будет свой, но соотношения устойчивы.

Типичные проблемы в проде

1. Неявный каст убивает индекс. Колонка bigint, параметр приходит строкой — индекс не применяется. Проверяйте типы в драйвере, а не гадайте.

2. LIKE '%текст%' не обслуживается B-tree никогда. Ответ — pg_trgm + GIN, либо полнотекстовый поиск, либо внешний движок.

3. Abort-early план с ORDER BY ... LIMIT. Планировщик видит ORDER BY created_at DESC LIMIT 10 и решает: пойду по индексу created_at с конца и остановлюсь, как только наберу 10 подходящих. Если фильтр редкий (customer_id = 42 для клиента с тремя старыми заказами), придётся просканировать полтаблицы. Оценка — 10 строк, реальность — 20 миллионов. Лечение — составной индекс (customer_id, created_at DESC), чтобы условие и порядок обслуживались одним индексом.

4. Пагинация через OFFSET. OFFSET 500000 LIMIT 20 читает и отбрасывает полмиллиона строк. Переходите на keyset-пагинацию:

-- Плохо: линейно деградирует
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC OFFSET 500000 LIMIT 20;
-- Хорошо: постоянное время при индексе (customer_id, created_at DESC, id DESC)
SELECT * FROM orders
WHERE customer_id = 42 AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;

5. Слишком много индексов. «На всякий случай» превращается в тридцать индексов на таблице, из которых используются шесть. Записи медленные, вакуум долгий, бэкап тяжёлый. Раз в квартал — ревизия по idx_scan.

6. Распухание индексов. При частых UPDATE страницы B-tree заполняются частично. Индекс может вырасти вдвое-втрое от полезного размера. Симптом — idx_scan не растёт, а размер растёт. Лечение — REINDEX CONCURRENTLY по расписанию для горячих индексов.

7. Generic plan для подготовленных выражений. После пяти выполнений PostgreSQL может переключиться на план, не зависящий от значений параметров. Для колонки с сильно перекошенным распределением это катастрофа: план, оптимальный для редкого значения, применяется к частому.

SET plan_cache_mode = 'force_custom_plan';   -- на сессию или для конкретной нагрузки

8. Индекс по колонке с низкой кардинальностью. status из пяти значений — почти всегда бесполезен как самостоятельный индекс; полезен как первая колонка составного или как условие частичного.

9. Забытый ANALYZE после массовой загрузки. Загрузили 100 млн строк, reltuples показывает 1000 — планировщик строит планы для игрушечной таблицы. Всегда ANALYZE после COPY.

10. Индекс на партиционированной таблице. CREATE INDEX на родителе создаёт индексы на всех партициях (в PG 11+), но CONCURRENTLY на родителе не поддерживается — придётся строить по партициям и потом ATTACH PARTITION к родительскому индексу.

Как выбирать: короткий алгоритм

Порядок действий, а не структура данных, — вот что чаще всего решает: сначала измерьте (pg_stat_statements), потом посмотрите план (EXPLAIN ANALYZE BUFFERS), потом добавьте индекс, потом снова измерьте. Индексы, добавленные «по интуиции», в среднем ухудшают систему: место и запись они едят гарантированно, а пользу приносят вероятностно.

Как это выглядит в других СУБД

Два принципиальных отличия стоит запомнить.

MySQL/InnoDB: таблица — это первичный индекс, поэтому длинный или случайный PK (например, UUIDv4) означает случайные вставки в середину дерева, расщепления страниц и распухание. UUIDv7 или bigint вместо UUIDv4 — одно из самых дешёвых улучшений производительности записи. И вторичный индекс всегда стоит два спуска: по индексу до PK, потом по кластеру до строки.

ClickHouse: там нет индексов в привычном смысле. PRIMARY KEY задаёт порядок сортировки данных на диске и разреженный индекс (одна запись на 8192 строки), а «skip-индексы» (minmax, set, bloom_filter) работают как BRIN — отбрасывают гранулы. Запрос вне порядка сортировки читает всё. Подробнее — в статье про ClickHouse и колоночные БД.

Oracle и SQL Server дают то, чего в PostgreSQL нет: механизмы фиксации плана (SQL Plan Baselines, Query Store). Это признание того, что стабильность плана в корпоративной среде иногда важнее его оптимальности — см. MS SQL и Oracle.

Чеклист по индексам

• pg_stat_statements включён, топ по total_exec_time регулярно просматривается
• auto_explain с сэмплированием ловит медленные планы в проде
• random_page_cost = 1.1 и корректный effective_cache_size на SSD
• Составные индексы: равенства слева, диапазон последним
• Проверено, что предикаты попадают в Index Cond, а не в Filter
• Для узких выборок — покрывающие индексы с INCLUDE, Heap Fetches близко к нулю
• Для селективных подмножеств — частичные индексы вместо полных
• Для коррелированных колонок — CREATE STATISTICS
• BRIN рассмотрен для append-only таблиц с высокой correlation
• GIN с jsonb_path_ops, если нужен только @>
• Все индексы в проде создаются через CREATE INDEX CONCURRENTLY с lock_timeout
• Ежеквартальная ревизия неиспользуемых и дублирующих индексов
• ANALYZE после любой массовой загрузки и после мажорного апгрейда
• Пагинация — keyset, а не OFFSET
• REINDEX CONCURRENTLY по расписанию для горячих распухающих индексов

Мини-итог

Индекс — это структура, которая позволяет прочитать несколько страниц вместо всех. B-tree покрывает подавляющее большинство задач благодаря огромному fanout, сбалансированности и упорядоченным листьям; GIN решает задачу «искать внутри значения»; GiST — «искать по области»; BRIN — «отбросить зоны», почти не занимая места. Составной индекс подчиняется правилу «равенства слева, диапазон последним», покрывающий индекс убирает обращения в кучу, частичный убирает лишние строки.

Но индексы — только половина. Вторая половина — планировщик, который выбирает между ними на основе статистики и стоимостной модели, а его главное упрощение (независимость колонок) регулярно даёт ошибки в оценках на порядки. Поэтому единственный надёжный рабочий цикл — измерить, прочитать EXPLAIN (ANALYZE, BUFFERS), найти конкретное место, где оценка разошлась с реальностью или где Rows Removed by Filter исчисляется миллионами, и уже туда прицельно поставить индекс. И потом измерить снова.

Источники

• PostgreSQL Documentation: Indexes и Performance Tips — первоисточник
• PostgreSQL: Using EXPLAIN — разбор формата планов
• Use The Index, Luke — Маркус Винанд, лучшая книга про индексы в SQL; бесплатна онлайн
• Markus Winand, «SQL Performance Explained» — печатная версия того же материала
• Егор Рогов, «PostgreSQL изнутри» — бесплатно от Postgres Professional, главы про индексы и планировщик
• Хиронобу Судзуки, «The Internals of PostgreSQL» — главы 3 (планировщик) и 1 (хранение)
• Depesz: explain.depesz.com и Dalibo: explain.dalibo.com — визуализаторы планов
• pganalyze: How Postgres chooses which index to use — разбор стоимостной модели
• MySQL: Optimization and Indexes — для сравнения подходов
• Hellerstein, Stonebraker, Hamilton, «Architecture of a Database System» — как устроены методы доступа и оптимизаторы в целом

Что дальше

Транзакции, уровни изоляции, блокировки и аномалии — вторая половина разговора о корректности и производительности. Индексы определяют, сколько страниц вы прочитаете; изоляция определяет, что вы там увидите и кого при этом заблокируете. И да, индексы участвуют в блокировках напрямую: предикатные блокировки в Serializable ставятся именно на диапазоны индекса.