Как выбирать и использовать индексы

Создать индекс легко — труднее создать нужный и убедиться, что планировщик им пользуется. Большинство «индекс не помог» сводится к нескольким причинам: низкая селективность, неправильный порядок колонок в составном индексе, функция или приведение типа поверх колонки, из-за которых индекс просто нельзя применить. Хуже того — иногда индекс применить можно, но планировщик всё равно откажется, потому что при низкой селективности (когда фильтру удовлетворяет большая доля строк) честное полное сканирование выходит дешевле. Эта статья — про то, как выбирать индекс под запрос и как проверять, что он реально работает, на живом стенде PostgreSQL 18 (таблица events, 2 000 000 строк).

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

В статье

• Селективность и кардинальность
• Кроссовер: где seq scan обгоняет индекс
• Врезка: что такое bitmap-index scan на самом деле
• Покрывающие индексы и index-only scan
• Составные индексы: порядок колонок и left-prefix
• Частичные и функциональные индексы
• Когда индекс не используется
• Как проверить: EXPLAIN и статистика

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

Селективность предиката — это доля строк таблицы, которые он отбирает. Предикат id = 12345 на таблице с уникальным id селективен предельно: одна строка из миллионов. Предикат status = 'active', если активных строк половина таблицы, почти не селективен — отбирает каждую вторую строку. Кардинальность — родственное понятие: число различных значений в колонке. У status с пятью возможными значениями кардинальность низкая, у user_id на таблице событий — высокая, близкая к числу пользователей.

Индекс выигрывает ровно тогда, когда предикат селективен: чтение по индексу стоит примерно O(log n) на поиск нужных ключей плюс O(k) на выборку k найденных строк из таблицы — и это дёшево, пока k мало относительно n. Но у каждой найденной по индексу строки нужно ещё сходить в heap (саму таблицу), чтобы прочитать данные, которых нет в индексе, — а heap не отсортирован в порядке индекса, поэтому переходы по нему случайны. Если k приближается к n, число случайных обращений к диску приближается к размеру всей таблицы, и последовательное чтение той же таблицы (Seq Scan) оказывается дешевле, чем k разрозненных попаданий в heap плюс накладные расходы самого индекса. Планировщик PostgreSQL считает эту оценку не на глаз, а по статистике: pg_stats хранит гистограмму распределения значений колонки и число уникальных значений (n_distinct), собранные ANALYZE (обычно через autovacuum). Устаревшая статистика — частая причина, когда план выглядит «неправильным», хотя формально ничего не сломано: планировщик просто считает по старым числам.

Отсюда практическое правило: индекс на колонку с низкой кардинальностью (два-три значения на всю таблицу) почти никогда не окупается — селективность каждого значения слишком низкая, Seq Scan почти всегда дешевле. Но граница не резкая — это не «10% и меньше» по какому-то фиксированному порогу, а зависящая от физики диска и распределения данных точка кроссовера, которую проще один раз увидеть на цифрах, чем запомнить как правило.

Кроссовер: где seq scan обгоняет индекс

На стенде это видно буквально: один и тот же запрос WHERE amount < N (диапазон, с побочным count(payload), форсирующим честное чтение heap — иначе планировщик подставляет только индекс и даже не смотрит на строки), где N подобран так, чтобы отбирать разную долю таблицы — от 0.05% до 90% строк. При низкой доле план — Bitmap Heap Scan (собрать карту подходящих страниц по индексу, затем прочитать их пачкой, отсортировав по физическому порядку). На полпути план переключается на честный Index Scan. А при высокой доле — на Seq Scan, и время исполнения при этом падает, а не растёт:

Числа по всем восьми точкам:

Три вещи, которые здесь важно заметить. Во-первых, до 30% доля растёт вместе со временем плавно — планировщик держится за Bitmap Heap Scan, потому что собрать карту страниц и прочитать их пачкой всё ещё дешевле, чем читать всю таблицу подряд. Во-вторых, точка 50% — это не промежуточная ступенька, а настоящий пик: 603.297 мс дороже, чем соседние точки в обе стороны, потому что здесь планировщик выбрал чистый Index Scan (без сборки bitmap), а обращение к половине строк таблицы по индексу означает примерно миллион случайных походов в heap — самый дорогой из возможных сценариев. В-третьих, при 70% и 90% время не продолжает расти, а падает до 317 и 358 мс — планировщик отказывается от индекса вообще и читает таблицу последовательно, потому что при такой доле совпадений индекс уже не экономит ничего, а только добавляет накладные расходы поверх честного Seq Scan. Кроссовер локализован между 50% (ещё индекс) и 70% (уже seq scan) — точнее не измерялось, но сам факт, что кривая имеет пик, а не монотонно растёт, и есть главный тезис: низкая селективность (большая доля строк проходит фильтр) не означает «нужен более быстрый индекс» — она означает, что индекс вообще не подходящий инструмент для этого запроса, и планировщик, переключаясь на Seq Scan, в этом случае прав.

