В первой статье я объяснял кэш через холодильник. Продолжу тем же способом. Сейчас будет про индексы, а потом про то, почему индекс есть, а база его игнорирует.
Статья для тех, кто индексы ставил, но EXPLAIN читал по диагонали.
Указатель
Открываешь книгу на 800 страниц. Нужно одно слово.
Можно листать всё подряд, страницу за страницей. А можно открыть алфавитный указатель в конце: слово, номер страницы, готово.
В первом приближении база работает похоже. Пишешь:
SELECT * FROM users WHERE email = 'vasya@example.com';
Если индекса на email нет, база честно читает всю таблицу от начала до конца. Все строки. Каждый раз.
На миллионе строк это 25,7 мс и Rows Removed by Filter: 333333 в плане. Число стоит понимать правильно: полный проход шёл параллельно, и EXPLAIN показывает здесь среднее на один loop, а не итог по таблице. С индексом тот же запрос отрабатывает за 0,23 мс. Разница в сто с лишним раз, и она растёт вместе с таблицей.
Индекс здесь играет роль того самого указателя. Это отдельная структура, обычно B-дерево, где ключи хранятся отсортированными, а рядом с ключом лежит ссылка на строку в таблице: TID, то есть номер страницы и позиция внутри неё.
CREATE INDEX idx_users_email ON users (email);
Как посмотреть, что реально делает база
Проще всего спросить у неё саму:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'vasya@example.com';
EXPLAIN покажет план, ANALYZE ещё и выполнит запрос с замерами. Что искать в выводе:
Seq Scan. Последовательное чтение всей таблицы. На большой таблице это тот самый вечер с перелистыванием 800 страниц. Вместо гологоSeq Scanможно увидетьGatherиParallel Seq ScanсWorkers Planned: 2, тогда Postgres разделит полный проход между воркерами. Суть та же, таблица читается целиком.Index Scan. Сначала обращение к индексу, затем к таблице за самими строками.Index Only Scan. Все нужные колонки есть в индексе. Если visibility map позволяет подтвердить видимость строк, в таблицу идти не придётся вообще.Bitmap Index Scan. База собрала из индекса битовую карту подходящих страниц и читает их пачкой. Промежуточный вариант, когда строк много, но не вся таблица.Rows Removed by Filter. Сколько строк узел отбросил после проверки фильтра. Большое значение рядом сSeq Scanэто повод проверить, нельзя ли сократить чтение индексом.
Полезно добавить BUFFERS:
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
Тогда видно работу с буферами. Сколько блоков уже лежало в shared buffers, а сколько Postgres пришлось прочитать. Это честнее времени выполнения, которое гуляет от прогрева. Слово «прочитать» здесь не равно «поднять с диска»: между Postgres и диском есть ещё страничный кэш операционной системы.
Индекс есть, а база его не использует
Самая частая жалоба. Дальше причины в том порядке, в каком я их встречал.
Функция над колонкой
-- индекс на email не поможет SELECT * FROM users WHERE lower(email) = 'vasya@example.com';
Индекс построен по значению email, а условие спрашивает про lower(email), и для базы это другое выражение. Лечится индексом по выражению:
CREATE INDEX idx_users_email_lower ON users (lower(email));
Эвристика такая. Условие должно совпадать с тем выражением, по которому построен индекс. Перестановка сторон сравнения тут ничего не меняет, а вот обёртка вокруг колонки меняет всё. С приведением типов сложнее: часть преобразований планировщику не мешает, часть закрывает индекс так же, как функция. Проверять EXPLAIN, а не держать список в голове.
Низкая селективность
SELECT * FROM orders WHERE status = 'active';
Если active занимает 80% таблицы, планировщик посмотрит на статистику и может решить, что последовательное чтение дешевле. По индексу пришлось бы прыгать почти по всем страницам, да ещё вразнобой.
В моём прогоне так и произошло. Индекс на status есть, но запрос по status = 'active' (800 тыс. строк из миллиона) уходит в Seq Scan и занимает 63 мс. Тот же индекс на status = 'refunded' (3 тыс. строк, 0,3%) даёт Index Scan за 12 мс. Индекс не «сломался» — планировщик выбрал дешёвый путь.
Индексы хороши там, где условие отсекает малую часть данных. Для «активных» из 80% таблицы в этом запросе индекс не помог, а для status = 'refunded' из 0,3% оказался как раз тем, что нужно.
Когда нагрузка спрашивает именно про редкое значение, например дашборд по возвратам, помогает частичный индекс:
CREATE INDEX idx_orders_refunded ON orders (created_at) WHERE status = 'refunded';
Он занимает место только под нужные строки и потому маленький.
Обратите внимание на состав ключа. status в него не входит вовсе, хотя запрос спрашивает именно про status. Это не опечатка. Условие вынесено в предикат самого индекса, и попадание записи внутрь уже доказывает, что статус нужный, перепроверять нечего. Ключом остаётся created_at, потому что дашборд по возвратам всё равно сортирует по дате.
В плане это выглядит как Bitmap Index Scan по частичному индексу внутри Bitmap Heap Scan. Postgres собирает битовую карту подходящих страниц и читает их пачкой. Нормальная картина, когда строк несколько десятков и они разбросаны по таблице.
Порядок колонок в составном индексе
Вот на этом запросе я ждал, что планировщик пойдёт в индекс.
SELECT * FROM events WHERE created_at > now() - interval '7 days';
Индекс на месте:
CREATE INDEX idx_events_user_created ON events (user_id, created_at);
Планировщик решил иначе. На миллионе строк он даже не пошёл в индекс, выбрал Parallel Seq Scan и прочитал таблицу целиком.
Причина в порядке колонок. Индекс отсортирован сначала по user_id, а внутри каждого user_id по created_at. Как телефонная книга: сначала фамилия, внутри фамилии имя. Найти всех с именем Иван, не зная фамилии, по такому порядку уже неудобно, Иваны разбросаны по всей книге.
Поэтому индекс хорошо работает для запросов, где первая колонка известна:
WHERE user_id = 42 WHERE user_id = 42 AND created_at > now() - interval '7 days' WHERE user_id = 42 ORDER BY created_at DESC -- сортировать не придётся
Ещё деталь из тех же прогонов. Запросы с user_id = 42 идут через Bitmap Heap Scan, а не через чистый Index Scan, потому что строк два десятка и они раскиданы по разным страницам. А вот WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 даёт Index Scan Backward без узла Sort. Сортировать не приходится вовсе, порядок уже оплачен при построении индекса. Это и есть главный бонус правильного порядка колонок.
Для типичного запроса вида a = ... AND b > ... разумная отправная точка такая: колонки под точное сравнение впереди, за ними диапазон или сортировка. Окончательно решает EXPLAIN на реальной нагрузке, потому что на выбор влияет ещё и набор запросов, селективность и возможность index-only scan.
Скрипт с запросами и планами из этой статьи я оставил на backendstart.ru, там можно прогнать те же примеры у себя.
Нужны только пара колонок, а база идёт в таблицу
Если в индексе есть всё, что запрашивают, база может не ходить в таблицу вообще:
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name); SELECT name FROM users WHERE email = 'vasya@example.com'; -- в плане: Index Only Scan
INCLUDE добавляет колонку в индекс прицепом. Искать по ней нельзя, а отдать её можно без обращения к таблице. В плане появляется Index Only Scan, и в моём прогоне рядом стоит Heap Fetches: 0, то есть в heap Postgres действительно не ходил. Приём заметно помогает на горячих запросах.
Цена индексов
Указатель в книге тоже занимает страницы, и при каждой правке текста его надо переделывать. В базе так же, и об этой стороне вспоминают реже.
Место. Индекс — отдельная структура, и сумма индексов легко перевешивает сами данные. В моём тестовом прогоне таблица на миллион строк занимает 86 МБ, а пять индексов на ней 161 МБ. Почти вдвое больше самих данных.
Запись. Каждый INSERT и DELETE обновляет все индексы таблицы. С UPDATE тоньше: PostgreSQL умеет HOT-обновление, когда изменённые колонки не входят ни в один индекс, и тогда индексы не трогаются. Индекс на часто меняющейся колонке этот механизм отключает.
Планировщику тоже думать. Чем больше индексов, тем больше вариантов он перебирает.
Значит, индексы ставят под конкретные запросы, а не «на всякий случай». Найти лишние можно так:
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;
Ноль обращений с момента сброса статистики — кандидат на удаление.
На этом я сам чуть не попался. Сделал запрос, тут же заглянул в статистику и увидел idx_scan = 0 у индекса, которым только что воспользовался. Статистика собирается асинхронно и доезжает с задержкой. Ещё она должна копиться достаточно долго, и это не должен быть индекс под уникальность или под отчёт раз в квартал.
Как добавлять индекс на живом проде
Обычный CREATE INDEX берёт блокировку на запись. Пока индекс строится, таблица недоступна для вставок и обновлений. На большой таблице это минуты, и это заметят все.
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
CONCURRENTLY не блокирует обычные INSERT, UPDATE и DELETE на всё время построения. Плата двойная: строится дольше и не работает внутри транзакции, то есть в миграцию его надо заводить аккуратно. Если построение упадёт, останется индекс в состоянии INVALID, его придётся удалить (тоже CONCURRENTLY) и повторить.
При этом CONCURRENTLY не делает операцию дешёвой для прода, он снимает только длительную блокировку записей. Фаз он выполняет больше, ждёт незакрытые транзакции, и CPU с диском всё равно съест. Перед созданием индекса на проде стоит посмотреть его ожидаемый размер и текущую нагрузку. Индекс на терабайтной таблице в час пик — плохая идея даже с CONCURRENTLY.
Напоследок
Если из статьи останется что-то одно, пусть будет это. Индекс не делает запрос быстрым сам по себе. Он даёт планировщику ещё один путь, а пойдёт Postgres по нему или нет, зависит от того, дешевле ли этот путь остальных.
Поэтому EXPLAIN встречается здесь чаще, чем CREATE INDEX. Пока не посмотрел план, у тебя есть предположение, а не факт. У меня в этих прогонах предположение не сошлось дважды: на составном индексе, куда планировщик не пошёл, и в pg_stat_user_indexes, где свежий idx_scan ещё не доехал.
Все планы и цифры из статьи сняты на PostgreSQL 17.2, таблица на миллион строк.
