Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-2 «TUNING» / forpes.ru

Главная
Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-2 «TUNING»

Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-2 «TUNING»

30.11.2025 10:44

pg_expecto 0 1700 Источник

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

Предисловие

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

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

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

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

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

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

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

Тестовый запрос, используемый в качестве baseline : LEFT JOIN

Запрос демонстрирует:

LEFT JOIN bookings → tickets
Все бронирования, даже без привязанных билетов
LEFT JOIN tickets → segments
Все билеты, даже без перелётов
LEFT JOIN segments → flights
Все перелёты, даже без информации о рейсах
LEFT JOIN segments → boarding_passes
Все перелёты, даже без посадочных талонов
Выбирает случайную дату из существующих бронирований
Гарантирует, что период полностью входит в доступный диапазон дат

Такой запрос может быть полезен для анализа:

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

Таблицы, используемые в тестовом запросе

demo=# \d bookings
                    Unlogged table "bookings.bookings"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
 book_ref     | character(6)             |           | not null |
 book_date    | timestamp with time zone |           | not null |
 total_amount | numeric(10,2)            |           | not null |
Indexes:
    "bookings_pkey" PRIMARY KEY, btree (book_ref)
    "idx_bookings_book_date" btree (book_date)
Referenced by:
    TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

demo=# \d tickets
               Unlogged table "bookings.tickets"
     Column     |     Type     | Collation | Nullable | Default
----------------+--------------+-----------+----------+---------
 ticket_no      | text         |           | not null |
 book_ref       | character(6) |           | not null |
 passenger_id   | text         |           | not null |
 passenger_name | text         |           | not null |
 outbound       | boolean      |           | not null |
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (ticket_no)
    "idx_tickets_book_ref" btree (book_ref)
    "tickets_book_ref_passenger_id_outbound_key" UNIQUE CONSTRAINT, btree (book_ref, passenger_id, outbound)
Foreign-key constraints:
    "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Referenced by:
    TABLE "segments" CONSTRAINT "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

demo=#  \d segments
                Unlogged table "bookings.segments"
     Column      |     Type      | Collation | Nullable | Default
-----------------+---------------+-----------+----------+---------
 ticket_no       | text          |           | not null |
 flight_id       | integer       |           | not null |
 fare_conditions | text          |           | not null |
 price           | numeric(10,2) |           | not null |
Indexes:
    "segments_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
    "idx_segments_flight_id" btree (flight_id)
    "idx_segments_flight_id_fare_conditions" btree (flight_id, fare_conditions)
    "idx_segments_ticket_no" btree (ticket_no)
    "segments_flight_id_idx" btree (flight_id)
Check constraints:
    "segments_fare_conditions_check" CHECK (fare_conditions = ANY (ARRAY['Economy'::text, 'Comfort'::text, 'Business'::text]))
    "segments_price_check" CHECK (price >= 0::numeric)
Foreign-key constraints:
    "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
    "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Referenced by:
    TABLE "boarding_passes" CONSTRAINT "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id)

demo=# \d boarding_passes
                 Unlogged table "bookings.boarding_passes"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 ticket_no     | text                     |           | not null |
 flight_id     | integer                  |           | not null |
 seat_no       | text                     |           | not null |
 boarding_no   | integer                  |           |          |
 boarding_time | timestamp with time zone |           |          |
Indexes:
    "boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
    "boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
    "boarding_passes_flight_id_boarding_time" btree (flight_id, boarding_time)
    "boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no)
Foreign-key constraints:
    "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id)

demo=# \d flights
                                  Unlogged table "bookings.flights"
       Column        |           Type           | Collation | Nullable |           Default
---------------------+--------------------------+-----------+----------+------------------------------
 flight_id           | integer                  |           | not null | generated always as identity
 route_no            | text                     |           | not null |
 status              | text                     |           | not null |
 scheduled_departure | timestamp with time zone |           | not null |
 scheduled_arrival   | timestamp with time zone |           | not null |
 actual_departure    | timestamp with time zone |           |          |
 actual_arrival      | timestamp with time zone |           |          |
