ИИ предложил индекс для SQL-запроса. Мы проверили результат на 100 тысячах строк

В учебной базе PostgreSQL запрос искал оплаченные заказы одного покупателя. Кодекс предложил добавить частичный индекс. В пяти прогонах медианное время выполнения снизилось с 18,197 до 0,067 мс. Запрос вернул те же десять идентификаторов в том же порядке. Прогон сделан на PostgreSQL 17.5 в PGlite с прогретыми данными, поэтому абсолютные времена относятся к этому стенду.

До индекса PostgreSQL проверял почти всю таблицу

В таблице 100 тысяч заказов на тысячу покупателей. Статус paid имеет каждый десятый блок из тысячи заказов. Запрос возвращает только идентификаторы оплаченных заказов покупателя № 42, по возрастанию:

        SELECT id
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY id;
    

В исходном плане PostgreSQL шёл по индексу первичного ключа orders_pkey. Из этого индекса он получал строки сразу в нужном порядке, но затем проверял условия для каждой строки. План зафиксировал 99 990 строк, отброшенных фильтром, и 816 обращений к блокам, которые уже были в кеше.

Частичный индекс оставил в поиске только оплаченные заказы

Предложение Кодекса состояло в том, чтобы сохранить запрос и добавить индекс по паре customer_id, id только для оплаченных заказов. PostgreSQL поддерживает частичные индексы для подмножеств таблицы, заданных условием.

        CREATE INDEX orders_paid_customer_id
ON orders (customer_id, id)
WHERE status = 'paid';
ANALYZE orders;
    

Планировщик выбрал Bitmap Index Scan по новому индексу, затем прочитал нужные строки из таблицы и отсортировал их. На корневом узле первого прогона после создания индекса было 12 попаданий в кеш вместо 816. Результат запроса совпал полностью: от 42 до 90 042 с шагом 10 000.

Почему индекс по первичному ключу не помогал отфильтровать заказы

В исходном плане слово Index уже было, но запрос всё равно делал много работы. Первичный ключ нужен, чтобы быстро найти заказ по id и обеспечить уникальность. В нашем запросе значение id заранее неизвестно. Нужно найти строки по двум другим полям, а id используется только для порядка вывода. Планировщик мог пройти первичный индекс по возрастанию и тем самым избежать отдельной сортировки. Плата за это решение видна в строке Rows Removed by Filter.

В новом плане полезнее смотреть на последовательность узлов. Bitmap Index Scan нашёл позиции подходящих записей по покупателю. Bitmap Heap Scan получил строки из таблицы, а верхний узел отсортировал десять идентификаторов. Отдельная сортировка на этом стенде не помешала ускорению. Сравнение планов показывает, почему наличие Sort само по себе мало говорит о том, какой вариант быстрее.

Проверить это можно по числу фактически обработанных строк. В исходном плане фильтр пропустил десять и отбросил 99 990. В новом плане узел доступа к индексу вернул десять кандидатов. Доля ненужной работы стала видна раньше, чем мы посмотрели на миллисекунды. Это особенно удобно, когда один и тот же план сравнивают на разных компьютерах.

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

У частичного индекса есть граница применения. Он содержит записи со статусом paid, а строки со статусом new в него не попадают. Если пользователь переключит фильтр на новые заказы, этот индекс не сможет ответить на запрос. Для приложения с несколькими частыми статусами полезно отдельно проверить каждый запрос и решить, нужен ли общий составной индекс или частичный помогает именно тому случаю, который важнее для нагрузки.

PostgreSQL должен понять на этапе планирования, что условия запроса подразумевают условие частичного индекса. Здесь в запросе прямо написано status = 'paid'. Если приложение отправляет подготовленную команду с параметром статуса и используется общий план, значение параметра заранее неизвестно. В такой ситуации проверяйте план на том же способе вызова, что и в приложении. Результат, полученный в консоли с подставленным литералом, может оказаться другим.

Размер индекса тоже зависит от структуры данных. В демонстрационной таблице оплачены 10 тысяч заказов из 100 тысяч. Если в рабочей таблице почти все заказы уже оплачены, частичный индекс охватит большую долю строк. Перед выбором посчитайте распределение статусов и частоту типовых запросов. Обновите статистику таблицы и посмотрите, меняется ли выбор плана на покупателях с разным числом заказов.

Как заметить, что переписанный запрос теряет строки

В нашем опыте текст запроса остался прежним, поэтому изменение затронуло способ чтения. ИИ может предложить другой путь: убрать соединение, заменить подзапрос или добавить DISTINCT. Такие правки уже способны изменить результат. Два запроса могут вернуть одинаковое количество строк, но разные значения. Возможен и обратный случай: уникальные значения совпали, а один запрос потерял повторяющиеся строки.

