Предисловие
Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?
Методология исследования
Тестовая среда и инструменты:
СУБД: PostgreSQL 17
Тестовая база данных: "Демобаза 2.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
Тестовый запрос - EXISTS
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; План выполнения тестового запроса - EXISTS
Sort (cost=2920577.58..2926299.37 rows=2288716 width=77) (actual time=52126.170..52366.389 rows=1076571 loops=1)
Sort Key: b.book_date, t.ticket_no
Sort Method: external merge Disk: 97592kB
CTE random_period
-> Limit (cost=191879.84..191879.84 rows=1 width=24) (actual time=4368.349..4368.353 rows=1 loops=1)
-> Sort (cost=191879.84..209662.82 rows=7113192 width=24) (actual time=4368.346..4368.349 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on bookings (cost=0.00..156313.88 rows=7113192 width=24) (actual time=0.033..3039.736 rows=7113192 loops=1)
-> Hash Left Join (cost=945420.57..2306495.49 rows=2288716 width=77) (actual time=43510.983..50848.328 rows=1076571 loops=1)
Hash Cond: (s.flight_id = f.flight_id)
-> Merge Right Join (cost=941817.78..2296884.59 rows=2288716 width=53) (actual time=43446.797..50235.025 rows=1076571 loops=1)
Merge Cond: (s.ticket_no = t.ticket_no)
-> Merge Left Join (cost=1.12..1264914.46 rows=20598448 width=25) (actual time=1.708..31823.629 rows=10235420 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=1.675..2642.891 rows=10235420 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.022..21355.771 rows=10235420 loops=1)
-> Materialize (cost=941816.66..950469.85 rows=1730639 width=42) (actual time=14800.486..15461.183 rows=1076571 loops=1)
-> Sort (cost=941816.66..946143.25 rows=1730639 width=42) (actual time=14800.477..15274.393 rows=834764 loops=1)
Sort Key: t.ticket_no
Sort Method: external merge Disk: 43680kB
-> Nested Loop Left Join (cost=0.43..731315.04 rows=1730639 width=42) (actual time=4370.165..12416.101 rows=834764 loops=1)
-> Nested Loop Semi Join (cost=0.00..298577.73 rows=790355 width=15) (actual time=4369.207..8500.573 rows=391485 loops=1)
Join Filter: ((b.book_date >= rp.start_date) AND (b.book_date <= rp.end_date))
Rows Removed by Join Filter: 6721707
-> Seq Scan on bookings b (cost=0.00..120747.92 rows=7113192 width=15) (actual time=0.589..818.523 rows=7113192 loops=1)
-> CTE Scan on random_period rp (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=7113192)
-> Index Scan using idx_tickets_book_ref on tickets t (cost=0.43..0.52 rows=3 width=34) (actual time=0.008..0.009 rows=2 loops=391485)
Index Cond: (book_ref = b.book_ref)
-> Hash (cost=2247.35..2247.35 rows=108435 width=28) (actual time=63.974..63.976 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.789..32.556 rows=108435 loops=1)
Planning Time: 10.731 ms
Execution Time: 52459.463 msСравнительный анализ планов выполнения запросов
Общие характеристики
Оба запроса выполняют одинаковую логику - выборку данных о бронированиях за случайный 30-дневный период с присоединением связанных данных о билетах, рейсах и посадочных талонах.
Ключевые различия
Стратегия соединения с random_period
LEFT JOIN запрос:
Использует CROSS JOIN LATERAL для соединения с отфильтрованными бронированиями
Эффективно использует индекс idx_bookings_book_date
Обрабатывает только 397,632 строк из bookings
EXISTS запрос:
Использует EXISTS с подзапросом в WHERE
Выполняет полное сканирование таблицы bookings (7,113,192 строк)
Применяет Join Filter который отбрасывает 6,721,707 строк
Производительность и ожиданий СУБД
Среднее уменьшение операционной скорости при использовании запроса "EXISTS" составило 12.84%
Типы событий ожиданий СУБД (wait_event_type)
1. wait_event_type = IO
JOIN: начальное ~6,800 → максимальное ~17,400 (рост в 2.6 раза)
EXISTS: начальное ~6,700 → максимальное ~16,100 (рост в 2.4 раза)
Вывод: Оба запроса создают нагрузку на подсистему ввода-вывода, но JOIN требует на 8-10% больше ожиданий IO.
2. wait_event_type = IPC
JOIN: начальное ~3,900 → максимальное ~26,500 (рост в 6.8 раза)
EXISTS: начальное ~3,900 → максимальное ~24,600 (рост в 6.2 раза)
Вывод: JOIN создает значительно большую нагрузку на IPC (на 15-20% выше), что указывает на более интенсивное взаимодействие между процессами.
3. wait_event_type = LWLOCK
JOIN: начальное ~13 → максимальное ~143 (рост в 11 раз)
EXISTS: начальное ~14 → максимальное ~133 (рост в 9.5 раз)
Вывод: JOIN требует значительно больше легковесных блокировок (на 20-25% выше), что может указывать на более сложную синхронизацию.
4. wait_event_type =TIMEOUT/SpinDelay
JOIN: начальное ~4 → максимальное ~21 (рост в 5.3 раза)
EXISTS: начальное ~3 → максимальное ~17 (рост в 5.7 раза)
Вывод: Оба запроса демонстрируют сходное поведение по таймаутам, что может указывать на схожие проблемы с конкуренцией за ресурсы.
Метрики iostat для дискового устройства, используемого файловой системой /data
Оба теста демонстрируют идентичные показатели - все метрики остаются абсолютно стабильными на протяжении всего тестирования без каких-либо изменений.
метрики vmstat
Оба теста демонстрируют высокую нагрузку на систему, но с различными характеристиками распределения ресурсов.
1. Процессы (procs)
JOIN |
EXISTS |
|
procs_r |
4 → 9 (рост в 2.25 раза) |
4 → 10 (рост в 2.5 раза) |
procs_b |
1 → 3 |
1 → 2 |
Вывод: EXISTS создает больше процессов в состоянии выполнения, но меньше блокированных процессов.
2. Память (memory)
JOIN |
EXISTS |
|
swpd |
203 → 217 |
217 → 212 |
free |
180 → 179 |
181 → 180 |
buff |
106 → 10 |
7 → 7 |
cache |
7012 → 6771 |
7129 → 6869 |
Вывод: Оба запроса эффективно используют кэш, но JOIN активнее использует буферы.
3. Ввод-вывод (I/O)
JOIN |
EXISTS |
|
io_bi |
66077 → 86122 |
63848 → 77227 |
io_bo |
3474 → 8023 |
2276 → 4984 |
Вывод: JOIN создает на 15-20% большую нагрузку на ввод-вывод.
4. Системные события
JOIN |
EXISTS |
|
system_in |
10449 → 14832 |
10030 → 14569 |
system_cs |
9681 → 14137 |
9967 → 13686 |
Вывод: JOIN вызывает больше системных прерываний и переключений контекста.
5. Использование CPU
JOIN |
EXISTS |
|
cpu_us |
35 → 58 |
37 → 63 |
cpu_sy |
4 → 5 |
4 → 5 |
cpu_id |
47 → 14 |
46 → 14 |
cpu_wa |
10 → 12 |
11 → 12 |
Вывод: EXISTS потребляет больше пользовательского CPU времени.
Общие проблемы обоих запросов:
Высокое использование CPU (idle снижается с ~46% до ~14%)
Значительная нагрузка на I/O
Увеличение количества процессов
Анализ трендов нагрузки
Начальная фаза (первые 20-30 измерений):
Оба запроса показывают сходное поведение
Стабильное использование ресурсовПиковая фаза (середина теста):
JOIN демонстрирует более резкий рост I/O нагрузки
EXISTS показывает более плавное увеличение нагрузкиФинальная фаза:
Оба запроса стабилизируются на высоком уровне нагрузки
JOIN сохраняет более высокие показатели I/O
EXISTS демонстрирует лучшую общую эффективность по ключевым метрикам:
✅ На 15-20% меньше операций I/O
✅ Меньше блокированных процессов
✅ Более стабильное использование памяти
✅ Меньше системных прерываний
JOIN показывает:
⚠️ Более высокую нагрузку на I/O подсистему
⚠️ Больше блокировок процессов
⚠️ Более интенсивные системные прерывания
Общий итог : Часть-1 "EXISTS"
Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - использование конструкции EXISTS не дает существенных преимуществ.
Производительность СУБД:
Среднее уменьшение операционной скорости при использовании запроса "EXISTS" составило 12.84%
Использование ресурсов СУБД:
JOIN создает на 15-25% большую нагрузку на подсистему ввода-вывода и межпроцессное взаимодействие
EXISTS генерирует меньше блокировок и системных прерываний
Системные метрики:
EXISTS показывает более стабильное использование памяти и меньше блокированных процессов
JOIN создает более интенсивную нагрузку на I/O подсистему
Продолжение цикла "Анализ вариантов оптимизации ресурсоёмкого SQL-запроса"
Часть-2: Оптимизация структуры запроса
Часть-3: Частичная агрегация и оконные функции
Часть-4: Использование временных таблиц
Часть-5: Оптимизация с помощью условий в JOIN
Часть-6: Пакетная обработка с LIMIT-OFFSET
Часть-7: Использование покрывающих индексов
Часть-8: Материализованное представление