Индексы в Postgres: почему ваш индекс не работает и что такое INCLUDE

Ваш SELECT всё ещё тормозит, хотя вы добавили индекс? Вот три главные причины, почему Postgres его не использует: вы обернули колонку в lower(), перепутали порядок колонок в композитном индексе или полагаетесь на INCLUDE ради скорости записи. Индекс — это отсортированная структура, по которой база делает бинарный поиск вместо чтения всей таблицы. Но даже правильно созданный индекс легко выключить одной лишней функцией.

Джон Чартер в статье «Things you didn't know about indexes» разбирает не только базовые принципы B-tree, но и три типа индексов, о которых новичкам не рассказывают: функциональные, частичные и покрывающие. Переводим полностью, с примерами на таблице покемонов и объяснением, почему EXPLAIN — ваш лучший друг.

Начнём с того, что вы, вероятно, уже знаете

Индекс в базе данных похож на алфавитный указатель в конце учебника. Хотите найти страницы про фосфор? Идёте в конец, находите тему и номера страниц рядом с ней:

			... Oxygen .................. 42, 88, 103
Periodic table .......... 12–15
Phosphorus .............. 67, 91
Photosynthesis .......... 54, 72, 110
Potassium ............... 33, 78 ...
		

Представим таблицу покемонов:

			id  | name       | type_1   | type_2 | generation | is_legendary | base_attack
----+------------+----------+--------+------------+--------------+-------------
1   | Bulbasaur  | Grass    | Poison | 1          | false        | 49
4   | Charmander | Fire     | NULL   | 1          | false        | 52
...
25  | Pikachu    | Electric | NULL   | 1          | false        | 55
...
150 | Mewtwo     | Psychic  | NULL   | 1          | true         | 110
		

Без индекса поиск Пикачу означает, что база прочитает каждую строку, проверяя колонку name. На четырёх строках это мгновенно. На десяти миллионах — уже проблема. Такое чтение называется full table scan, и оно не медленное само по себе: современные базы легко прошивают миллионы строк в секунду. Проблема в линейной сложности — удваиваете число строк, удваиваете время. Индексный поиск этой разницы почти не замечает.

Но если добавить индекс на name, получится примерно то же, что указатель в учебнике:

			name         row
-----------+----
Bulbasaur  → 1
Charmander → 4
Mewtwo     → 150
Pikachu    → 25
		

Данные отсортированы, поэтому база может сделать бинарный поиск по имени и найти нужную строку, а не сканировать всю таблицу. Под капотом Postgres хранит это как B-tree (сбалансированное дерево), но идея такая же, как в учебнике: отсортированные данные, по которым можно быстро искать.

Итак, индексируем всё подряд, верно? Не так быстро.

Цена индексации

Как бы ни хотелось проиндексировать всё, у индексов есть компромиссы. Главное правило:

Чтение ускоряется, запись замедляется.

С новым блестящим индексом каждый INSERT, UPDATE и DELETE должен обновить и индекс тоже. Когда мы добавляем нового покемона, база находит правильное место в отсортированном индексе имён и вставляет его туда. А индексов может быть несколько — умножайте эту работу на каждый из них.

Плюс индексы — это реальные структуры данных, и их нужно хранить. Они живут на диске и подгружаются в память: в Postgres это shared_buffers. У таблицы с восемью индексами девять объектов, которые конкурируют за буфер, а не один.

И не забываем про планировщик (query planner) — компонент, который перед каждым запросом оценивает варианты доступа к данным и выбирает самый дешёвый. Чем больше индексов, тем больше вариантов он взвешивает, так что время планирования растёт и на быстрых выборках может даже превысить время выполнения.

Отдельный риск на проде. Обычный CREATE INDEX блокирует таблицу на запись до конца построения. На таблице в десятки миллионов строк это минуты недоступности. В production используйте CREATE INDEX CONCURRENTLY — он строит индекс без эксклюзивной блокировки, ценой чуть большего времени и того, что операция может быть прервана и оставить индекс в состоянии INVALID (который надо пересобрать).

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