Врезка: что такое bitmap-index scan на самом деле

В плане выше несколько раз встречается Bitmap Heap Scan — и на первый взгляд можно решить, что это ещё один тип индекса, наравне с B-tree или GIN. Это не так. Как уже отмечалось в первой статье серии, в PostgreSQL нет персистентного bitmap-индекса как отдельной структуры на диске — есть bitmap-стратегия сканирования, которую планировщик строит на лету поверх любого обычного индекса (обычно B-tree, но так же работает поверх hash или GIN).

Устроено это в два шага, которые в плане всегда идут парой: сначала Bitmap Index Scan проходит по существующему индексу и строит в памяти битовую карту страниц таблицы, где потенциально есть подходящие строки — не строк, а именно страниц, что заметно компактнее при большом числе совпадений. Затем Bitmap Heap Scan читает эти страницы, отсортировав обращения по физическому порядку на диске, а не в порядке следования по индексу — это превращает случайное чтение построчно в почти последовательное чтение блоками. Именно поэтому bitmap-стратегия выгодна в средней зоне селективности: строк слишком много для дешёвого чистого Index Scan (который читает heap в порядке индекса, то есть случайно), но ещё недостаточно много, чтобы выигрывал полный Seq Scan. AND и OR нескольких условий по разным индексам планировщик тоже может объединить именно на этом уровне — побитовыми операциями над картами до похода в heap, что дешевле, чем пересечение результатов двух независимых Index Scan.

Отсюда практический вывод: увидев в EXPLAIN пару Bitmap Heap Scan / Bitmap Index Scan on <имя_индекса>, не ищите отдельный «bitmap-индекс» в списке индексов таблицы — там будет обычный B-tree (или другой) индекс, а bitmap — это выбор планировщика, как этим индексом воспользоваться для конкретной селективности запроса.

Покрывающие индексы и index-only scan

Даже быстрый Index Scan делает две вещи: ищет подходящие ключи в индексе и затем идёт в heap за остальными колонками строки. Если все нужные запросу колонки уже есть в самом индексе, второй шаг можно исключить целиком — это и называется покрывающим индексом, а соответствующий план — Index Only Scan.

Разницу правильно мерить на одинаковой проекции — SELECT user_id, amount до и после покрывающего индекса, иначе сравниваются разные запросы. До covering-индекса выборку обслуживает обычный idx_events_user(user_id): это Index Scan, который за колонкой amount всё равно идёт в heap — здесь 28 обращений к буферам (Buffers: shared hit=28):

Index Scan using idx_events_user on events
  (cost=0.43..24.99 rows=21 width=14) (actual time=0.007..0.023 rows=25.00 loops=1)
  Index Cond: (user_id = 12345)
  Buffers: shared hit=28
Execution Time: 0.029 ms

После добавления idx_events_user_cov ON events(user_id) INCLUDE (amount) — где amount кладётся в индекс «довеском», не как часть ключа поиска и без участия в сортировке — та же выборка идёт как Index Only Scan и heap не читает вовсе: Heap Fetches: 0, всего около четырёх обращений к буферам (только страницы индекса):

Index Only Scan using idx_events_user_cov on events
  (cost=0.43..1.90 rows=21 width=14) (actual time=0.043..0.045 rows=25.00 loops=1)
  Index Cond: (user_id = 12345)
  Heap Fetches: 0
  Buffers: shared hit=1 read=3
Execution Time: 0.058 ms

Ключевая тонкость, ради которой смотреть надо на Heap Fetches, а не на секундомер: Index Only Scan пропускает heap только тогда, когда карта видимости (visibility map) помечает нужные страницы как all-visible. Эту карту выставляет VACUUM — поэтому в стенде перед замером стоит VACUUM (ANALYZE) events. Без него, сразу после массовой вставки, тот же план даёт Heap Fetches: N > 0: PostgreSQL вынужден сходить в heap за проверкой видимости каждой строки, и «only» превращается в обычный поход в таблицу. А само wall-time на 25 строках — шум (десятки микросекунд, к тому же свежесозданный covering-индекс сначала читается с диска, из-за чего может выйти даже номинально «медленнее» прогретого Index Scan): настоящий выигрыш покрывающего индекса — это устранённые heap-fetch и буферные обращения (≈4 против 28), и он проявляется на объёме и под нехваткой кэша, а не на секундомере крошечной выборки.