Indexes:
    "flights_pkey" PRIMARY KEY, btree (flight_id)
    "flights_route_no_scheduled_departure_key" UNIQUE CONSTRAINT, btree (route_no, scheduled_departure)
    "idx_flights_route_no" btree (route_no)
    "idx_flights_scheduled_departure" btree (scheduled_departure DESC)
Check constraints:
    "flight_actual_check" CHECK (actual_arrival IS NULL OR actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure)
    "flight_scheduled_check" CHECK (scheduled_arrival > scheduled_departure)
    "flight_status_check" CHECK (status = ANY (ARRAY['Scheduled'::text, 'On Time'::text, 'Delayed'::text, 'Boarding'::text, 'Departed'::text, 'Arrived'::text, 'Cancelled'::text]))
Referenced by:
    TABLE "segments" CONSTRAINT "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)

Тестовый запрос - LEFT JOIN

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;

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

Sort  (cost=2544355.52..2550077.32 rows=2288717 width=93) (actual time=58778.563..59050.999 rows=1146771 loops=1)
   Sort Key: b.book_date, t.ticket_no
   Sort Method: external merge  Disk: 121832kB
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2883.937..2883.945 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2883.919..2883.925 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=1.994..1.996 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=1.989..1.991 rows=1 loops=1)
                                 ->  Index Only Scan Backward using idx_bookings_book_date on bookings  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=1.988..1.989 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2883.917..2883.918 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       ->  Index Only Scan using idx_bookings_book_date on bookings bookings_1  (cost=0.43..54573.91 rows=2371064 width=16) (actual time=2.183..1714.099 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  Hash Left Join  (cost=685482.92..2046557.85 rows=2288717 width=93) (actual time=49378.694..57307.629 rows=1146771 loops=1)
         Hash Cond: (s.flight_id = f.flight_id)
         ->  Merge Right Join  (cost=681880.13..2036946.95 rows=2288717 width=69) (actual time=49326.107..56627.660 rows=1146771 loops=1)
               Merge Cond: (s.ticket_no = t.ticket_no)
               ->  Merge Left Join  (cost=1.12..1264914.46 rows=20598448 width=25) (actual time=3.919..32375.016 rows=6564405 loops=1)
                     Merge Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id))
                     ->  Index Only Scan using segments_pkey on segments s  (cost=0.56..424624.50 rows=20598448 width=18) (actual time=3.875..1950.364 rows=6564405 loops=1)
                           Heap Fetches: 0
                     ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..637941.24 rows=19870556 width=25) (actual time=0.033..25169.883 rows=6564405 loops=1)
               ->  Materialize  (cost=681879.01..690532.20 rows=1730639 width=58) (actual time=21308.253..22022.391 rows=1146771 loops=1)
                     ->  Sort  (cost=681879.01..686205.61 rows=1730639 width=58) (actual time=21308.245..21812.491 rows=862459 loops=1)
                           Sort Key: t.ticket_no
                           Sort Method: external merge  Disk: 58600kB
                           ->  Nested Loop Left Join  (cost=0.87..464448.40 rows=1730639 width=58) (actual time=2886.101..18909.459 rows=862459 loops=1)
                                 ->  Nested Loop  (cost=0.43..31711.08 rows=790355 width=31) (actual time=2884.013..3268.567 rows=397632 loops=1)
                                       ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=2883.941..2883.946 rows=1 loops=1)
                                       ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..23807.51 rows=790355 width=15) (actual time=0.063..308.659 rows=397632 loops=1)
                                             Index Cond: ((book_date >= rp.start_date) AND (book_date <= rp.end_date))
                                 ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..0.52 rows=3 width=34) (actual time=0.038..0.038 rows=2 loops=397632)
                                       Index Cond: (book_ref = b.book_ref)
         ->  Hash  (cost=2247.35..2247.35 rows=108435 width=28) (actual time=52.383..52.385 rows=108435 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 7377kB
               ->  Seq Scan on flights f  (cost=0.00..2247.35 rows=108435 width=28) (actual time=0.024..22.614 rows=108435 loops=1)
 Planning Time: 37.456 ms
 Execution Time: 59151.413 ms

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

Эксперимент-2 : вариант с оптимизацией структуры запроса (TUNING)

Тестовый запрос - TUNING

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;

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

 Gather Merge  (cost=227985.43..235104.05 rows=60584 width=77) (actual time=31581.892..32072.285 rows=1112921 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   CTE random_period
     ->  Limit  (cost=72357.36..72357.36 rows=1 width=24) (actual time=4621.212..4621.219 rows=1 loops=1)
           InitPlan 2
             ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=7.082..7.086 rows=1 loops=1)
                   InitPlan 1
                     ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=7.073..7.077 rows=1 loops=1)
                           ->  Index Only Scan Backward using idx_bookings_book_date on bookings bookings_1  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=7.071..7.073 rows=1 loops=1)
                                 Heap Fetches: 0
           ->  Sort  (cost=72356.89..78284.55 rows=2371064 width=24) (actual time=4621.209..4621.210 rows=1 loops=1)
                 Sort Key: (random())
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Index Only Scan using idx_bookings_book_date on bookings bookings_2  (cost=0.43..60501.57 rows=2371064 width=24) (actual time=8.609..3199.815 rows=6894590 loops=1)
                       Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                       Heap Fetches: 0
   InitPlan 4
     ->  CTE Scan on random_period  (cost=0.00..0.02 rows=1 width=8) (actual time=4621.220..4621.223 rows=1 loops=1)
   InitPlan 5
     ->  CTE Scan on random_period random_period_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Sort  (cost=154628.02..154779.48 rows=60584 width=77) (actual time=26940.612..27078.250 rows=556460 loops=2)
         Sort Key: bookings.book_date, tickets.ticket_no
         Sort Method: external merge  Disk: 50312kB
         Worker 0:  Sort Method: external merge  Disk: 50520kB
         ->  Nested Loop Left Join  (cost=2600.16..147409.45 rows=60584 width=77) (actual time=68.204..26185.997 rows=556460 loops=2)
               ->  Parallel Hash Left Join  (cost=2599.60..110002.19 rows=60584 width=70) (actual time=64.826..16855.338 rows=556460 loops=2)
                     Hash Cond: (segments.flight_id = flights.flight_id)
                     ->  Nested Loop Left Join  (cost=1.43..107244.98 rows=60584 width=46) (actual time=7.852..16362.564 rows=556460 loops=2)
                           ->  Nested Loop Left Join  (cost=0.87..78616.09 rows=45811 width=42) (actual time=3.772..12952.137 rows=424275 loops=2)
                                 ->  Parallel Index Scan using idx_bookings_book_date on bookings  (cost=0.43..982.51 rows=20921 width=15) (actual time=0.217..650.492 rows=197926 loops=2)
                                       Index Cond: ((book_date >= (InitPlan 4).col1) AND (book_date <= (InitPlan 5).col1))
                                 ->  Index Scan using idx_tickets_book_ref on tickets  (cost=0.43..3.68 rows=3 width=34) (actual time=0.058..0.061 rows=2 loops=395851)
                                       Index Cond: (book_ref = bookings.book_ref)
                           ->  Index Only Scan using segments_pkey on segments  (cost=0.56..0.60 rows=2 width=18) (actual time=0.007..0.007 rows=1 loops=848550)
                                 Index Cond: (ticket_no = tickets.ticket_no)
                                 Heap Fetches: 0
                     ->  Parallel Hash  (cost=1800.85..1800.85 rows=63785 width=28) (actual time=55.150..55.152 rows=54218 loops=2)
                           Buckets: 131072  Batches: 1  Memory Usage: 7840kB
                           ->  Parallel Seq Scan on flights  (cost=0.00..1800.85 rows=63785 width=28) (actual time=0.042..22.674 rows=54218 loops=2)
               ->  Index Scan using boarding_passes_pkey on boarding_passes  (cost=0.56..0.61 rows=1 width=25) (actual time=0.016..0.016 rows=1 loops=1112921)
                     Index Cond: ((ticket_no = tickets.ticket_no) AND (flight_id = segments.flight_id))
 Planning Time: 66.407 ms
 Execution Time: 32156.015 ms

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

