PostgreSQL 12 Beta 3: нагрузочный тест перед GA - пять запросов ускорились, два потребовали MATERIALIZED

В конце июля мы разбирали PostgreSQL 12 Beta 1 на копии клиентской DWH-базы: смотрели партиционирование, генерируемые столбцы и первые признаки inline CTE. Тогда вывод был осторожным: CTE-история требует аудита, в продакшн бета не идёт, ждём GA.

С тех пор вышли Beta 2 и Beta 3 - в августе PostgreSQL Global Development Group выпустила третью бету. GA ожидается в октябре. Самое время прогнать нормальный нагрузочный тест - лучше найти сюрпризы сейчас, на стенде. Что и сделали на той же базе, но уже прицельно: подняли стенд на Beta 3, воспроизвели продакшн-нагрузку через pgbench с кастомными скриптами и прошлись по всем аналитическим запросам, которые реально ходят к этой базе в рамках DWH и BI.

Что тестировали

Нагрузка на DWH у клиента - это смесь двух типов. Первый: регулярные выгрузки по расписанию, раз в несколько часов, тяжёлые запросы с агрегациями за период. Второй: запросы от BI-инструмента в ответ на действия аналитиков - чуть легче, но непредсказуемы по времени. Вместе это дюжины уникальных запросов, в половине из которых есть CTE.

Стенд: отдельный сервер, идентичный железо, реплика продакшн-базы на момент снятия дампа. PostgreSQL 11 и PostgreSQL 12 Beta 3 рядом, один и тот же набор запросов, EXPLAIN ANALYZE на каждом.

Пять запросов стали быстрее

Там, где CTE использовался просто как способ разбить запрос на читаемые части - inline дал ожидаемый выигрыш. Оптимизатор получил возможность протолкнуть условия из внешнего запроса внутрь CTE, выбрать лучший план целиком.

Конкретные паттерны, которые ускорились:

Фильтрация по дате в CTE. Раньше CTE отрабатывал без учёта внешнего WHERE dt >= ..., тянул больше данных, потом внешний запрос их фильтровал. Теперь планировщик видит условие насквозь и сразу идёт с индексом в нужный диапазон. Разница на больших таблицах заметная.

JOIN внутри CTE с маленькой driving-таблицей. Когда CTE материализовался, планировщик не знал заранее, сколько строк там окажется, и выбирал консервативный план. После inline он видит статистику обеих таблиц и строит нормальный hash join вместо nested loop по всему результату.

Несколько CTE в цепочке, каждый фильтрует дальше. Здесь inline дал эффект накопительно: каждый следующий CTE получил возможность протолкнуть условия в предыдущий.

Суммарно из семи запросов с CTE пять стали быстрее на Beta 3. На некоторых разница в планах видна невооружённым глазом в EXPLAIN.

Два запроса стали хуже - и почему

Два запроса получили худшие планы. Оба случая разные, и оба поучительные.

Первый случай: CTE с агрегацией, результат используется дважды. Запрос выглядит примерно так:

WITH monthly_totals AS (
    SELECT account_id, SUM(amount) AS total
    FROM transactions
    WHERE txn_date >= '2019-01-01'
    GROUP BY account_id
)
SELECT
    a.name,
    mt.total,
    mt.total / SUM(mt.total) OVER () AS share
FROM accounts a
JOIN monthly_totals mt ON a.id = mt.account_id;

CTE monthly_totals используется в JOIN и в оконной функции. При материализации агрегация считается один раз. После inline оптимизатор раскрыл CTE, и агрегация посчиталась дважды - по разу для каждого использования. EXPLAIN ANALYZE честно это показал: два узла HashAggregate на один и тот же набор данных.

Фикс очевидный:

WITH monthly_totals AS MATERIALIZED (
    SELECT account_id, SUM(amount) AS total
    FROM transactions
    WHERE txn_date >= '2019-01-01'
    GROUP BY account_id
)

Один MATERIALIZED - и поведение вернулось к версии 11-й, только теперь это явно задокументировано в самом запросе. Что, в общем, честнее: раньше материализация была неявной особенностью, о которой надо было знать.

Второй случай: CTE как барьер оптимизатора с UNION ALL. Это был хитрый запрос, написанный когда-то специально так, чтобы CTE выступал барьером. Внутри - UNION ALL из нескольких подзапросов, причём порядок строк из разных ветвей имел значение для логики, которая была в функции с побочными эффектами. После inline оптимизатор переставил порядок выполнения - результат стал другим.

Это именно тот случай, о котором PostgreSQL-разработчики предупреждают: если в CTE есть функции с побочными эффектами, inline может сломать семантику. MATERIALIZED решил проблему. Заодно этот запрос поставили в очередь на переписывание - архитектурно он был кривоватым.

Итог по нагрузочному тесту

Из семи запросов с CTE: пять ускорились, два деградировали, оба починились через MATERIALIZED. Остальные запросы без CTE - никаких сюрпризов, в пределах погрешности.

Вывод, с которым уходим на ожидание GA: аудит CTE-запросов перед апгрейдом обязателен. Не потому что "всё сломается" - большинство запросов только выиграют. Но те несколько, которые молча изменят поведение, найти надо до, а не после. EXPLAIN ANALYZE на обеих версиях рядом - это несколько часов работы, которые потом не превращаются в ночной инцидент.

Когда выйдет GA - будем принимать решение про апгрейд. Аудит к тому моменту закрыт.