Дополнительный материал к статье Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-4 «Временная таблица»
Предупреждение: статья подготовлена с использованием ответов нейросети DeepSeek.
Входные данные для вопроса нейросети
Хранимая функция-1 (LEFT JOIN)
-- scenario1.sql
-- JOIN
/*
Пример запроса с использованием нескольких LEFT OUTER JOIN,
который показывает информацию о бронированиях, билетах, перелётах и рейсах,
включая случаи, когда данные могут отсутствовать
*/
CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$
DECLARE
test_rec record ;
BEGIN
SET application_name = 'scenario1';
-- Оптимизированный запрос
WITH random_period AS (
SELECT
start_date,
start_date + INTERVAL '30 days' AS end_date
FROM (
SELECT
book_date AS start_date
FROM bookings
WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
ORDER BY RANDOM()
LIMIT 1
) AS random_date
)
SELECT
b.book_ref,
b.book_date,
t.ticket_no,
t.passenger_name,
s.flight_id,
f.status,
f.scheduled_departure,
f.actual_departure,
bp.seat_no,
bp.boarding_no,
rp.start_date AS period_start,
rp.end_date AS period_end
INTO test_rec
FROM random_period rp
CROSS JOIN LATERAL (
SELECT *
FROM bookings b
WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
) b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON
s.ticket_no = bp.ticket_no
AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no;
return 0 ;
END
$$ LANGUAGE plpgsql;Хранимая функция-1.1 (Temp table)
-- scenario1.1.sql
-- TEMP TABLES
CREATE OR REPLACE FUNCTION scenario1.1()
RETURNS integer AS $$
DECLARE
test_rec record;
original_app_name text;
temp_table_name text;
BEGIN
-- Сохраняем оригинальное application_name
original_app_name := current_setting('application_name');
-- Генерируем уникальное имя временной таблицы
temp_table_name := 'temp_filtered_bookings_' || pg_backend_pid();
-- Устанавливаем уникальное имя приложения для сессии
PERFORM set_config('application_name', 'scenario1_' || pg_backend_pid(), false);
-- Создаем временную таблицу с уникальным именем
EXECUTE format('
CREATE TEMP TABLE %I ON COMMIT DROP AS
WITH random_period AS (
SELECT
book_date AS start_date,
book_date + INTERVAL ''30 days'' AS end_date
FROM bookings
ORDER BY RANDOM()
LIMIT 1
)
SELECT b.*
FROM bookings b
CROSS JOIN random_period rp
WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
', temp_table_name);
-- Создаем индексы
EXECUTE format('CREATE INDEX ON %I (book_ref)', temp_table_name);
EXECUTE format('CREATE INDEX ON %I (book_date)', temp_table_name);
-- Выполняем запрос (пример для одной строки)
EXECUTE format('
SELECT
b.book_ref,
b.book_date,
t.ticket_no,
t.passenger_name,
s.flight_id,
f.status,
f.scheduled_departure,
f.actual_departure,
bp.seat_no,
bp.boarding_no
FROM %I b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no
AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no
LIMIT 1
', temp_table_name)
INTO STRICT test_rec;
-- Восстанавливаем оригинальное application_name
PERFORM set_config('application_name', original_app_name, false);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
-- Восстанавливаем application_name в случае ошибки
PERFORM set_config('application_name', original_app_name, false);
RAISE;
END;
$$ LANGUAGE plpgsql;1-й вопрос нейросети
Cделай прогноз - какая хранимая функция покажет большую производительность при нагрузочном тестировании с количеством параллельных соединений от 5 до 22 и ресурсах CPU=8 RAM=8GB ?
Ответ нейросети
Прогноз: функция scenario1 будет показывать большую производительность при нагрузочном тестировании с 5–22 параллельными соединениями на ресурсах CPU=8, RAM=8GB.
Обоснование:
Накладные расходы на временные таблицы:
scenario1.1 создаёт временную таблицу, индексы и удаляет их для каждого вызова. Это требует дополнительных операций ввода-вывода, памяти и процессорного времени.
При 22 параллельных соединениях одновременное создание/удаление временных таблиц может вызвать конкуренцию за ресурсы (блокировки, фрагментация памяти).
Параллельное выполнение:
scenario1 использует только CTE и LATERAL JOIN, что позволяет планировщику PostgreSQL более гибко параллелизировать запрос.
Временные таблицы в scenario1.1 могут препятствовать эффективному параллельному выполнению, особенно при высокой нагрузке.
Использование памяти:
Каждая временная таблица потребляет память. При 22 соединениях это может привести к исчерпанию доступной оперативной памяти (8 ГБ) или увеличению свопинга.
scenario1 работает в рамках одного запроса без материализации промежуточных данных, что более экономично.
Планировщик запросов:
PostgreSQL лучше оптимизирует одиночные сложные запросы с CTE, чем цепочки операций с временными таблицами.
Индексы во временных таблицах могут помочь, но их создание «на лету» съедает выигрыш.
Сетевые издержки:
Обе функции возвращают только 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. Нет конкуренции с другими транзакциями
Ключевое понимание:
Прогноз основывался на теоретических накладных расходах создания временных таблиц. Но на практике:
Динамические запросы с параметрами страдают больше, чем статические с временными таблицами
Планировщик PostgreSQL лучше оптимизирует простые запросы к маленьким таблицам
Изоляция данных важнее накладных расходов при высокой параллельной нагрузке
Вывод:
Прогноз не учел:
Параметрическую нестабильность планов запросов
Преимущества изоляции данных при параллельной нагрузке
Размерность данных — временные таблицы выигрывают на больших таблицах с маленькой выборкой
Эффект "теплого кэша" — временная таблица полностью находится в памяти