Цена покрывающего индекса — размер и запись: каждая INCLUDE-колонка увеличивает индекс и должна обновляться при каждом UPDATE этой колонки, даже если она не участвует в поиске. Смысл есть там, где один и тот же горячий запрос выполняется очень часто и выбирает малое подмножество колонок — типичный случай для агрегатов и списков в API. Обратная сторона медали — write-amplification от избыточных индексов — разобрана подробнее в статье #4 этой серии.

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

Составной индекс по нескольким колонкам физически хранит строки, отсортированные сначала по первой колонке, затем внутри неё — по второй, и так далее. Отсюда прямое следствие: индексом можно воспользоваться для поиска по префиксу этого порядка (одной первой колонке, или первой и второй вместе), но не для поиска по колонке, которая не идёт первой, — это и называется left-prefix.

На стенде создан индекс idx_user_status ON events(user_id, status), и пять запросов к нему дают ровно предсказанную left-prefix картину:

Первая строка — поиск по одному user_id, первой колонке индекса, — уже пользуется индексом. Вторая, с обеими колонками в предикате, — ещё быстрее (0.017 мс против 0.170 мс), потому что индекс сужает диапазон уже по обоим условиям, а не только по первому. А запрос только по status, второй колонке индекса, без user_id, — это честный Seq Scan за 226.914 мс: PostgreSQL физически не может начать спуск по дереву idx_user_status, не зная значения user_id, потому что строки внутри дерева упорядочены по нему в первую очередь — status без user_id разбросан по всему дереву равномерно, индекс тут бесполезен, ровно как обычный алфавитный указатель бесполезен, если вы знаете только отчество, а не фамилию.

Практическое правило порядка колонок в составном индексе — ESR (Equality, Sort, Range): сначала колонки с равенством в WHERE, затем колонка сортировки из ORDER BY, затем колонка с диапазонным условием. Такой порядок позволяет индексу одновременно сузить диапазон по равенствам и отдать уже отсортированный результат без отдельного шага Sort в плане — этот же принцип, только на примере MongoDB, подробно разбирается в статье #3 серии.

Частичные и функциональные индексы

Частичный (partial) индекс строится не по всей таблице, а только по строкам, удовлетворяющим условию WHERE в самом определении индекса. Это уместно, когда типичные запросы всегда фильтруют по одному и тому же условию, а остальные строки таблицы для этих запросов не важны — тогда индексировать их незачем, и индекс получается меньше и точнее.

На стенде индекс idx_paid_partial ON events(user_id) WHERE status = 'paid' под запрос WHERE status = 'paid' AND user_id = 777 даёт Index Scan, всего 3 подходящие строки, 0.042 мс — индекс уже отфильтрован по status, дополнительная работа планировщика — только найти нужный user_id среди уже отфильтрованных paid-строк. Занимает такой индекс 5784 КБ — заметно меньше полного индекса по user_id на всей таблице.

Функциональный (expression) индекс строится не по значению колонки, а по результату выражения над ней — и это единственный способ ускорить запрос, где предикат уже содержит функцию. На стенде idx_status_lower ON events(lower(status)) под запрос WHERE lower(status) = 'paid' даёт Index Scan, 113.3 мс на ~500 000 подходящих строк (селективность здесь низкая — примерно четверть таблицы, поэтому время заметно выше, чем у точечных примеров выше, но индекс всё равно используется, потому что выражение в запросе и в определении индекса совпадают буквально). Здесь и кроется важная тонкость — индекс должен соответствовать запросу по разобранному выражению и типам аргументов. Регистр имени функции при этом роли не играет: lower(status) и LOWER(status) парсятся в один и тот же вызов, планировщик сравнивает уже разобранные деревья выражений, а не текст. Значение имеет именно эквивалентность выражения — та же функция над тем же выражением и с теми же типами; малейшее расхождение (другой каст, иная функция, другой порядок аргументов) — и планировщик индекс просто не увидит.

Когда индекс не используется

Самая частая причина «индекс есть, а не работает» — это не баг планировщика, а несовпадение выражения в запросе с тем, что реально проиндексировано. Три реальных плана со стенда показывают это буквально:

-- запрос БЕЗ функции/каста — индекс используется
Index Scan using idx_user_status on events
  Index Cond: (user_id = 555)
Execution Time: 0.170 ms

-- та же колонка, с функцией upper() — индекс НЕ используется
Seq Scan on events
  Filter: (upper(status) = 'PAID')
Execution Time: 631.755 ms

