Итоги анализа вариантов оптимизации ресурсоёмкого SQL-запроса

Глоссарий терминов

Статистический анализ производительности СУБД PostgreSQL

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Задача

Исследовать и определить наиболее эффективный SQL-запрос, позволяющий получить информацию для анализа:

• Неполных бронирований

• Билетов без привязки к рейсам

• Рейсов без процедуры посадки

• Статистики по незавершённым операциям

Методология исследования

Тестовая среда и инструменты:

• Тестовая ВМ: CPU = 8 / RAM = 8GB

• Инструмент нагрузочного тестирования и статистического анализа производительности СУБД: pg_expecto

• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)

• Условия тестирования: параллельная нагрузка, ресурсоемкий запрос

• СУБД: PostgreSQL 17

track_io_timing = 'on'
listen_addresses = '0.0.0.0'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'postgresql-%u.log'
log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '
log_truncate_on_rotation = 'on'
log_checkpoints = 'on'
archive_mode = 'on'
archive_command = 'true'
archive_timeout = '30min'
checkpoint_timeout = '15min'
checkpoint_warning = '60'
checkpoint_completion_target = '0.9'
min_wal_size = '2GB'
max_wal_size = '8GB'
synchronous_commit = 'on'
wal_compression = 'on'
random_page_cost = '1.1'
effective_io_concurrency = '300'
wal_sender_timeout = '0'
autovacuum_naptime = '1s'
autovacuum_vacuum_scale_factor = '0.01'
autovacuum_analyze_scale_factor = '0.005'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_max_workers = '4'
autovacuum_work_mem = '256MB'
vacuum_cost_limit = '4000'
bgwriter_delay = '10ms'
bgwriter_lru_multiplier = '4'
bgwriter_lru_maxpages = '400'
max_locks_per_transaction = '256'
max_pred_locks_per_transaction = '256'
shared_buffers = '1919MB'
effective_cache_size = '5757MB'
work_mem = '14MB'
temp_buffers = '14MB'
maintenance_work_mem = '479MB'
max_worker_processes = '8'
max_parallel_workers = '8'
max_parallel_workers_per_gather = '4'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
max_parallel_maintenance_workers = '4'
hash_mem_multiplier = '2'
autovacuum_vacuum_insert_scale_factor = '0.01'
shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling'
commit_delay = '1000'
log_autovacuum_min_duration = '0'
wipe_file_on_delete = 'on'
wipe_heaptuple_on_delete = 'on'
wipe_mem_on_free = 'on'
wipe_memctx_on_free = 'on'
wipe_xlog_on_free = 'on'
log_connections = 'on'
log_disconnections = 'on'
pg_stat_statements.track = 'all'
max_connections = '1000'

План нагрузочного тестирования (param.conf)

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
finish_load = 20
# Тестовая БД
testdb = demo
# Веса сценариев
scenario1 = 1.0

Нагрузка на СУБД

Варианты SQL-запроса

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
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;

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.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 bookings 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
WHERE EXISTS (
    SELECT 1 FROM random_period rp 
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
)
ORDER BY b.book_date, t.ticket_no;

WITH random_period AS (
    SELECT 
        book_date AS start_date,
        book_date + INTERVAL '30 days' AS end_date
    FROM bookings 
    WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
    ORDER BY RANDOM() 
    LIMIT 1
),
filtered_bookings AS (
    SELECT book_ref, book_date
    FROM bookings
    WHERE book_date BETWEEN (SELECT start_date FROM random_period) 
                        AND (SELECT end_date FROM random_period)
)
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 filtered_bookings b
LEFT JOIN LATERAL (
    SELECT ticket_no, passenger_name
    FROM tickets 
    WHERE book_ref = b.book_ref
) t ON true
LEFT JOIN LATERAL (
    SELECT flight_id, fare_conditions, price
    FROM segments 
    WHERE ticket_no = t.ticket_no
) s ON true
LEFT JOIN LATERAL (
    SELECT status, scheduled_departure, actual_departure, route_no
    FROM flights 
    WHERE flight_id = s.flight_id
) f ON true
LEFT JOIN LATERAL (
    SELECT seat_no, boarding_no
    FROM boarding_passes 
    WHERE ticket_no = t.ticket_no AND flight_id = s.flight_id
) bp ON true
ORDER BY b.book_date, t.ticket_no;

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
),
booking_summary AS (
    SELECT 
        b.book_ref,
        b.book_date,
        COUNT(t.ticket_no) as ticket_count,
        ARRAY_AGG(t.ticket_no) as ticket_numbers
    FROM bookings b
    LEFT JOIN tickets t ON b.book_ref = t.book_ref
    WHERE b.book_date BETWEEN (SELECT start_date FROM random_period) 
                        AND (SELECT end_date FROM random_period)
    GROUP BY b.book_ref, b.book_date
)
SELECT 
    bs.book_ref,
    bs.book_date,
    bs.ticket_count,
    tn.ticket_no,  -- Используем развернутое значение
    t.passenger_name,
    s.flight_id,
    f.status