Основные отличия планов

1. Стратегия соединения таблиц

Первый запрос (LEFT JOIN): Использует классические LEFT JOIN между таблицами
Второй запрос (TUNING): Применяет LATERAL JOIN, что позволяет более эффективно использовать индексы

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

Первый запрос: Выполняется в одном процессе
Второй запрос: Использует параллельное выполнение (Workers: 1) с Gather Merge

Преимущества оптимизированного запроса

1. Ранняя фильтрация

-- Эффективно: фильтрация бронирований до соединений
WHERE book_date BETWEEN (SELECT start_date FROM random_period)
AND (SELECT end_date FROM random_period)

2. LATERAL JOIN для точечных запросов

LEFT JOIN LATERAL (
SELECT ticket_no, passenger_name
FROM tickets
WHERE book_ref = b.book_ref -- Индексное сканирование
) t ON true

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

Parallel Hash Join
Parallel Seq Scan на flights
Gather Merge для сортировки

4. Улучшенное использование индексов

Все ключевые соединения используют индексные сканирования
Уменьшено количество обрабатываемых строк

Выводы

Оптимизированный запрос показывает значительное улучшение производительности за счет:

Более эффективной стратегии соединений (LATERAL vs обычные JOIN)
Параллельного выполнения
Лучшего использования индексов
Уменьшения объема обрабатываемых данных

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

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

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

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

