Ускорение запросов PostgreSQL: стенд из восьми случаев

Задача

«Отчёт открывается десять секунд», «страница заказов виснет к вечеру» - с этого начинается почти любая задача на оптимизацию. Дальше обычно идёт спор без доказательств: одни предлагают навесить индексы, другие - взять сервер помощнее. Стенд отвечает иначе: восемь типовых тяжёлых запросов, у каждого показан план до правки, само изменение, план после и замеренное время. Видно не только «стало быстрее», но и за счёт чего.

Проект демонстрационный: витрина синтетическая, сгенерирована скриптом, клиентских данных в ней нет ни строки.

Решение

Витрина интернет-магазина: 120 000 клиентов, 900 000 заказов, 1 800 000 позиций заказа. Генерация идёт с фиксированным зерном случайных чисел, поэтому объём и распределение витрины повторяются на любой машине. PostgreSQL 17 поднимается в контейнере с настройками по умолчанию: подкрученных параметров, которых не будет на боевом сервере, здесь нет.

Восемь случаев закрывают ходовой набор проблем: выборка заказов клиента без индексов, соединение трёх таблиц, отчёт по месяцам, оконная функция с сортировкой на диск, глубокая страница через LIMIT 20 OFFSET 200000 и она же через курсор, и два случая, которые по моему опыту встречаются чаще всего: индекс уже есть, но планировщик его не берёт, потому что слева от равенства не колонка, а функция над ней.

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

Цифры одного прогона на ноутбуке: заказы клиента по почте - 50,2 против 0,144 мс, в 349 раз; поиск клиента по почте без учёта регистра - 29,5 против 0,052 мс, в 567 раз; отчёт по месяцам за год - 65,3 против 27,0 мс, всего в 2,4 раза. Трёхзначный выигрыш бывает не везде, и в сводке прогона это видно сразу по всем восьми строкам.

Детали, которые легко не заметить

• Два случая из восьми чинятся без единой строки DDL. В одном условие created_at::date = ... переписано в диапазон дат, и уже существующий индекс наконец заработал: 37,4 против 3,6 мс. Во втором глубокая страница переписана с OFFSET на курсор при том же индексе: 21,4 против 0,067 мс.
• Переписанный запрос обязан возвращать то же самое, и стенд проверяет это в том же прогоне. Проверки разной силы, и честнее сказать прямо: для страницы через курсор сверяются сами идентификаторы строк, совпали 20 из 20; для запроса с приведением типа пока сверяется только число строк в обеих формах.
• Время не единственная метрика. Стенд печатает и решающий узел плана: в четырёх случаях последовательное чтение таблицы сменяется на Index Only Scan, в случае с курсором число прочитанных строк падает с 200 020 до 20, а внешняя сортировка на диск (18 080 КБ) просто исчезает из плана.
• Абсолютные значения машинозависимы: они зависят от процессора, диска и от того, чем ещё занят компьютер. Во время отладки этого же стенда под нагрузкой ноутбука те же запросы показывали времена в 3-5 раз хуже. В описании стенда это сказано прямо, вместе с оговоркой: при повторе брать цифры своего прогона, а не эти.

Что осталось

Кадры прогона снимались в разные заходы, и числа между ними не сходятся: у одного и того же случая на двух картинках разное время. Обе цифры настоящие, разброс между прогонами на ноутбуке - обычное дело, но в одной подборке это выглядит несогласованно, поэтому кадров на странице пока нет. Нужна пересъёмка всех кадров с одного прогона; до этого карточка опирается на сам стенд, который воспроизводит все восемь случаев одной командой.