FROM booking_summary bs
CROSS JOIN UNNEST(bs.ticket_numbers) AS tn(ticket_no)  -- Разворачиваем массив здесь
LEFT JOIN tickets t ON t.ticket_no = tn.ticket_no      -- Теперь соединяем по одиночному значению
LEFT JOIN segments s ON s.ticket_no = t.ticket_no
LEFT JOIN flights f ON f.flight_id = s.flight_id
ORDER BY bs.book_date, tn.ticket_no;  -- Сортируем по развернутому значению

-- Создаем временную таблицу для отфильтрованных бронирований
CREATE TEMPORARY TABLE temp_filtered_bookings 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;

-- Создаем индексы на временной таблице
CREATE INDEX tmp_idx_book_ref ON temp_filtered_bookings (book_ref);
CREATE INDEX tmp_idx_book_date ON temp_filtered_bookings (book_date);

--TEST-5 "TEMP TABLES"
EXPLAIN (ANALYZE , SUMMARY , COSTS)
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 temp_filtered_bookings 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;

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.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 bookings b
CROSS JOIN random_period rp
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
WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
  AND (f.status IS NULL OR f.status IN ('Scheduled', 'On Time', 'Departed')) -- Фильтр по статусу
ORDER BY b.book_date, t.ticket_no;

Дополнение

Для варианта "TEMPORARY TABLE (Временная таблица)" - временная таблица temp_filtered_bookings пересоздается перед выполнением тес��ового запроса.

Эксперименты

Вариант-1: Использование EXISTS

Вариант-2: Оптимизация структуры запроса

Вариант-3: Частичная агрегация

Вариант-4: Использование временных таблиц

Вариант-5: Условие WHERE

Характерные особенности тестовых запросов и планов выполнения

1. LEFT JOIN

Особенности:

• Использует CROSS JOIN LATERAL для фильтрации

• Сложная структура с множеством вложенных соединений

• Большие объемы сортировки на диске (121 MB)

Проблемы:

• Многоуровневое вложение соединений

• Неоптимальная последовательность JOIN

• Высокий объем временных данных

2. EXISTS

Особенности:

• Использует подзапрос EXISTS для фильтрации

• Semi Join с фильтрацией строк (Rows Removed: 6,721,707)

• Последовательное сканирование всей таблицы bookings

Проблемы:

• Сканирование 7+ миллионов строк для фильтрации

• Высокая стоимость Join Filter

• Неэффективное использование индексов

3. TUNNING

Особенности:

• Использует CTE с предварительной фильтрацией

• LEFT JOIN LATERAL для последовательного соединения

• Параллельное выполнение (Gather Merge)

• Разделение на filtered_bookings

Преимущества:

• Ранняя фильтрация данных

• Оптимальная последовательность JOIN

• Использование параллелизма

• Наименьший объем дисковых операций (50 MB)

4. ARRAY

Особенности:

• Использует агрегацию ARRAY_AGG и UNNEST

• Мемоизация (кэширование) данных tickets

• Параллельная агрегация

Проблемы:

• Двойная работа с таблицей tickets

• Высокие промахи кэша (Misses: 821,542)

• Сложная структура с развертыванием массива

5. TEMP TABLE

Особенности:

• Создание временной таблицы с индексами

• Предварительная фильтрация во временной таблице

• Использование индексов на временной таблице

Преимущества:

• Повторное использование отфильтрованных данных

• Эффективные индексы для JOIN

• Упрощение основного запроса

Недостатки:

• Накладные расходы на создание таблицы

• Дополнительное управление транзакциями

6. WHERE

Особенности:

• Простая фильтрация в WHERE

• Параллельное выполнение

• Дополнительные фильтры по статусу

🎯 Принципиальные различия, влияющие на производительность

1. Стратегия фильтрации:

• Хорошо: Ранняя фильтрация (TUNNING, TEMP TABLE) - уменьшает объем обрабатываемых данных

• Плохо: Поздняя фильтрация (EXISTS, WHERE) - обрабатывает все строки перед фильтрацией

2. Использование индексов:

• Эффективно: Индексные сканы по book_date (TUNNING, LEFT JOIN)

• Неэффективно: Sequential Scan (EXISTS, ARRAY на полной таблице)

3. Параллелизм:

• Есть: TUNNING, ARRAY, WHERE - используют параллельное выполнение

• Нет: LEFT JOIN, EXISTS, TEMP TABLE - последовательное выполнение

4. Структура соединений:

• Оптимально: Последовательные LATERAL JOIN (TUNNING)

• Сложно: Многоуровневые вложенные JOIN (LEFT JOIN)

5. Управление памятью:

• Хорошо: External Merge с меньшим объемом (TUNNING - 50 MB)

• Плохо: Большие дисковые сортировки (LEFT JOIN - 121 MB)

💡 Ключевой вывод

Подход TUNNING демонстрирует наилучший баланс производительности и читаемости:

• Дисковые операции: 50 MB (на 60% меньше LEFT JOIN)

• Использование параллелизма: Gather Merge

• Четкая последовательность операций

Второй по эффективности - подход TEMP TABLE, который особенно полезен при:

• Многократном использовании отфильтрованных данных

• Сложных цепочках преобразований

• Необходимости разделения логики запроса

Нагрузка на СУБД

Производительность СУБД в ходе нагрузочного тестирования

Тестовый запрос "WHERE" - деградация производительности, при нагрузке 15 сессий и выше (точка наблюдения - 81)

Характерные особенности производительности СУБД, для нагрузки 15 соединений и выше

1. Производительность

Вывод: ARRAY и TUNNING демонстрируют наилучшую и стабильную производительность.

2. Ввод-вывод (IO) - нагрузка на систему

Вывод: TUNNING создает самую высокую нагрузку на ввод-вывод, но при этом сохраняет высокую производительность.

3. Межпроцессное взаимодействие (IPC)

Вывод: LEFT JOIN и EXISTS создают чрезмерную нагрузку на IPC, что указывает на неэффективное межпроцессное взаимодействие.

4. Легковесные блокировки (LWLOCK)

Вывод: TEMP TABLE вызывает наибольшее количество легковесных блокировок, что связано с управлением временными объектами.

5. Таймауты (TIMEOUT)

Вывод: WHERE показывает наихудшие показатели по таймаутам, что указывает на проблемы с выполнением запроса.

🎯 Ключевые особенности каждого подхода

1. ARRAY - ЛУЧШИЙ ПО ПРОИЗВОДИТЕЛЬНОСТИ

• Преимущества:

  • Максимальная и стабильная скорость

  • Самый низкий IPC

  • Умеренные таймауты

• Недостатки:

  • Высокие LWLOCK

  • Умеренный рост IO

2. TUNNING - ЛУЧШИЙ ПО СТАБИЛЬНОСТИ

• Преимущества:

  • Стабильная производительность

  • Минимальные LWLOCK

  • Самые низкие таймауты

• Недостатки:

  • Очень высокий IO

  • Умеренный рост IPC

3. TEMP TABLE - ВЫСОКАЯ ЦЕНА УПРАВЛЕНИЯ

• Особенности:

  • Экстремальный рост LWLOCK

  • Стабильный IPC

  • Умеренные таймауты

• Вывод: Подходит для изолированных операций, но создает высокую нагрузку на блокировки

4. LEFT JOIN & EXISTS - ПРОБЛЕМЫ С IPC

• Общие проблемы:

  • Очень высокий IPC

  • Умеренная производительность

  • Значительные таймауты

• Вывод: Неэффективное межпроцессное взаимодействие снижает производительность

5. WHERE - КАТАСТРОФИЧЕСКОЕ ПАДЕНИЕ

• Критические проблемы:

  • Операционная скорость падает до 0

  • Таймауты взрывные

  • Высокие LWLOCK

• Вывод: Непригоден для нагрузочного тестирования

📈 Итоговые рекомендации

Для максимальной производительности:

1. Используйте ARRAY подход - обеспечивает самую высокую скорость

2. Используйте TUNNING подход - обеспечивает максимальную стабильность

Для оптимизации инфраструктуры:

1. Мониторинг IPC: для LEFT JOIN и EXISTS

2. Контроль LWLOCK: для TEMP TABLE и ARRAY

3. Избегайте WHERE подхода в высоконагруженных системах

Итог: Подходы ARRAY и TUNNING демонстрируют наилучший баланс производительности и системных нагрузок, в то время как WHERE категорически не подходит для высоконагруженных систем.

Метрики производительности инфраструктуры для нагрузки 15 соединений и выше

