Найти в Дзене
Postgres DBA
62 подписчика

⬇️5️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-5 "Изменение WHERE"👎

3
7 мин
Не верь на слово, проверь под давлением. Методология исследования Тестовая среда и инструменты: СУБД: PostgreSQL 17 Инструмент нагрузочного тестирования: pg_expecto Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема) Условия тестирования: параллельная нагрузка, ресурсоемкий запрос Нагрузка на СУБД 1️⃣Эксперимент-1 : Оптимизация с использованием EXISTS 2️⃣Эксперимент-2 : Оптимизация структуры запроса 3️⃣Эксперимент-3 : Частичная агрегация 4️⃣Эксперимент-4 : Временная таблица 5️⃣Тестовый запрос - WHERE План выполнения тестового запроса -WHERE Сравнительный анализ планов выполнения запросов ❓Проведи сравнительный анализ и сформируй итог по планам выполнения тестовых запросов Основные различия между TEST-1 и TEST-6: Ключевые наблюдения: TEST-1 более эффективен для фильтрации: Использует CROSS JOIN LATERAL для раннего ограничения данных Применяет индекс idx_bookings_book_date для быстрого доступа к бронированиям в периоде Обрабатывает только релевантные да
Оглавление
Не верь на слово, проверь под давлением.
Не верь на слово, проверь под давлением.

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

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

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

-2

1️⃣Эксперимент-1 : Оптимизация с использованием EXISTS

1️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-1 "EXISTS"
Postgres DBA29 ноября

2️⃣Эксперимент-2 : Оптимизация структуры запроса

2️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-2 "Оптимизация структуры запроса"
Postgres DBA30 ноября

3️⃣Эксперимент-3 : Частичная агрегация

3️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-3 "Частичная агрегация"
Postgres DBA30 ноября

4️⃣Эксперимент-4 : Временная таблица

4️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-4 "Временная таблица"
Postgres DBA6 декабря

5️⃣Тестовый запрос - WHERE

Тестовый запрос - WHERE 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…
Postgres DBA2 декабря

План выполнения тестового запроса -WHERE