Среднее увеличение операционной скорости при использовании запроса "EXISTS" составило 71.77%

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

График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "TUNING"

График изменения относительной разницы ожиданий СУБД для запроса "TUNING" по сравнению с тестовым запросом "JOIN"

Сравнительный анализ wait_event_type

1. wait_event_type = IPC

LEFT JOIN: Экстремально высокие значения - до 24,392
TUNING: Умеренные значения - до 2,868
Разница: В 8.5 раз меньше в оптимизированном запросе

2. wait_event_type =IO

LEFT JOIN: Средние значения - до 15,546
TUNING: Высокие значения - до 46,489
Разница: В 3 раза больше в оптимизированном запросе

3. wait_event_type =LWLOCK

LEFT JOIN: Высокие значения - до 111
TUNING: Низкие значения - до 22
Разница: В 5 раз меньше в оптимизированном запросе

4. wait_event_type =TIMEOUT/SpinDelay

LEFT JOIN: Значительные - до 19
TUNING: Минимальные - до 3
Разница: В 6 раз меньше в оптимизированном запросе

Преимущества оптимизированного запроса (TUNING):

Смещение нагрузки на IO - более предсказуемые и управляемые ожидания
Минимизация блокировок - резкое снижение IPC и LWLOCK
Параллельная обработка - процессы работают более независимо
TUNING паттерн: Высокий IO → Параллелизм → Минимальные блокировки

Итог

Оптимизированный запрос демонстрирует здоровый паттерн ожидания:

Замена дорогостоящих IPC-ожиданий на более эффективные IO-ожидания
Значительное снижение конкуренции за ресурсы
Улучшение параллелизма выполнения

Метрики iostat для дискового устройства, используемого файловой системой /data

Дисковая подсистема не участвует в ограничении производительности данных запросов.

Метрики vmstat

Сравнительный анализ ключевых метрик

Процессы (procs)

Параметр	LEFT JOIN	TUNING	Анализ
procs_r	4-9	5-8	Схожая нагрузка
procs_b	1-2	1-8	В TUNING в 4 раза больше процессов в ожидании

Память

Параметр	LEFT JOIN	TUNING	Анализ
memory_swpd	203-217	204	Одинаковый своп
memory_free	179-182	179-191	Больше свободной памяти в TUNING
memory_buff	10-147	105-147	Схожие значения
memory_cache	6823-7069	6690-6943	LEFT JOIN использует больше кэша

Ввод-вывод

Параметр	LEFT JOIN	TUNING	Анализ
io_bi	42442-86819	42442-74086	На 17% меньше операций ввода в TUNING
io_bo	3474-7528	3800-11145	На 48% больше операций вывода в TUNING

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

Параметр	LEFT JOIN	TUNING	Анализ
system_in	9681-14285	3175-9575	На 49% меньше прерываний в TUNING
system_cs	9559-13719	3175-4975	На 71% меньше переключений контекста в TUNING