Для проверки двух вариантов в PostgreSQL используйте EXCEPT ALL в обе стороны. В отличие от обычного EXCEPT, он учитывает число повторений. Выровняйте порядок и типы колонок, сохраните оба запроса как CTE и посмотрите, остались ли строки хотя бы в одном направлении. При сравнении проверяйте, что оба запроса видят один снимок данных. Иначе в вывод могут попасть заказы, которые другая транзакция создала между запусками.

Отдельно придумайте крайние случаи: покупатель без заказов, несколько заказов на одну дату, отсутствующее значение в колонке, по которой соединяются таблицы. Если результат должен идти в определённом порядке, задайте ORDER BY с однозначным правилом и сравните последовательность. Разность множеств покажет состав строк, а порядок проверяется отдельно.

Какие метрики снимать рядом с временем

В нашем прогоне сравнивалось поле Execution Time. В него не входят сетевой путь от приложения к базе и последующая обработка результата клиентом. Это удобно для изолированного теста плана. Если задача редакции или команды заключается в том, чтобы ускорить ответ страницы, после SQL-замера проверьте ещё время запроса со стороны приложения. База может выполнять запрос быстрее, а пользователь не заметит разницы, если задержка была в другом месте.

В отчёте EXPLAIN показывает планирование и выполнение отдельно. Полезно сохранять число возвращённых строк, вид доступа к таблице и данные BUFFERS. Здесь речь о попаданиях в кеш в первом замере каждого варианта, а не о количестве физических чтений с диска. Для обоих первых планов поле чтения блоков показывало ноль. Это согласуется с прогревом.

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

Как сравнить ответы двух запросов с учётом повторов

Представим, что модель предложила ускорить отчёт, заменив соединение с платежами на проверку существования платежа. Для заказа с двумя платежами обычное соединение может выдать две строки. Проверка существования вернёт одну. Если исходный отчёт считал платежи, потеря повтора меняет смысл. Если отчёт должен перечислять заказы, наоборот, стоит явно записать это требование и проверять один результат на заказ.

В учебной базе это можно показать на двух маленьких наборах. Следующий запрос имитирует два ответа, в одном из которых значение 42 встречается дважды. Он возвращает одну строку с пометкой lost. Обычный EXCEPT здесь скрыл бы различие между наборами.

        WITH old_result(id) AS (VALUES (42), (42), (43)),
     new_result(id) AS (VALUES (42), (43))
SELECT 'lost' AS change, id
FROM (
  SELECT id FROM old_result
  EXCEPT ALL
  SELECT id FROM new_result
) AS lost
UNION ALL
SELECT 'added' AS change, id
FROM (
  SELECT id FROM new_result
  EXCEPT ALL
  SELECT id FROM old_result
) AS added;
    

Подставляя рабочие запросы, сравнивайте все поля, которые получают пользователи отчёта. Совпадения идентификаторов недостаточно, если переписанный запрос изменил сумму или дату. Сортировку и выдачу по страницам проверьте отдельным тестом. Например, если сортировка задана только по дате и на одну дату приходится много строк, добавьте однозначный второй ключ, прежде чем сравнивать страницы.

Что проверить перед переносом индекса на большую таблицу

На большой таблице план создания индекса сам по себе становится частью работы. В PostgreSQL описаны способы построения индекса, включая CREATE INDEX CONCURRENTLY. У него есть ограничения: например, такую команду нельзя выполнить внутри обычного блока транзакции. Проверьте, умеет ли система миграций запускать подобную операцию и как вы заметите, что она завершилась.

Сначала снимите план на копии с похожим распределением данных. Проверьте запрос для покупателя с десятью заказами и для покупателя, у которого заказов намного больше. Запишите размер нового индекса и его влияние на типовые операции записи. Это даст основание выбрать окно внедрения и набор метрик, по которым можно проверить эффект после миграции.

Как повторить проверку на своих данных

Для учебной таблицы подойдёт такой набор:

        CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL
);

INSERT INTO orders
SELECT n,
       (n - 1) % 1000 + 1,
       CASE WHEN ((n - 1) / 1000) % 10 = 0
            THEN 'paid' ELSE 'new' END
FROM generate_series(1, 100000) AS n;

ANALYZE orders;
    

Запустите обычный запрос и сохраните ответ. Затем добавьте перед ним EXPLAIN (ANALYZE, BUFFERS). PostgreSQL выполнит запрос и покажет фактические строки, время и работу с блоками. После изменения повторите обычный запрос, сравните данные и только затем сравнивайте планы. В этом тесте перед каждой серией был прогрев, а цифры взяты из поля Execution Time пяти отдельных прогонов.

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