План выполнения тестового запроса -WHERE  Sort (cost=1445335.77..1445902.80 rows=226814 width=77) (actual time=26918.538..27291.773 rows=0 loops=1)   Sort Key: b.book_date, t.ticket_no   Sort Method: quicksort Memory: 25kB   CTE random_period    -> Limit (cost=191879.84..191879.84 rows=1 width=24) (actual time=4017.386..4017.390 rows=1 loops=1)    -> Sort (cost=191879.84..209662.82 rows…
Postgres DBA2 декабря

Сравнительный анализ планов выполнения запросов

❓Проведи сравнительный анализ и сформируй итог по планам выполнения тестовых запросов

Основные различия между TEST-1 и TEST-6:

-3

Ключевые наблюдения:

  1. TEST-1 более эффективен для фильтрации:
  2. Использует CROSS JOIN LATERAL для раннего ограничения данных
  3. Применяет индекс idx_bookings_book_date для быстрого доступа к бронированиям в периоде
  4. Обрабатывает только релевантные данные (397,632 строки вместо 7,113,192)
  5. TEST-6 имеет проблемы с производительностью:
  6. Случайный период выбирается через сортировку всей таблицы bookings (дорогая операция)
  7. Фильтрация в WHERE происходит после всех соединений, что создает промежуточные большие наборы данных
  8. Параллельное выполнение помогает, но не компенсирует неоптимальный план

Итог:

  • TEST-1 демонстрирует более качественный план выполнения с лучшим использованием индексов и поэтапной фильтрацией данных
  • TEST-6 страдает от неоптимального выбора случайного периода и поздней фильтрации, несмотря на использование параллельного выполнения
  • Оба запроса выполняются достаточно долго (>27 сек), что указывает на необходимость дополнительной оптимизации для рабочих нагрузок

Вывод: TEST-1 представляет более сбалансированный и эффективный подход, в то время как TEST-6 требует существенной доработки, особенно в части выбора случайного периода и фильтрации данных.

Сравнительный анализ производительности и ожиданий СУБД в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"

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

График изменения операционной скорости в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "WHERE"
График изменения операционной скорости в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "WHERE"
График изменения относительной разницы операционной скорости для запроса "WHERE" по сравнению с тестовым запросом "JOIN"
График изменения относительной разницы операционной скорости для запроса "WHERE" по сравнению с тестовым запросом "JOIN"

Результат: изменение условия WHERE приводит к деградации производительности после 15 соединений.

Ожидания СУБД

График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "WHERE"
График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "WHERE"
График изменения относительной разницы ожиданий СУБД для запроса "WHERE" по сравнению с тестовым запросом "JOIN"
График изменения относительной разницы ожиданий СУБД для запроса "WHERE" по сравнению с тестовым запросом "JOIN"
❓Проведи сравнительный анализ и сформируй итог по показаниям wait_event_type для тестовых запросов

Анализ по типам событий:

1. Ввод/вывод (IO)

  • TEST-1: Крайне высокие показатели (16,397) - в 2 раза больше
  • TEST-6: Умеренные показатели (7,928)
  • Интерпретация: TEST-1 выполняет значительно больше операций чтения/записи с диска, что согласуется с использованием внешней сортировки (external merge) на диске

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

  • TEST-1: Чрезвычайно высокие значения (25,208) - почти в 6 раз больше
  • TEST-6: Относительно низкие значения (4,329)
  • Интерпретация: TEST-1 активно использует разделяемую память и механизмы IPC, вероятно из-за сложных хеш-соединений и сортировок

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

  • TEST-1: Умеренные значения (134)
  • TEST-6: Высокие значения (271) - в 2 раза больше
  • Интерпретация: TEST-6 испытывает больше contention на блокировках, что характерно для параллельных запросов с несколькими воркерами

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

  • TEST-1: Низкие значения (21)
  • TEST-6: Повышенные значения (36)
  • Интерпретация: TEST-6 чаще сталкивается с ожиданием ресурсов, что также коррелирует с параллельным выполнением

Динамика событий во времени:

TEST-1 "LEFT JOIN":

  • Ранняя фаза: Умеренный рост всех показателей
  • Средняя фаза: Быстрый рост IO и IPC (2,000-10,000)
  • Пиковая фаза: Экстремальные значения IO и IPC (>16,000 и >25,000)
  • Характер: Постепенное накопление событий с резким ростом в середине выполнения

TEST-6 "WHERE":

  • Ранняя фаза: Низкие стабильные значения
  • Средняя фаза: Медленный рост IO, умеренный рост LWLOCK
  • Пиковая фаза: Значения остаются относительно умеренными
  • Характер: Более равномерное распределение событий во времени

Корреляция с планами выполнения:

  1. TEST-1 согласуется с планом:
  2. Высокие IO соответствуют external merge sort на диске (121,832 kB)
  3. Высокие IPC соответствуют сложным хеш-соединениям и сортировкам
  4. Умеренные LWLOCK/TIMEOUT соответствуют последовательному выполнению
  5. TEST-6 согласуется с планом:
  6. Относительно низкие IO соответствуют работе в памяти и параллельному доступу
  7. Высокие LWLOCK соответствуют координации между параллельными воркерами (4 workers)
  8. Повышенные TIMEOUT соответствуют ожиданию воркеров друг друга

Ключевые выводы:

  1. Профиль нагрузки:
  2. TEST-1: IO- и IPC-интенсивный, тяжелые операции с данными
  3. TEST-6: LWLOCK-интенсивный, проблемы с параллельной координацией
  4. Масштаб проблем:
  5. TEST-1 имеет абсолютно более высокую нагрузку на систему (в 2-6 раз по IO/IPC)
  6. TEST-6 имеет относительно более высокую нагрузку на блокировки
  7. Влияние на общее время:
  8. Несмотря на меньшие значения wait events, TEST-6 выполняется почти в 2 раза быстрее (27.29с vs 59.15с)
  9. Это указывает, что события ожидания в TEST-1 являются более "дорогими" по времени

Итог:

TEST-1 создает значительно более высокую системную нагрузку с точки зрения операций ввода/вывода и межпроцессного взаимодействия, что объясняется его сложным планом выполнения с несколькими этапами сортировки и соединения больших наборов данных.

TEST-6, хотя и выполняется быстрее, страдает от проблем параллельной координации, что проявляется в повышенном количестве легковесных блокировок и таймаутов.

Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "WHERE"

❓Проведи сравнительный анализ и сформируй итог по показаниям vmstat для тестовых запросов в ходе нагрузочного тестирования

Ключевые различия в системной нагрузке:

1. Процессорная нагрузка (CPU)

  • TEST-1: Умеренная CPU-нагрузка (45% user + 4.5% system = ~50%)
  • TEST-6: Очень высокая CPU-нагрузка (72% user + 11.6% system = ~84%)
  • Разница: TEST-6 нагружает CPU в 1.7 раза сильнее

2. Ввод/вывод (I/O)

  • TEST-1: Умеренный ввод (77K блоков/сек), низкий вывод (4.9K блоков/сек)
  • TEST-6: Экстремальный ввод (199K блоков/сек), экстремальный вывод (226K блоков/сек)
  • Разница: TEST-6 имеет в 2.6 раза больше операций ввода и в 46 раз больше операций вывода

3. Ожидание I/O (cpu_wa)

  • TEST-1: Высокое время ожидания I/O (12.7%) - система часто ждет диска
  • TEST-6: Низкое время ожидания I/O (2.4%) - диск успевает за запросами
  • Разница: TEST-1 в 5.3 раза больше времени тратит на ожидание I/O

4. Использование памяти

  • TEST-1: Меньше свободной памяти (180 КБ), больше кэша (6.9 МБ)
  • TEST-6: Больше свободной памяти (313 КБ), меньше кэша (6.4 МБ)
  • Интерпретация: TEST-1 активно использует кэш для данных, TEST-6 работает с буферами

5. Системные события

  • TEST-1: Высокие прерывания (12K/сек) и очень высокие переключения контекста (10.8K/сек)
  • TEST-6: Умеренные прерывания (8.4K/сек) и низкие переключения контекста (2.6K/сек)
  • Разница: TEST-1 создает в 4 раза больше переключений контекста

Динамика во времени:

TEST-1 "LEFT JOIN":

  • CPU: Начинается с 47% idle, постепенно снижается до 15-20% idle
  • I/O wait: Стабильно высокий (10-17%), пики до 17%
  • I/O активность: Умеренная, с постепенным ростом
  • Память: Стабильное использование кэша, низкая свободная память

TEST-6 "WHERE":

  • CPU: Очень высокая нагрузка с начала (34% idle снижается до 1-6% idle)
  • I/O wait: Стабильно низкий (1-3%)
  • I/O активность: Экстремально высокая с самого начала
  • Память: Больше свободной памяти, активное использование буферов

Корреляция с планами выполнения и wait events:

  1. TEST-1 подтверждает анализ плана:
  2. Высокий cpu_wa (12.7%) соответствует external merge sort на диске
  3. Умеренный I/O соответствует последовательному доступу к данным
  4. Высокие переключения контекста соответствуют сложной обработке в одном процессе
  5. TEST-6 подтверждает анализ плана:
  6. Высокий cpu_us (72%) соответствует параллельному выполнению и обработке в памяти
  7. Низкий cpu_wa (2.4%) подтверждает эффективный параллельный доступ к диску
  8. Экстремальный I/O соответствует параллельному сканированию больших таблиц

Ключевые выводы:

Профиль нагрузки:

  • TEST-1: I/O-bound запрос с умеренной CPU-нагрузкой и высокой задержкой на дисковых операциях
  • TEST-6: CPU-bound запрос с экстремальной CPU-нагрузкой и очень интенсивным I/O

Эффективность использования ресурсов:

  • TEST-1: Низкая эффективность - много времени тратится в ожидании I/O
  • TEST-6: Высокая эффективность CPU, но чрезмерная нагрузка на подсистему ввода/вывода

Влияние на систему:

  • TEST-1: Создает меньше общей нагрузки на CPU, но вызывает проблемы с отзывчивостью из-за высокого I/O wait
  • TEST-6: Исчерпывает CPU-ресурсы, но минимизирует время ожидания диска

Итог:

TEST-1 страдает от проблем с производительностью диска - высокое время ожидания I/O (12.7%) является основным ограничивающим фактором. Несмотря на меньшую общую CPU-нагрузку, запрос выполняется почти в 2 раза дольше из-за дисковых задержек.

TEST-6 демонстрирует экстремальную CPU-нагрузку (84% суммарно) и очень интенсивные операции ввода/вывода, но при этом минимизирует время ожидания диска. Это более современный профиль нагрузки, характерный для параллельных запросов.

Общий итог : Часть-5 "WHERE"

ℹ️ Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - изменение условий JOIN - неприемлемо.

Производительность СУБД:

Производительность полностью деградирует при нагрузке выше 15 соединений.

Продолжение цикла "Сравнительный анализ методов оптимизации ресурсоёмкого SQL-запроса под конкурентной нагрузкой с использованием pg_expecto":

6️⃣Итог: Сводный анализ