Загрузка CPU

Параметр	LEFT JOIN	TUNING	Анализ
cpu_us	35-57%	46-66%	На 19% выше пользовательская нагрузка в TUNING
cpu_sy	4-5%	3-5%	Схожая системная нагрузка
cpu_id	16-47%	3-36%	На 62% меньше простоя в TUNING
cpu_wa	10-17%	12-28%	На 65% выше ожидание I/O в TUNING

TUNING (оптимизированный с LATERAL):

Более эффективное использование CPU (меньше простоя)
Значительно меньше системных издержек
Смещение нагрузки с чтения на запись (вероятно временные данные)
TUNING: Высокий cpu_us + Высокий cpu_wa → Эффективный параллелизм

Связь с ожиданиями СУБД:

Высокий cpu_wa в TUNING соответствует высоким IO в wait_event_type
Низкий cpu_id в TUNING подтверждает лучшую утилизацию процессора
Меньше system_* объясняет снижение IPC в wait_event_type

Итог

Оптимизированный запрос демонстрирует более здоровый паттерн использования ресурсов:

Эффективная утилизация CPU - меньше простоя, больше полезной работы
Снижение системных издержек - меньше прерываний и переключений контекста
Оптимальный баланс операций I/O - смещение в сторону записи временных данных

Несмотря на более высокое ожидание I/O, производительность увеличилась благодаря:

Параллельному выполнению
Уменьшению contention за ресурсы
Более эффективному плану выполнения

Это подтверждает, что оптимизация запроса дает значительный выигрыш в производительности.

Общий итог : Часть-2 "TUNING"

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

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

Среднее увеличение операционной скорости при использовании запроса "TUNING" составило 71.77%

? Сводка результатов нагрузочного тестирования

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

Операционная скорость: Увеличение на 71.77%
Эффективность использования ресурсов: Значительное улучшение

? Детальный анализ по компонентам

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

Планы выполнения:
LEFT JOIN - последовательное выполнение, массовые соединения
TUNING - параллельное выполнение, точечные LATERAL JOIN
Ожидания СУБД:
Снижение IPC в 8.5 раз
Снижение LWLOCK в 5 раз
Снижение TIMEOUT в 6 раз
Увеличение IO в 3 раза (более управляемый паттерн)

2. Использование системных ресурсов

CPU:
Утилизация процессора: +19% пользовательской нагрузки
Простой CPU: -62% (лучшее использование ресурсов)
Системные издержки: -49% прерываний, -71% переключений контекста
Память:
Оба запроса работают преимущественно в памяти
Эффективное использование shared buffers и кэша ОС
Дисковая подсистема:
Utilization 0% для обоих запросов
Не является ограничивающим фактором

? Архитектурные преимущества оптимизированного запроса

Стратегические улучшения

Параллельное выполнение - Gather Merge с Workers
Ранняя фильтрация - уменьшение объема обрабатываемых данных
LATERAL JOIN - точечные индексные сканирования вместо массовых соединений
Оптимальное использование индексов - все ключевые соединения используют индексы

Эффект на системном уровне

Оптимизированный паттерн:

Высокий CPU_US + Высокий CPU_WA → Эффективный параллелизм + Управляемые IO ожидания

Против исходного:

Высокий IPC + Высокий LWLOCK → Блокировки + Системные издержки

✅ Заключение

Оптимизация структуры запроса доказала свою исключительную эффективность:

✅ Рост операционной скорости на 71.77%
✅ Снижение системных издержек на 49-71%
✅ Уменьшение конкуренции за ресурсы
✅ Более здоровый паттерн использования CPU

Оптимизированный подход демонстрирует превосходную масштабируемость и эффективность использования ресурсов, что делает его рекомендованным решением для среды с высокой параллельной нагрузкой.

Продолжение цикла "Анализ вариантов оптимизации ресурсоёмкого SQL-запроса"

Вариант-3: Частичная агрегация и оконные функции

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

Вариант-5: Оптимизация с помощью условий в JOIN

Вариант-6: Пакетная обработка с LIMIT-OFFSET

Вариант-7: Использование покрывающих индексов

Вариант-8: Материализованное представление