Продвинутые индексы: частичные, по выражению, покрывающие

[Egor_DBA]

Урок 23. Продвинутые индексы: частичные, по выражению, покрывающие

Обычный индекс по столбцу - это база, но в реальной нагрузке он часто либо слишком жирный, либо вообще не используется планировщиком. В этом уроке разбираем три приёма тонкой оптимизации в postgresql: частичный индекс (индексируем не всю таблицу, а нужный срез), индекс по выражению (индексируем результат функции, а не голый столбец) и покрывающий индекс с INCLUDE, который даёт index-only scan без похода в таблицу. Попутно затронем многоколоночные и уникальные частичные индексы. Всё на демобазе Авиаперевозки от Postgres Professional, схема bookings.



Как это работает

Индекс в PostgreSQL по умолчанию - это B-дерево, в котором лежит запись для КАЖДОЙ строки таблицы. Чем больше строк, тем больше дерево, тем дольше его обновлять при INSERT и UPDATE и тем больше места он занимает на диске и в кеше. Идея продвинутых индексов простая: не индексировать лишнее и складывать в индекс ровно то, что нужно запросам.

Частичный индекс описывается через WHERE при создании. В дерево попадают только строки, удовлетворяющие условию. Если 95 процентов рейсов уже улетели и вас интересуют только статусы Delayed и Scheduled, нет смысла держать в индексе остальные миллионы строк. Меньше индекс - быстрее запись и меньше работы вакууму.

Индекс по выражению хранит не значение столбца, а результат функции от него, например lower(login) или (data->>'phone'). Это нужно, потому что планировщик использует индекс по столбцу x только тогда, когда в условии стоит ровно x, а не f(x). Запрос WHERE lower(email) = 'a@b.ru' обычный индекс по email проигнорирует - выражение в условии должно текстуально совпасть с выражением в индексе.

Покрывающий индекс - это когда все нужные запросу столбцы уже лежат в самом индексе, и Postgres может ответить, не читая таблицу. Такой доступ называется index-only scan и он заметно быстрее обычного index scan. Ключевые столбцы (по которым идёт поиск и сортировка) ставим в основную часть, а столбцы только для возврата - в INCLUDE (появился в PG 11). INCLUDE-столбцы не участвуют в сортировке и не раздувают сравнения в дереве, но позволяют отдать данные из индекса.

Важная тонкость про index-only scan: Postgres всё равно сверяется с visibility map, чтобы понять, видима ли строка текущей транзакции. Если страница таблицы недавно менялась и не помечена как all-visible, движок сходит в heap за версией строки. Поэтому index-only scan хорошо работает на относительно стабильных, провакуумленных таблицах.

SQL и примеры

Частичный индекс. Индексируем только незавершённые рейсы - именно их дёргает оперативное табло:

Код: Выделить всё

CREATE INDEX idx_flights_active
  ON flights (departure_airport)
  WHERE status IN ('Scheduled', 'Delayed', 'On Time');

Такой индекс подхватится, только если в запросе есть совместимое условие по status. Проверяем планом:

Код: Выделить всё

EXPLAIN ANALYZE
SELECT flight_no, scheduled_departure
FROM flights
WHERE departure_airport = 'SVO'
  AND status = 'Delayed';

В выводе explain analyze вы увидите Index Scan using idx_flights_active. Без условия по status планировщик этот индекс не возьмёт - и это правильно.

Индекс по выражению. Хотим искать пассажиров без учёта регистра. Делаем индекс по lower():

Код: Выделить всё

CREATE INDEX idx_tickets_name_lower
  ON tickets (lower(passenger_name));

SELECT ticket_no, passenger_name
FROM tickets
WHERE lower(passenger_name) = 'ivan petrov';

В условии WHERE стоит ровно lower(passenger_name) - то же выражение, что в индексе, поэтому он сработает. Если написать lower(passenger_name) LIKE 'ivan%', индекс тоже пригодится для префикса (с оператором text_pattern_ops или в C-локали).

Покрывающий индекс и index-only scan. Частый отчёт - по номеру билета вернуть книгу и имя:

Код: Выделить всё

CREATE INDEX idx_tickets_cover
  ON tickets (ticket_no) INCLUDE (book_ref, passenger_name);

