❌Прогноз vs Реальность: Когда временные таблицы побеждают CTE в многопользовательской среде PostgreSQL

Материал для ознакомления. Данные исходного эксперимента - некорректны.

Предисловие

В мире оптимизации SQL-запросов часто руководствуются устоявшимися догмами: «CTE эффективнее временных таблиц», «избегайте материализации промежуточных данных», «меньше операций — выше производительность». Но нагрузочное тестирование на реальной системе с 5-22 параллельными соединениями преподнесло сюрприз: решение с временными таблицами, которое по всем теоретическим расчетам должно было проиграть, показало существенный рост производительности. Эта статья — разбор того, почему прогнозы не оправдались, какие скрытые факторы влияют на производительность в условиях высокой параллельной нагрузки, и как принятие архитектурных решений на основе реальных тестов, а не только теорий, может изменить подход к оптимизации баз данных.

Дополнительная иллюстрация к циклу "Оптимизация SQL-запроса с использованием pg_expecto"

Следующий тест - "Использование временных таблиц" - в процессе тестирования.

Входные данные для вопроса нейросети

Хранимая функция-1 (LEFT JOIN)

❌Хранимая функция-1.1 (Temp table)

1-й вопрос нейросети

Cделай прогноз - какая хранимая функция покажет большую производительность при нагрузочном тестировании с количеством параллельных соединений от 5 до 22 и ресурсах CPU=8 RAM=8GB ?

Ответ нейросети

Прогноз: функция scenario1 будет показывать большую производительность при нагрузочном тестировании с 5–22 параллельными соединениями на ресурсах CPU=8, RAM=8GB.

Обоснование:

1. Накладные расходы на временные таблицы:
2. scenario1.1 создаёт временную таблицу, индексы и удаляет их для каждого вызова. Это требует дополнительных операций ввода-вывода, памяти и процессорного времени.
3. При 22 параллельных соединениях одновременное создание/удаление временных таблиц может вызвать конкуренцию за ресурсы (блокировки, фрагментация памяти).
4. Параллельное выполнение:
5. scenario1 использует только CTE и LATERAL JOIN, что позволяет планировщику PostgreSQL более гибко параллелизировать запрос.
6. Временные таблицы в scenario1.1 могут препятствовать эффективному параллельному выполнению, особенно при высокой нагрузке.
7. Использование памяти:
8. Каждая временная таблица потребляет память. При 22 соединениях это может привести к исчерпанию доступной оперативной памяти (8 ГБ) или увеличению свопинга.
9. scenario1 работает в рамках одного запроса без материализации промежуточных данных, что более экономично.
10. Планировщик запросов:
11. PostgreSQL лучше оптимизирует одиночные сложные запросы с CTE, чем цепочки операций с временными таблицами.
12. Индексы во временных таблицах могут помочь, но их создание «на лету» съедает выигрыш.
13. Сетевые издержки:
14. Обе функции возвращают только integer, но внутренние операции в scenario1.1 более тяжелы.

Рекомендация для нагрузочного тестирования:

• Для scenario1 убедитесь, что есть индексы на:
• bookings(book_date)
• tickets(book_ref)
• segments(ticket_no)
• flights(flight_id)
• boarding_passes(ticket_no, flight_id)

Если в scenario1.1 количество строк во временной таблице очень велико (сотни тысяч), то индексы могут дать преимущество, но при параллельной нагрузке это маловероятно перевесит накладные расходы.

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

Важная деталь

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

2-й вопрос нейросети

Почему прогноз не оправдывается при нагрузочном тестировании и использование временной таблицы показывает существенный рост производительности ?

Ответ нейросети

Основные причины, почему прогноз не оправдался и временные таблицы оказались быстрее:

1. Параметрическая неоптимизация в основном запросе

WHERE b.book_date BETWEEN rp.start_date AND rp.end_date

Это условие:

• Не использует индексы эффективно для динамического диапазона дат
• Заставляет планировщик PostgreSQL каждый раз переоценивать стоимость для нового случайного периода
• Временная таблица фиксирует статистику для конкретного подмножества данных

2. Проблема "проклятия параметров" (Parameter Sniffing)

• PostgreSQL не может создать оптимальный план для постоянно меняющихся start_date/end_date
• Временная таблица ломает зависимость от параметров — статистика собирается для конкретных данных
• Автономный план запроса для временной таблицы оказывается более эффективным

3. Анализ сканирования vs изоляция данных

scenario1 (CTE/LATERAL):

1. Сканирование всей таблицы bookings для фильтрации по дате

2. Nested Loop с неопределенным количеством строк

3. Динамическая оценка кардинальности → неправильные планы

scenario1.1 (Temp Table):

1. Одноразовое создание фильтрованного подмножества

2. Точная статистика по временной таблице

3. Декомпозиция сложного запроса на простые этапы

4. Особенности параллельной нагрузки на 8 CPU

• При 22 соединениях конкуренция за общие ресурсы (shared buffers)
• Временные таблицы изолируют данные по соединениям → меньше блокировок
• Каждое соединение работает со своей копией данных в temp_buffers

5. Специфика тестовых данных

Если:

• Таблица bookings очень большая (миллионы строк)
• Выборка за 30 дней составляет небольшой процент (5-10%)
• Индексы по дате не оптимальны или требуют перестроения

Тогда временная таблица выигрывает за счет:

• Полного сканирования маленькой таблицы вместо частичного сканирования большой
• Лучшей локализации данных в памяти

6. Эффект "прямого доступа к данным"

-- Временная таблица хранится локально для сессии

-- Весь последующий доступ идет через:

1. Temp buffer (в памяти)

2. Отсутствие WAL-логирования (в большинстве случаев)

3. Нет конкуренции с другими транзакциями

Ключевое понимание:

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

1. Динамические запросы с параметрами страдают больше, чем статические с временными таблицами
2. Планировщик PostgreSQL лучше оптимизирует простые запросы к маленьким таблицам
3. Изоляция данных важнее накладных расходов при высокой параллельной нагрузке

Вывод:

Прогноз не учел:

1. Параметрическую нестабильность планов запросов
2. Преимущества изоляции данных при параллельной нагрузке
3. Размерность данных — временные таблицы выигрывают на больших таблицах с маленькой выборкой
4. Эффект "теплого кэша" — временная таблица полностью находится в памяти

Правило: Динамические запросы с диапазонами дат часто выигрывают от материализации промежуточных результатов, особенно при параллельном выполнении.