1. "LEFT JOIN"

• Процессы: procs_r колеблется от 4 до 9, procs_b остаётся в основном на уровне 1–2.

• Память: swpd стабилен (~217), free ~179–180, buff ~10–27, cache ~6890–7069.

• Ввод/вывод: bi высокий (66000–73000), bo умеренный (3474–4205).

• Системные вызовы: in ~10449–13884, cs ~9511–13622.

• CPU: us ~35–56%, sy ~4–5%, id ~17–47%, wa ~11–17%.

• Особенности: Нагрузка на CPU (us) растёт, особенно к концу теста. wa остаётся умеренным, что указывает на приемлемую нагрузку на диск. Заметен рост системных вызовов (in, cs) к точкам 105–110.

2. "EXISTS" 

• Процессы: procs_r 7–9, procs_b 1–2.

• Память: swpd ~212–217, free ~180–181, buff ~7, cache ~6970–7137.

• Ввод/вывод: bi ~63848–77743, bo ~2276–4977.

• Системные вызовы: in ~10030–14105, cs ~9967–13579.

• CPU: us ~36–63%, sy ~4–5%, id ~15–46%, wa ~11–15%.

• Особенности: Нагрузка на CPU (us) возрастает до 63% к концу теста. wa снижается до 12–13% после точки 90, что может говорить об оптимизации операций ввода-вывода.

3. "TUNNING" 

• Процессы: procs_r 7–8, procs_b увеличивается до 3–7 (много заблокированных процессов).

• Память: swpd ~204, free ~179–185, buff ~105–147, cache ~6701–6951.

• Ввод/вывод: bi ~42442–73802, bo ~3800–11145.

• Системные вызовы: in ~7062–9575, cs ~3175–4974.

• CPU: us ~46–66%, sy ~3–5%, id ~3–36%, wa ~12–28%.

• Особенности: Очень высокий wa (до 28%) и низкий id (до 3%), что указывает на сильную нагрузку на ввод-вывод. Много заблокированных процессов (procs_b до 7) свидетельствует о проблемах с дисковой подсистемой.

4. "ARRAY" 

• Процессы: procs_r 10–15, procs_b 0–1.

• Память: swpd ~222–237, free ~174–296, buff ~3–124, cache ~5973–6613.

• Ввод/вывод: bi ~12455–34691, bo ~3569–22406.

• Системные вызовы: in ~6324–9509, cs ~1505–2568.

• CPU: us ~59–95%, sy ~2–4%, id ~0–36%, wa ~0–3%.

• Особенности: Очень высокий us (до 95%) при низком wa — нагрузка процессорная, а не дисковая. id падает до 0% в некоторых точках, что говорит о полной загрузке CPU.

5. "TEMP TABLE" 

• Процессы: procs_r 8–13, procs_b 1.

• Память: swpd ~226–243, free ~170–364, buff ~2–54, cache ~6099–6887.

• Ввод/вывод: bi ~57009–59628, bo ~8307–26681.

• Системные вызовы: in ~8748–11622, cs ~5720–7777.

• CPU: us ~42–80%, sy ~4–6%, id ~2–40%, wa ~7–16%.

• Особенности: Умеренные значения us и wa. После точки 90 wa снижается до 7–9%, а us растёт до 80%, что может указывать на эффективное использование временных таблиц.

6. "WHERE" 

• Процессы: procs_r 10–15, procs_b = 0 .

• Память: swpd ~212, free ~188–198, buff ~123–145, cache ~6189–6615.

• Ввод/вывод: bi ~146134–252075, bo ~190616–276018.

• Системные вызовы: in ~7306–9652, cs ~1664–3320.

• CPU: us ~52–80%, sy ~9–14%, id ~1–34%, wa ~1–3%.

• Особенности: Очень высокие bi и bo, но при этом низкий wa (1–3%), что может указывать на эффективное кэширование или быструю дисковая подсистему. Нагрузка на CPU (us) высокая, id низкий.

Сводная таблица по метрикам vmstat

Выводы:

• "ARRAY" и "WHERE" демонстрируют процессорную нагрузку с минимальными задержками на I/O.

• "TUNNING" явно страдает от медленного I/O, что видно по высокому wa и заблокированным процессам.

• "EXISTS" и "LEFT JOIN" показывают сбалансированную нагрузку с умеренным использованием CPU и диска.

• "TEMP TABLE" эффективно использует временные структуры, снижая нагрузку на диск к концу теста.

P.S. Следующий цикл публикация будет посвящен способам пагинации.