-- та же колонка, с явным кастом ::text — индекс НЕ используется
Seq Scan on events
  Filter: ((user_id)::text = '555'::text)
Execution Time: 255.980 ms

Контраст последней пары нагляднее всего: user_id = 555 находит индекс idx_user_status и отрабатывает за 0.170 мс. Тот же user_id, тот же результат — 555, но записанный как user_id::text = '555' — уходит в Seq Scan за 255.980 мс, хотя предикат находит всего 22 строки из 2 миллионов, то есть селективность здесь исключительно высокая и по сути кроссовера сама по себе не оправдывает выбор seq scan. Причина не в селективности — она в том, что user_id::text это уже не колонка user_id, а результат приведения типа, и обычный B-tree индекс по user_id (тип bigint) не индексирует значения этого выражения. PostgreSQL не умеет «развернуть» каст обратно к исходной колонке для произвольных типов — с точки зрения планировщика это два разных выражения, и индекс просто не подходит под второе, независимо от того, насколько избирательным был бы результат.

upper(status) = 'PAID' — тот же механизм: индекс idx_status_lower существует, но построен по lower(status), а не по upper(status) — выражения не совпадают буквально, и Seq Scan неизбежен, пока не появится индекс именно по upper(status) или запрос не перепишут под уже существующий lower. Итоговый список типичных причин, по которым готовый индекс не используется:

• функция или приведение типа над индексируемой колонкой без соответствующего expression-индекса под то же самое выражение;
• обращение не к первой колонке (или не к префиксу колонок) составного индекса — left-prefix, разобранный выше;
• низкая селективность предиката — планировщик рационально выбирает Seq Scan, потому что это дешевле, что и показал кроссовер;
• устаревшая статистика (ANALYZE давно не выполнялся) — планировщик оценивает селективность неверно и делает не тот выбор;
• маленькая таблица, целиком помещающаяся в несколько страниц, — Seq Scan дешевле для любого предиката;
• OR между условиями по разным колонкам без соответствующих индексов на каждую ветвь — часто требует BitmapOr или переписывания через UNION.

Разбор того, как эти же ловушки — особенно неявные касты — незаметно возникают из кода приложения и ORM (несовпадение типа параметра и типа колонки в JDBC/pgx), отдельно и подробно — в статье #5 серии.

Как проверить: EXPLAIN и статистика

Единственный надёжный способ узнать, использует ли планировщик индекс, — прочитать план, а не гадать по интуиции. EXPLAIN (ANALYZE, BUFFERS) показывает и оценку планировщика, и то, что реально произошло при выполнении: узел плана (Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan), число обращений к буферам (Buffers: shared hit=... read=...) и фактическое время. Расхождение между оценкой (rows=) и фактом (actual ... rows=) — обычно первый сигнал, что статистика устарела и планировщик работает по неверным числам.

Второй инструмент — pg_stat_user_indexes: показывает, обращался ли планировщик к конкретному индексу вообще за время жизни статистики (idx_scan), независимо от плана одного конкретного запроса. Индекс с нулём сканирований за недели работы — кандидат на удаление, а не на «подождать ещё». Полный разбор чтения EXPLAIN — стоимости узлов, BUFFERS, статистики автовакуума и типичных антипаттернов вроде N+1 — отдельная статья «Оптимизация запросов PostgreSQL: EXPLAIN на практике»готовится, с 6 октября, где та же таблица events разбирается ещё глубже. А чтобы разобрать конкретный план не глазами по тексту, а визуально, удобен разборщик explain.tensor.ru: вставляете вывод EXPLAIN (ANALYZE, BUFFERS) — он подсвечивает самые дорогие узлы и места, где фактические время и число строк расходятся с оценкой планировщика.

Что дальше

Эта статья дала практику выбора: селективность, покрывающие и составные индексы, частичные и функциональные, и главное — как читать план, когда индекс отказывается работать. Дальше серия идёт вширь и вглубь: как та же логика индексов реализована в PostgreSQL, MongoDB и Tarantool — статья #3, «Индексы в разных БД». Где индексирование превращается в антипаттерн — over-indexing, write-amplification, bloat — статья #4, «Паттерны и антипаттерны индексирования». И как ORM и язык приложения незаметно ломают использование индекса — статья #5, «Индексы и языки/ORM»готовится, с 30 июля.

Источники

• PostgreSQL Documentation — Chapter 11. Indexes, 11.3. Multicolumn Indexes, 11.8. Partial Indexes, 11.9. Index-Only Scans and Covering Indexes
• PostgreSQL Documentation — 14.1. Using EXPLAIN, Planner Statistics
• Стенд: digital-cookbook/databases/db-indexes/postgres (PostgreSQL 18.4)