64 подписчика

➡️✔️"Демобаза 2.0" нагрузочное тестирование : СУБД оказалась устойчива к выбору между Join и коррелированным подзапросом.

18 ноября 202518 ноя 2025

10 мин

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub Принято считать, что выбор между JOIN и коррелированным подзапросом — одна из ключевых задач оптимизации, способная кардинально повлиять на нагрузку базы данных. В качестве эксперимента, было проведено нагрузочное тестирование, используя Демобазу 2.0 в качестве полигона и vmstat для мониторинга изменений со стороны инфраструктуры, готовясь наглядно продемонстрировать превосходство одного подхода над другим. Однако результаты оказались неожиданными. Исследование показало практическое отсутствие существенного влияния выбранной структуры запроса на общую производительность СУБД и сервера. В данной статье показано, что в контексте современной оптимизации запросов и мощного аппаратного обеспечения, "страшилка" о катастрофических последствиях использования коррелированных подзапросов часто преувеличена. Нагрузочное тестирование выяв

Оглавление

Предисловие:
Демобаза 2.0
Тестовая виртуальная машина

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

gitflic.ru

kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL

github.com

GitHub - pg-expecto/pg_expecto: Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Предисловие:

Принято считать, что выбор между JOIN и коррелированным подзапросом — одна из ключевых задач оптимизации, способная кардинально повлиять на нагрузку базы данных. В качестве эксперимента, было проведено нагрузочное тестирование, используя Демобазу 2.0 в качестве полигона и vmstat для мониторинга изменений со стороны инфраструктуры, готовясь наглядно продемонстрировать превосходство одного подхода над другим.

Однако результаты оказались неожиданными. Исследование показало практическое отсутствие существенного влияния выбранной структуры запроса на общую производительность СУБД и сервера. В данной статье показано, что в контексте современной оптимизации запросов и мощного аппаратного обеспечения, "страшилка" о катастрофических последствиях использования коррелированных подзапросов часто преувеличена. Нагрузочное тестирование выявило, что СУБД успешно справляется с обоими типами запросов, а реальное влияние на метрики vmstat оказалось малым, что позволяет разработчикам в подобных случаях делать выбор, основываясь на читаемости кода, а не на гипотетических рисках для производительности.

Демобаза 2.0

PG_EXPECTO 4 + Демобаза 2.0 : использование тестовой базы данных для нагрузочного тестирования СУБД

Postgres DBA11 ноября 2025

Тестовая виртуальная машина

CPU = 8

RAM = 8GB

PostgreSQL 17

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

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ

# Максимальная нагрузка

finish_load = 20

# Тестовая БД

testdb = demo

# Веса сценариев

scenario1 = 1.0

scenario2 = 1.0

scenario3 = 1.0

scenario4 = 1.0

Тестовый сценарий-1

-- scenario1.sql

-- version 4.0

-- Простой точечный SELECT по первичному ключу.

CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$

DECLARE

test_rec record ;

test_code text ;

BEGIN

SET application_name = 'scenario1';

SELECT

array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',((random()*(26-1)+1)::integer),1) from generate_series(1,3)),'')

INTO

test_code ;

SELECT

*

INTO

test_rec

FROM

airports_data

WHERE

airport_code = test_code ;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-2

-- scenario2.sql

-- version 4.0

-- Запрос с GROUP BY и хэш-агрегацией.

CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario2';

WITH empty_flights AS

(

SELECT f.flight_id, count( bp.flight_id )

FROM bookings.flights f

LEFT JOIN bookings.boarding_passes bp ON bp.flight_id = f.flight_id

WHERE f.status IN ( 'Departed', 'Arrived' )

GROUP BY f.flight_id

HAVING count( bp.flight_id ) = 0

)

SELECT count(*) empty,

CASE WHEN count(*) > 0 THEN 'WARNING: empty cabin' ELSE 'Ok' END verdict

INTO test_rec

FROM empty_flights;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-3

-- scenario3.sql

-- version 4.0

-- Запросы с ORDER BY ... LIMIT N

CREATE OR REPLACE FUNCTION scenario4() RETURNS integer AS $$

DECLARE

test_rec record ;

test_limit integer ;

BEGIN

SET application_name = 'scenario3';

SELECT random() * 1000 + 1

INTO

test_limit ;

SELECT a.airplane_code, a.model->>'en' AS model,

count(DISTINCT r.route_no) AS no_flights,

CASE

WHEN count(DISTINCT r.route_no) > 0 AND a.in_use THEN 'Ok'

WHEN count(DISTINCT r.route_no) = 0 AND a.in_use THEN 'NOT USED'

WHEN count(DISTINCT r.route_no) > 0 AND NOT a.in_use THEN 'WRONGLY USED'

WHEN count(DISTINCT r.route_no) = 0 AND NOT a.in_use THEN 'Ok (not in use)'

END AS verdict

INTO test_rec

FROM bookings.routes r

RIGHT JOIN gen.airplanes_data a ON a.airplane_code = r.airplane_code

GROUP BY a.airplane_code, a.model, a.in_use

ORDER BY a.airplane_code

