Как сравнить производительность двух эквивалентных по результату запросов (с подзапросом и с CTE) и аргументированно выбрать один для продакшена?

Короткий ответ

Нужно прогнать оба варианта через EXPLAIN ANALYZE на представительном объёме данных (близком к продакшену, а не на маленькой тестовой выборке) и сравнить фактическое время выполнения и объём обработанных данных, а не полагаться на общие утверждения вроде «CTE всегда медленнее подзапроса».

Как это работает подробнее

в современных версиях PostgreSQL (начиная с 12) простой, не рекурсивный CTE по умолчанию может быть «встроен» (inlined) оптимизатором в основной запрос точно так же, как подзапрос — то есть различие в производительности, характерное для более старых версий, во многих случаях исчезло. Поведение может отличаться, если CTE явно помечен как материализуемый (AS MATERIALIZED) или используется многократно.

Пример кода

question-70.sqlКопировать

EXPLAIN ANALYZE
WITH high_value AS (SELECT * FROM orders WHERE amount > 1000)
SELECT customer_id, COUNT(*) FROM high_value GROUP BY customer_id;
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*)
FROM (SELECT * FROM orders WHERE amount > 1000) t
GROUP BY customer_id;

Что использовать на практике

• Практика. универсального правила «что быстрее» нет — решение зависит от конкретной версии СУБД, объёма данных, наличия индексов и того, насколько сложен план для каждого варианта. Самый надёжный способ — измерить на реалистичных данных, а не делать вывод по теории или по результатам на маленькой тестовой таблице, где различия в плане могут быть не видны.
• Проверка. Проверка: сравнить строки Execution Time в обоих выводах на реальном объёме таблицы orders, а не на тестовых 100 строках.

Подводные камни

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