Статистический анализ производительности СУБД PostgreSQL
Предисловие
Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?
Методология исследования
Тестовая среда и инструменты:
Тестовая ВМ: 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
Нагрузка на СУБД
Тестовый запрос, используемый в качестве 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)
Параллельного выполнения
Лучшего использования индексов
Уменьшения объема обрабатываемых данных
Производительность и ожидания СУБД
Операционная скорость
Среднее увеличение операционной скорости при использовании запроса "EXISTS" составило 71.77%
Ожидания СУБД
Сравнительный анализ 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: Частичная агрегация