LIMIT test_limit ;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-4.1 (JOIN)

-- scenario4.sql

-- version 4.0

-- Запросы с JOIN

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario4';

WITH seats_available AS

( SELECT airplane_code, fare_conditions, count( * ) AS seats_cnt

FROM bookings.seats

GROUP BY airplane_code, fare_conditions

), seats_booked AS

( SELECT flight_id, fare_conditions, count( * ) AS seats_cnt

FROM bookings.segments

GROUP BY flight_id, fare_conditions

), overbook AS (

SELECT f.flight_id, r.route_no, r.airplane_code, sb.fare_conditions,

sb.seats_cnt AS seats_booked,

sa.seats_cnt AS seats_available

FROM bookings.flights AS f

JOIN bookings.routes AS r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN seats_booked AS sb ON sb.flight_id = f.flight_id

JOIN seats_available AS sa ON sa.airplane_code = r.airplane_code

AND sa.fare_conditions = sb.fare_conditions

WHERE sb.seats_cnt > sa.seats_cnt

)

SELECT count(*) overbookings,

CASE WHEN count(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END verdict

INTO test_rec

FROM overbook;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-4.2 (Коррелированный подзапрос)

Создание индексов

demo=# CREATE INDEX CONCURRENTLY idx_seats_airplane_fare ON bookings.seats(airplane_code, fare_conditions);

CREATE INDEX

demo=# CREATE INDEX CONCURRENTLY idx_segments_flight_fare ON bookings.segments(flight_id, fare_conditions);

CREATE INDEX

demo=# CREATE INDEX CONCURRENTLY idx_routes_no_validity ON bookings.routes(route_no, validity);

CREATE INDEX

Изменение SQL запроса

-- scenario4.sql

-- version 4.0

-- коррелированный подзапрос

CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario4';

WITH seats_agg AS MATERIALIZED (

SELECT

airplane_code,

fare_conditions,

COUNT(*) AS seats_total

FROM bookings.seats

GROUP BY airplane_code, fare_conditions

)

SELECT

COUNT(*) AS overbookings,

CASE WHEN COUNT(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END AS verdict

INTO test_rec

FROM (

SELECT 1

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN (

SELECT

flight_id,

fare_conditions,

COUNT(*) AS seats_booked

FROM bookings.segments

GROUP BY flight_id, fare_conditions

) sb ON sb.flight_id = f.flight_id

WHERE sb.seats_booked > (

SELECT sa.seats_total

FROM seats_agg sa

WHERE sa.airplane_code = r.airplane_code

AND sa.fare_conditions = sb.fare_conditions

)

) overbooked;

Планы выполнения тестовых запросов с использованием Join и Коррелированного подзапроса

План выполнения сценарий-4.1 (JOIN)

Aggregate (cost=344227.87..344227.88 rows=1 width=40) (actual time=4847.002..4847.172 rows=1 loops=1)

QUERY PLAN

------------------------------------------------------------------------------------------Aggregate (cost=344227.87..344227.88 rows=1 width=40) (actual time=4847.002..4847.172 rows=1 loops=1)

-> Hash Join (cost=336510.65..344227.85 rows=5 width=0) (actual time=4846.997..4847.166 rows=0 loops=1)

Hash Cond: ((f.route_no = r.route_no) AND (seats.airplane_code = r.airplane_code))

Join Filter: (r.validity @> f.scheduled_departure)

Rows Removed by Join Filter: 346552

-> Nested Loop (cost=336267.85..343763.20 rows=10745 width=19) (actual time=1909.202..4535.444 rows=745468 loops=1)

-> Hash Join (cost=336267.56..340364.01 rows=10745 width=8) (actual time=1908.114..2479.199 rows=745468 loops=1)

Hash Cond: (segments.fare_conditions = seats.fare_conditions)

Join Filter: ((count(*)) > (count(*)))

Rows Removed by Join Filter: 1164168

-> Finalize HashAggregate (cost=336226.55..338912.75 rows=268620 width=20) (actual time=1906.750..2079.580 rows=268571 loops=1)

Group Key: segments.flight_id, segments.fare_conditions

Batches: 5 Memory Usage: 28721kB Disk Usage: 736kB

-> Gather (cost=1000.44..328167.95 rows=1074480 width=20) (actual time=1.595..1678.058 rows=273890 loops=1)

Workers Planned: 4

Workers Launched: 4

-> Partial GroupAggregate (cost=0.44..219719.95 rows=268620 width=20) (actual time=0.963..1827.841 rows=54778 loops=5)

Group Key: segments.flight_id, segments.fare_conditions

-> Parallel Index Only Scan using idx_segments_flight_fare on segments (cost=0.44..178411.66 rows=5149612 width=12) (actual time=0.934..1237.930 rows=4119689 loops=5)

Heap Fetches: 323405

-> Hash (cost=40.71..40.71 rows=24 width=20) (actual time=1.306..1.308 rows=20 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 10kB

-> HashAggregate (cost=40.47..40.71 rows=24 width=20) (actual time=1.287..1.293 rows=20 loops=1)

Group Key: seats.airplane_code, seats.fare_conditions

Batches: 1 Memory Usage: 24kB

-> Seq Scan on seats (cost=0.00..27.41 rows=1741 width=12) (actual time=0.094..0.504 rows=1741 loops=1)

-> Index Scan using flights_pkey on flights f (cost=0.29..0.32 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=745468)

Index Cond: (flight_id = segments.flight_id)

-> Hash (cost=155.92..155.92 rows=5792 width=33) (actual time=4.500..4.501 rows=5792 loops=1)

Buckets: 8192 Batches: 1 Memory Usage: 432kB

-> Seq Scan on routes r (cost=0.00..155.92 rows=5792 width=33) (actual time=0.083..2.335 rows=5792 loops=1)

Planning Time: 10.698 ms

Execution Time: 4857.452 ms

(33 rows)

План выполнения сценарий-4.2 (Коррелированный подзапрос)

Aggregate (cost=356898.94..356898.96 rows=1 width=40) (actual time=3774.801..3774.963 rows=1 loops=1)

QUERY PLAN

------------------------------------------------------------------------------------------Aggregate (cost=356898.94..356898.96 rows=1 width=40) (actual time=3774.801..3774.963 rows=1 loops=1)

CTE seats_agg

-> HashAggregate (cost=40.47..40.71 rows=24 width=20) (actual time=0.782..0.789 rows=20 loops=1)

Group Key: seats.airplane_code, seats.fare_conditions

Batches: 1 Memory Usage: 24kB

-> Seq Scan on seats (cost=0.00..27.41 rows=1741 width=12) (actual time=0.032..0.225 rows=1741 loops=1)

-> Hash Join (cost=348691.87..356855.81 rows=971 width=0) (actual time=3774.793..3774.952 rows=0 loops=1)

Hash Cond: (segments.flight_id = f.flight_id)

Join Filter: ((count(*)) > (SubPlan 2))

Rows Removed by Join Filter: 268571

-> Finalize HashAggregate (cost=336226.55..338912.75 rows=268620 width=20) (actual time=1304.096..1459.047 rows=268571 loops=1)

Group Key: segments.flight_id, segments.fare_conditions

Batches: 5 Memory Usage: 28721kB Disk Usage: 736kB

-> Gather (cost=1000.44..328167.95 rows=1074480 width=20) (actual time=0.753..1093.867 rows=273893 loops=1)

Workers Planned: 4

Workers Launched: 4

-> Partial GroupAggregate (cost=0.44..219719.95 rows=268620 width=20) (actual time=0.156..1230.150 rows=54779 loops=5)

Group Key: segments.flight_id, segments.fare_conditions

-> Parallel Index Only Scan using idx_segments_flight_fare on segments (cost=0.44..178411.66 rows=5149612 width=12) (actual time=0.123..665.551 rows=4119689 loops=5)

Heap Fetches: 323348

-> Hash (cost=12450.62..12450.62 rows=1176 width=8) (actual time=1207.823..1207.826 rows=108435 loops=1)

Buckets: 131072 (originally 2048) Batches: 1 (originally 1) Memory Usage: 5260kB

-> Nested Loop (cost=0.42..12450.62 rows=1176 width=8) (actual time=0.088..1169.358 rows=108435 loops=1)

-> Seq Scan on routes r (cost=0.00..155.92 rows=5792 width=33) (actual time=0.024..1.396 rows=5792 loops=1)

-> Index Scan using flights_route_no_scheduled_departure_key on flights f (cost=0.42..2.11 rows=1 width=19) (actual time=0.093..0.198 rows=19 loops=5792)

Index Cond: (route_no = r.route_no)

Filter: (r.validity @> scheduled_departure)

Rows Removed by Filter: 184

SubPlan 2

-> CTE Scan on seats_agg sa (cost=0.00..0.60 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=268571)

Filter: ((airplane_code = r.airplane_code) AND (fare_conditions = segments.fare_conditions))

Rows Removed by Filter: 19

Planning Time: 5.590 ms

Execution Time: 3790.543 ms

(34 rows)

Важная деталь по планам выполнения

Стоимости планов выполнения с использованием Join или с использованием Коррелированного подзапроса - практически не отличаются .

JOIN : cost=344227.87..344227.88
Коррелированный подзапрос : cost=356898.94..356898.96

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

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

Операционная скорость СУБД

График изменения операционной скорости СУБД для нагрузочного тестирования с использованием Join и Коррелированного подзапроса.

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

Средняя разница операционной скорости СУБД при использовании JOIN и Коррелированного подзапроса составила 0.58%.

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

График изменения операционной скорости тестового сценария с использованием Join и Коррелированного подзапроса.

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

Средняя разница операционной скорости тестового запроса при использовании JOIN и Коррелированного подзапроса составила 2.44%.

Ожидания типа LWLock

График изменения ожиданий типа LWLock для нагрузочного тестирования с использованием Join и Коррелированного подзапроса.

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

С ростом нагрузки, количество ожиданий типа LWLock - снижается, при использовании коррелированного подзапроса, в сравнении с использованием JOIN.

Вывод по результатам анализа метрик производительности СУБД

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