Вы взвесили компромиссы, решили, что индекс уместен. Отлично. Но он не работает как ожидалось: улучшения нет или, хуже того, скорость падает. Разберём типичные грабли.

Композитные индексы и порядок колонок

Возвращаясь к таблице покемонов: вы могли решить, что хороший индекс — это индекс на type_1 и type_2. «Покажи всех покемонов типа Water и Flying» — вполне разумный запрос.

			CREATE INDEX ON pokemon (type_1, type_2);
		

Этот индекс определённо поможет запросам вроде:

			SELECT * FROM pokemon WHERE type_1 = 'Water';
SELECT * FROM pokemon WHERE type_1 = 'Water' AND type_2 = 'Flying';
		

Но вот этому — уже нет:

			SELECT * FROM pokemon WHERE type_2 = 'Flying';
		

Удивлены? Когда вы создаёте композитный индекс (type_1, type_2), вы просите базу построить структуру примерно такого вида:

			Bug      → Flying   → [Butterfree, Beedrill, ...]
         → Poison   → [Venonat, Spinarak, ...]
         → Water    → [Surskit, ...]
Electric → NULL     → [Pikachu, Raichu, ...]
         → Flying   → [Zapdos, ...]
         → Steel    → [Magnemite, ...]
Fire     → NULL     → [Charmander, Vulpix, ...]
         → Flying   → [Charizard, Moltres, ...]
Grass    → Poison   → [Bulbasaur, Oddish, ...]
Water    → NULL     → [Squirtle, Psyduck, ...]
         → Flying   → [Wingull, Pelipper, ...]
		

Сначала сортировка по type_1, затем по type_2 внутри каждой группы. Для запросов по type_1 индекс отличный, для запросов по type_1 AND type_2 — ещё лучше, но для запросов только по type_2 он не будет использован так, как вы надеетесь. Посмотрите на Flying: он разбросан под Bug, Electric, Fire и Water — базе некуда прыгнуть.

Задайте себе вопрос: какие запросы будут частыми? Если по type_2 вы будете искать так же часто, как по type_1, создайте второй индекс на type_2.

Функции убивают индекс

Поиск без учёта регистра — частая фича. Пользователям всё равно, пишут ли они «Pikachu», «pikachu» или «PiKaChU» — они просто хотят результат. Поэтому можно написать так:

			SELECT * FROM pokemon WHERE lower(name) = 'pikachu';
		

Я бы не обратил на такой запрос особого внимания. Выглядит нормально. У нас есть индекс на name, запрос должен летать. Но, конечно, он не летает.

Почему? Индекс на name, а не на lower(name). Для базы это две совершенно разные вещи. Ваш индекс — это отсортированный список значений вида Bulbasaur, Charmander, Squirtle, Pikachu, а не bulbasaur, charmander, squirtle, pikachu. Так что когда вы просите строки, где lower(name) = 'pikachu', у базы нет отсортированной структуры для этого — и она откатывается к сканированию всей таблицы, приводя каждое имя к нижнему регистру по ходу дела.

Это касается любой функции, оборачивающей колонку. Если база не видит индексированную колонку слева от сравнения, индекс выбывает из игры.

И вот настоящие грабли: неявные преобразования тоже считаются. Классический пример — колонка user_id типа bigint сравнивается со строкой (WHERE user_id = '42') или timestamp сравнивается со строковой датой. Postgres тихо добавит приведение типа — и индекс работать перестанет с тем же эффектом, что и явная обёртка функцией.

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

Как избежать этих ловушек

Короткий ответ: не гадайте. Измеряйте. Как? Спросите базу.

В Postgres есть инструмент EXPLAIN, который показывает, как именно база собирается выполнить запрос. Поставьте его перед любым SELECT — и получите отчёт:

			EXPLAIN SELECT * FROM pokemon WHERE name = 'Pikachu';
		

			Index Scan using pokemon_name_idx on pokemon
  Index Cond: (name = 'Pikachu'::text)
		