EXPLAIN ANALYZE
SELECT ticket_no, book_ref, passenger_name
FROM tickets
WHERE ticket_no = '0005432000284';

В плане при прогретой visibility map будет Index Only Scan, а Heap Fetches стремится к нулю. Все три столбца берутся из индекса, в таблицу ходить не надо.

Уникальный частичный индекс. Классика - частичная уникальность. Допустим, мы храним черновики бронирований и хотим, чтобы активный book_ref был уникален, а отменённые не мешали:

Код: Выделить всё

CREATE UNIQUE INDEX uq_booking_active
  ON bookings (book_ref)
  WHERE total_amount > 0;

Уникальность проверяется только на строках, попавших под WHERE. Это способ сделать что-то вроде уникальности с исключениями, которого нет в обычном UNIQUE-ограничении.

Многоколоночный против покрывающего. Если по второму столбцу тоже идёт поиск или сортировка - кладите его в ключ, а не в INCLUDE:

Код: Выделить всё

CREATE INDEX idx_tf_flight_amount
  ON ticket_flights (flight_id, amount);

Здесь amount в ключе помогает и фильтру по диапазону, и сортировке. А вот если столбец нужен только чтобы его вернуть - ему место в INCLUDE.

Частые грабли

• Выражение в запросе не совпало с выражением в индексе. Индекс по lower(x) не сработает для upper(x) или для x без функции. Совпадение текстовое.
• Ждёте index-only scan, а в плане высокий Heap Fetches. Таблицу давно не вакуумили, visibility map холодная. Запустите VACUUM (или ANALYZE и подождите автовакуум).
• Частичный индекс не подхватывается, потому что планировщик не может доказать, что условие запроса входит в предикат индекса. Условие в WHERE запроса должно логически следовать из WHERE индекса.
• INCLUDE-столбцы пытаются использовать для поиска или ORDER BY. Они там не работают - только для возврата значений. Для поиска нужен ключевой столбец.
• Слишком много частичных индексов с пересекающимися условиями. Каждый замедляет запись и путает планировщик. Лучше один продуманный.
• Функция в индексе по выражению должна быть IMMUTABLE. now() или функции с зависимостью от настроек туда нельзя - индекс просто не создастся или будет некорректен.
• Огромный INCLUDE раздувает индекс почти как сама таблица. Index-only scan перестаёт быть выгодным - кладите туда только реально нужные столбцы.

Мини-лаба

Выполните на своём PostgreSQL 15 с развёрнутой демобазой bookings.

• Создайте частичный индекс на flights только для status = 'Delayed' по departure_airport.
• Прогоните EXPLAIN ANALYZE на выборку задержанных рейсов из одного аэропорта и найдите имя своего индекса в плане.
• Создайте индекс по выражению lower(passenger_name) на tickets и проверьте, что поиск в нижнем регистре идёт через индекс.
• Создайте покрывающий индекс на tickets с INCLUDE (book_ref) и добейтесь Index Only Scan в плане.
• Запустите VACUUM ANALYZE tickets и сравните Heap Fetches до и после.
• Сделайте уникальный частичный индекс по своему условию и попробуйте вставить дубль внутри и вне предиката - убедитесь, что блокируется только внутри.
• Сравните размеры индексов через \di+ или pg_relation_size и прикиньте экономию от частичного индекса.

Контрольные вопросы

• Чем частичный индекс выгоднее обычного и в каком случае планировщик его НЕ применит?
• Почему обычный индекс по столбцу не помогает запросу с lower() в условии?
• В чём разница между ключевыми столбцами индекса и столбцами в INCLUDE?
• Что такое index-only scan и почему он иногда всё равно ходит в таблицу (Heap Fetches)?
• Как реализовать уникальность с исключениями средствами одного индекса?
• Какое требование предъявляется к функции, по которой строится индекс по выражению?

[roi249]

А почему мой частичный индекс по status в плане вообще не появляется? Запрос без условия по статусу как раз и шёл, теперь понятно - надо чтобы WHERE запроса входил в предикат индекса.

[triheadz]

Поставил INCLUDE на три столбца, Index Only Scan заработал, но Heap Fetches был огромный. Помог VACUUM ANALYZE, после него почти ноль. Спасибо за подсказку про visibility map.