ИТОГ - Оптимизация SQL-запроса с использованием pg_expecto

Не верь на слово, проверь под давлением.

Предисловие

Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?

Бизнес задача

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

• Неполных бронирований
• Билетов без привязки к рейсам
• Рейсов без процедуры посадки
• Статистики по незавершённым операциям

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

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

• СУБД: PostgreSQL 17
• Инструмент нагрузочного тестирования: pg_expecto
• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
• Условия тестирования: параллельная нагрузка, ресурсоемкий запрос

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

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

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

График изменения операционной скорости в ходе нагрузочного тестирования

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

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

❓Проанализируй тексты запросов и планы выполнения запросов. Выдели ключевые особенности и принципиальные различия влияющие на производительность.

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

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
• Параллельное выполнение
• Дополнительные фильтры по статусу

Особенность: Возвращает 0 строк из-за фильтра по статусу

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

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, который особенно полезен при:

• Многократном использовании отфильтрованных данных
• Сложных цепочках преобразований
• Необходимости разделения логики запроса

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

❓Проанализируй данные производительности(чем выше значение тем выше производительность) и ожиданиям СУБД в ходе нагрузочного тестирования для разных тестовых запросов . Выдели ключевые особенности после точки измерения 81.

📊 Ключевые метрики производительности после точки 81

1. Производительность (SPEED) - выше значение = лучше

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

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

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

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

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

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

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

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

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

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

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

• Преимущества:
  Максимальная и стабильная скорость (32)
  Самый низкий IPC (101-110)
  Умеренные таймауты
• Недостатки:
  Высокие LWLOCK (до 227)
  Умеренный рост IO

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

• Преимущества:
  Стабильная производительность
  Минимальные LWLOCK (19)
  Самые низкие таймауты (3)
• Недостатки:
  Очень высокий IO (до 44,556)
  Умеренный рост IPC

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

• Особенности:
  Экстремальный рост LWLOCK (311)
  Стабильный IPC (5870)
  Умеренные таймауты (8)
• Вывод: Подходит для изолированных операций, но создает высокую нагрузку на блокировки

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

• Общие проблемы:
  Очень высокий IPC (24107 и 21728)
  Умеренная производительность (14-16)
  Значительные таймауты (19 и 14)
• Вывод: Неэффективное межпроцессное взаимодействие снижает производительность

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

• Критические проблемы:
  Операционная скорость падает с 16 до 0
  Таймауты взрывные (30)
  Высокие LWLOCK (247)
• Вывод: Непригоден для нагрузочного тестирования

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

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

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

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

1. Мониторинг IPC для LEFT JOIN и EXISTS
2. Контроль LWLOCK для TEMP TABLE и ARRAY
3. Избегайте WHERE подхода в высоконагруженных системах

Баланс производительности и стабильности:

Итог:

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

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

❓Проанализируй показатели метрик vmstat в ходе нагрузочного тестирования для разных тестовых запросов . Выдели ключевые особенности по метрикам после точки измерения 81.

1. "LEFT JOIN" (точки 81–110)

• Процессы: 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" (точки 81–110)

• Процессы: 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" (точки 81–110)

• Процессы: 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" (точки 81–110)

• Процессы: 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" (точки 81–110)

• Процессы: 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" (точки 81–110)

• Процессы: 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 низкий.

Сводная таблица особенностей после точки 81:

Выводы:

• "ARRAY" и "WHERE" демонстрируют процессорную нагрузку с минимальными задержками на I/O.
• "TUNNING" явно страдает от медленного I/O, что видно по высокому wa и заблокированным процессам.
• "EXISTS" и "LEFT JOIN" показывают сбалансированную нагрузку с умеренным использованием CPU и диска.
• "TEMP TABLE" эффективно использует временные структуры, снижая нагрузку на диск к концу теста.