Index Scan — это то, что вы хотите видеть. Значит, база использует индекс. Сравните с проблемным запросом:

			EXPLAIN SELECT * FROM pokemon WHERE lower(name) = 'pikachu';
		

			Seq Scan on pokemon
  Filter: (lower(name) = 'pikachu'::text)
		

Seq Scan значит, что читается каждая строка. Никаких индексов.

Если хотите реальные тайминги вместо оценок планировщика, используйте EXPLAIN ANALYZE. Он действительно запускает запрос и сообщает, что произошло. Попробуйте на запросе, который, как вам кажется, вы понимаете — результаты часто удивляют. А если Postgres тормозит не из-за индексов — у нас есть 12 шагов диагностики, от pg_stat_activity до auto_explain.

Чего вам не рассказывали

Мы разобрали базовый индекс на одной колонке и его композитного брата. Вместе они покрывают большинство случаев. Но есть ещё несколько видов индексов, которые иногда оказываются именно тем, что нужно.

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

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

Мы затрагивали это в разделе про функции. Проблемный запрос был такой:

			SELECT * FROM pokemon WHERE lower(name) = 'pikachu';
		

Индекс на name не помогает, потому что база ищет строки с lower(name) = ..., а отсортированной структуры для lower(name) у неё нет. Исправление — дать ей такую структуру:

			CREATE INDEX ON pokemon (lower(name));
		

Это функциональный индекс (его ещё называют индексом на выражении). Вместо того, чтобы индексировать сырую колонку, вы индексируете результат выражения, применённого к ней. Конечно, это не ограничивается lower(). Можно индексировать любое детерминированное выражение:

			CREATE INDEX ON pokemon (lower(name));
CREATE INDEX ON users ((created_at::date));
CREATE INDEX ON pokemon ((base_attack * 2));
		

Годится любая функция с меткой IMMUTABLE — в Postgres это означает, что она возвращает одинаковый результат для одинаковых аргументов при любых условиях (в отличие от STABLE, которая детерминирована только в рамках одной транзакции, и VOLATILE).

Но осторожно. Если тянуться к функциональному индексу при первом же случае, это часто плохой запах. Если мы так часто ищем по имени в нижнем регистре, почему мы вообще не храним имя в нижнем регистре? Альтернативы: отдельная колонка name_lower, которую заполняете на вставке; тип citext (case-insensitive text) из одноимённого расширения; или COLLATE с регистронезависимой коллацией. Функциональный индекс — когда изменить хранение уже нельзя.

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

Большинство индексов покрывает каждую строку таблицы. Но иногда вы запрашиваете только её небольшой срез — и индексировать всё подряд расточительно. Напомню: индексы не бесплатны.

В нашей таблице покемонов есть колонка is_legendary. На момент написания существует около 1000 видов покемонов, из которых легендарными считаются всего 80 — меньше 10%.

Представьте приложение с опцией «показать всех легендарных». Изначально так и хочется создать составной индекс:

			CREATE INDEX ON pokemon (is_legendary, name);
		

Это работает, но это перебор. Индекс теперь содержит запись для каждого покемона, а подавляющее большинство — не легендарные. Мы будем платить за хранение и запись 1000 строк, чтобы обслуживать запросы, которым интересны только 80. Более того, поскольку is_legendary — булево, получается структура, где сначала идёт разделение на False и True, и первая группа — это 920 записей мёртвого груза, оплачивающих свою долю каждой записи в таблицу ради запросов, которые их игнорируют.

Частичный индекс покрывает только строки, которые подходят под условие:

			CREATE INDEX ON pokemon (name) WHERE is_legendary = true;
		

Теперь в индексе 80 записей вместо 1000. Он меньше, быстрее в запросах; а запросы вида WHERE is_legendary = true используют его без вопросов. Запросы, которые не подходят под условие (WHERE is_legendary = false), откатываются к full table scan — что вам и нужно. Эти запросы и так матчат почти каждую строку, так что индекс им не особо помог бы.

Любая колонка, где вы почти всегда запрашиваете одно значение, — кандидат на частичный индекс. Представьте индекс на колонке email в таблице users, где у вас реализован soft-delete:

			CREATE INDEX ON users (email) WHERE deleted_at IS NULL;
		

Soft-deleted строки почти никогда не запрашиваются, но без фильтра всё равно раздували бы ваши индексы.

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

Когда база использует индекс, чтобы найти строку, это только половина работы. Индекс указывает, где строка лежит, но потом база должна сходить в саму таблицу и забрать остальные колонки, которые запросил запрос. Две поездки.

А что, если я скажу, что так делать необязательно?

Если в индексе уже есть все колонки, которые нужны запросу, база может ответить на запрос из одного только индекса. Это покрывающий индекс. В EXPLAIN вы увидите его как Index Only Scan — три самых сладких слова из возможного вывода EXPLAIN.

Одна важная оговорка. Даже при Index Only Scan Postgres всё же заглядывает в visibility map — битовую карту, которая для каждой страницы таблицы отмечает, видимы ли все её строки всем активным транзакциям. Если страница «грязная» (были недавние UPDATE/DELETE), Postgres всё равно пойдёт в heap за проверкой MVCC. Так что на интенсивно пишущих таблицах регулярный VACUUM (автовакуум обычно справляется) критичен для того, чтобы Index Only Scan оставался «онли».

Вот запрос:

			SELECT name FROM pokemon WHERE is_legendary = true;
		

Наш частичный индекс из предыдущего раздела идеально покрывает этот запрос. Индекс на name, отфильтрованный по легендарным, — и запрос просит name легендарных. Всё, что нужно запросу, уже в индексе. Поход в таблицу не нужен.

Покрывающие индексы можно строить и специально. Postgres позволяет добавить к индексу дополнительные колонки через INCLUDE:

			CREATE INDEX ON pokemon (name) INCLUDE (base_attack);
		

Теперь на такой запрос можно ответить прямо из индекса:

			SELECT name, base_attack FROM pokemon WHERE name = 'Pikachu';
		

Почему не положить base_attack прямо в индексируемые колонки? Потому что база считает индексируемые колонки тем, по чему нужно сортировать и искать. Если добавить base_attack в ключевые колонки индекса, вы скажете базе сортировать индекс сначала по name, потом по base_attack — дополнительная работа, которая не нужна, если вы ищете только по name. INCLUDE говорит: «тащи эту колонку с собой, но не морочься сортировкой».

Правка автора: когда на самом деле нужен INCLUDE

В комментариях на Reddit автор получил поправку от u/therealgaxbo и обновил статью. Скорость записи — не главная причина использовать INCLUDE для покрывающих индексов: в обоих случаях (колонка в ключе или в INCLUDE) индекс обновляется при каждой записи, разница по нагрузке скромная.

Реальные причины использовать INCLUDE:

Можно включать колонки, у которых типы данных не имеют подходящего operator class для типа индекса (operator class — набор функций сравнения, по которым Postgres строит индекс для конкретного типа; для некоторых типов он просто не определён). Например, колонку box (геометрический прямоугольник) нельзя добавить в B-tree индекс как ключевую — нет оператора «меньше/больше». А через INCLUDE можно.

Можно добавить колонки в уникальный индекс без изменения его семантики уникальности:

			CREATE UNIQUE INDEX ON users (email) INCLUDE (user_id);
		

Это обеспечивает уникальность только по email, но при этом покрывает запросы, которым нужен user_id.

Выводы

Индексы могут казаться новичку чем-то второстепенным. Даже опытные инженеры часто создают их не задумываясь или не создают вовсе. Но грамотная индексация делает или ломает производительность вашей базы.

Хотите глубже? Сайт Use The Index, Luke научит вас всему, что только можно знать про индексы в разных СУБД. А если вы только начинаете с Postgres — загляните в наши основы PostgreSQL: от установки до первых запросов.

И не забудьте запустить EXPLAIN ANALYZE хотя бы на одном запросе сегодня — результаты могут вас удивить.

Источник: jon.chrt.dev — Things you didn't know about indexes