Аннотация

В статье проводится сравнительный анализ эффективности использования оператора JOIN и коррелированного подзапроса в СУБД PostgreSQL в условиях высокой параллельной нагрузки. На основе экспериментальных данных опровергаются универсальные рекомендации систем искусственного интеллекта и выявляются ключевые факторы, влияющие на производительность.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

1. Постановка задачи

Рассматривается вопрос выбора оптимального паттерна для выполнения запросов при высокой параллельной нагрузке на СУБД: использование JOIN или коррелированного подзапроса.

Были получены рекомендации от нейросетевых моделей:

• «Ask Postgres»: Для нагрузочных тестов с растущей параллельностью всегда используйте версию с JOIN. Коррелированные подзапросы с агрегациями — плохая практика в сценариях с высокой конкуренцией.

• «DeepSeek»: Для данного сценария производительность будет выше при использовании запроса с LEFT JOIN и GROUP BY.

2. Детали эксперимента

Полное описание эксперимента:

Опасный мираж оптимизации: почему нейросетевые советы по СУБД PostgreSQL убивают производительность под нагрузкой.

2.1. Тестовый запрос с использованием JOIN

SELECT

c.customer_id, COUNT(o.order_id) AS orders_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id;

План выполнения

HashAggregate (cost=35.85..37.25 rows=140 width=12) (actual time=0.622..0.629 rows=25 loops=1)

Group Key: c.customer_id

Batches: 1 Memory Usage: 40kB

-> Hash Right Join (cost=13.15..30.85 rows=1000 width=8) (actual time=0.077..0.429 rows=1000 loops=1)

Hash Cond: (o.customer_id = c.customer_id)

-> Seq Scan on orders o (cost=0.00..15.00 rows=1000 width=8) (actual time=0.035..0.148 rows=1000 loops=1)

-> Hash (cost=11.40..11.40 rows=140 width=4) (actual time=0.028..0.028 rows=25 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 9kB

-> Seq Scan on customers c (cost=0.00..11.40 rows=140 width=4) (actual time=0.018..0.021 rows=25 loops=1)

Planning Time: 0.221 ms

Execution Time: 0.787 ms

2.2. Тестовый запрос с использованием коррелированного подзапроса

SELECT c.customer_id,

(SELECT COUNT(o.order_id)

FROM orders o

WHERE o.customer_id = c.customer_id) AS orders_count

FROM customers c;

План выполнения

Seq Scan on customers c (cost=0.00..1015.20 rows=140 width=12) (actual time=0.093..0.614 rows=25 loops=1)

SubPlan 1

-> Aggregate (cost=7.16..7.17 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=25)

-> Bitmap Heap Scan on orders o (cost=1.56..7.06 rows=40 width=4) (actual time=0.007..0.017 rows=40 loops=25)

Recheck Cond: (customer_id = c.customer_id)

Heap Blocks: exact=125

-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..1.55 rows=40 width=0) (actual time=0.004..0.004 rows=40 loops=25)

Index Cond: (customer_id = c.customer_id)

Planning Time: 0.145 ms

Execution Time: 0.718 ms

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

В ходе сравнительного нагрузочного тестирования была измерена операционная скорость СУБД при использовании оператора JOIN и коррелированного подзапроса. Согласно результатам, среднее снижение операционной скорости при использовании JOIN составило 288% по сравнению с коррелированным подзапросом.

3. Анализ причин некорректности рекомендаций нейросетей

3.1. Применение статических эвристик вместо анализа плана выполнения

Нейросети опираются на общие рекомендации, такие как:

• «JOIN обычно эффективнее подзапросов»;

• «Избегайте N+1 запросов»;

• «Коррелированные подзапросы плохо масштабируются».

Однако они не анализируют конкретные планы выполнения запросов в условиях высокой нагрузки и конкуренции за ресурсы.

3.2. Игнорирование паттернов доступа к данным

Анализ планов выполнения показал:

• Запрос 1 (JOIN): Seq Scan on orders (полное сканирование таблицы).

• Запрос 2 (Подзапрос): Bitmap Index Scan on idx_orders_customer_id (точечный доступ по индексу).

При параллельных соединениях:

• Количество сессий × Seq Scan = количество полных сканирований таблицы orders.

• Количество сессий × Index Scan = равномерно распределенная нагрузка на чтение.

3.3. Неучёт механизмов блокировки и конфликтов ресурсов

• Проблема JOIN при высокой конкуренции: Все сессии одновременно читают одни и те же страницы таблицы orders, что вызывает конфликт ресурсов (contention) на буферный кэш и подсистему ввода-вывода.

• Преимущество подзапроса: Каждая сессия работает с разными частями индекса, что снижает конкуренцию за блокировки и улучшает параллелизм.

3.4. Разный профиль использования памяти

• JOIN: Memory Usage: 40 kB + хэш-таблица.

• Подзапрос: Точечное использование памяти для каждого клиента.

При множественных сессиях JOIN создает значительную нагрузку на shared_buffers.

4. Критические факторы, упускаемые нейросетями

1. Влияние на shared_buffers: Множественные последовательные сканирования вытесняют полезные данные из кэша.

2. Lock contention: Конкуренция за одни и те же ресурсы (блокировки).

3. Распределение операций ввода-вывода: Индексные чтения лучше распределены.

4. Параметры PostgreSQL: Значения work_mem, shared_buffers, random_page_cost и других настроек существенно влияют на результат.

5. Причины ошибок в рекомендациях нейросетевых моделей

Нейросети обучаются на синтетических или упрощённых данных, для которых характерны:

• Небольшой объём наборов данных (TPC-H, TPC-DS).

• Низкая параллельность запросов (1–10 соединений).

• Идеализированные индексы.

• Отсутствие блокировок и конкуренции за ресурсы (ЦП, ввод-вывод).

В результате модель вырабатывает универсальное правило «JOIN всегда лучше», которое не работает в реальных условиях высокой конкуренции.

6. Заключение

Рекомендации, сгенерированные нейросетями, основаны на общих эвристиках и не могут учитывать всех особенностей конкретной эксплуатационной среды. Они не заменяют глубокого анализа планов выполнения запросов и понимания архитектуры СУБД под нагрузкой. Проведенный эксперимент наглядно демонстрирует важность практического тестирования и невозможность слепого следования автоматизированным советам.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

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

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

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

Демобаза 2.0

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

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

CPU = 8

RAM = 8GB

PostgreSQL 17

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

-- Запросы с 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 запроса

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

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 и Коррелированного подзапроса составила 0.58%.

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

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

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

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Постановка эксперимента

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

Тестовый запрос-1 : с использованием JOIN

SELECT

c.customer_id, COUNT(o.order_id) AS orders_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id;

Тестовый запрос-2 : с использованием коррелированного подзапроса

SELECT c.customer_id,

(SELECT COUNT(o.order_id)

FROM orders o

WHERE o.customer_id = c.customer_id) AS orders_count

FROM customers c;

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

Вопрос нейросети

Тестовые таблицы

"-- Create the customers table

CREATE TABLE customers (

customer_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL );

-- Insert 25 random customer records

INSERT INTO customers (name) VALUES ('Alice Smith'), ('Bob Johnson'), ('Charlie Brown'), ('Diana Prince'), ('Ethan Hunt'), ('Fiona Gallagher'), ('George Miller'), ('Hannah Abbott'), ('Ian Fleming'), ('Jane Austen'), ('Kevin Spacey'), ('Laura Croft'), ('Michael Jordan'), ('Nancy Drew'), ('Oliver Twist'), ('Penelope Cruz'), ('Quentin Tarantino'), ('Rachel Green'), ('Steve Rogers'), ('Tina Turner'), ('Ursula K. Le Guin'), ('Victor Hugo'), ('Wendy Darling'), ('Xavier Roberts'), ('Yvonne Craig');

-- Create the orders table

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INT,

order_date TIMESTAMP,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );

INSERT INTO orders (customer_id) WITH RECURSIVE data_rows (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM data_rows WHERE n < 1000 ) SELECT FLOOR(RANDOM() * 25) + 1 FROM data_rows;

-- Create index on orders table

CREATE INDEX idx_orders_customer_id ON orders (customer_id);".

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

"SELECT c.customer_id, COUNT(o.order_id) AS orders_count INTO test_rec FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;" .

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

"SELECT c.customer_id, (SELECT COUNT(o.order_id) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count INTO test_rec FROM customers c;" . Тестовая СУБД :

CPU = 8 , RAM = 8GB , Версия PostgreSQL 17.

Нагрузка растет экспоненциально от 10 до 45 одновременных сессий, длительность тестовой итерации 10 минут.

Вопрос - для какого тестового запроса производительность СУБД будет выше в ходе нагрузочного тестирования - при использовании тестового запроса-1 или при использовании тестового запроса-2 ?

1️⃣Ответ нейросети "Ask Postgres"

✅ Производительность будет выше при использовании тестового запроса-1 (LEFT JOIN + GROUP BY)

Обоснование

1. Запрос-1 (JOIN + GROUP BY) — оптимальный

• Один проход по таблице orders с использованием индекса idx_orders_customer_id.

• PostgreSQL 17 эффективно использует параллельные планы для JOIN и GROUP BY — при 45 сессиях нагрузка распределяется между ядрами CPU.

• Минимальная конкуренция за ресурсы: индекс сканируется один раз на запрос, а не 25 раз.

• Оптимизатор может применить Hash Join или Merge Join, что особенно эффективно при наличии индекса и небольшом количестве клиентов (25).

• Масштабируемость: при росте числа сессий производительность падает умеренно — линейно с числом запросов, а не с числом строк в orders.

Документация подтверждает:

«Subqueries can be transformed into semi-joins» — но только для некоррелир

ованных.

Коррелированные подзапросы (как в запросе-2) не преобразуются в JOIN автом

атически, особенно при использовании COUNT() — это ограничение оптимизатора.

2. Запрос-2 (коррелированный подзапрос) — неэффективный при нагрузке

• Для каждой из 25 строк customers выполняется отдельный подзапрос.

• При 45 сессиях — 1125 отдельных сканов индекса idx_orders_customer_id в секунду.

• Это вызывает: Конкуренцию за блоки индекса → локальные блокировки.

• Высокую нагрузку на CPU → много контекстных переключений.

• Ограничение параллелизма — подзапросы выполняются последовательно внутри одного запроса.

  📌 Рекомендация

  Для нагрузочных тестов с растущей параллельностью всегда используйте JOIN-версию (запрос-1).

  Коррелированные подзапросы с агрегациями — плохая практика в сценариях с высокой конкуренцией, даже если они выглядят «проще».

  Дополнительно: Для ещё большей производительности можно добавить CLUSTER на orders по customer_id, или использовать materialized view для кэширования агрегированных данных, если данные не меняются часто.

  ✅ Итог: Запрос-1 (LEFT JOIN + GROUP BY) обеспечивает значительно более высокую производительность при нагрузке 10–45 сессий.

  2️⃣Ответ нейросети "DeepSeek"

  Для данного сценария нагрузочного тестирования производительность будет выше при использовании тестового запроса-1 (с LEFT JOIN и GROUP BY). Обоснование:

  1. Оптимизация выполнения запроса:

  2. Запрос-1 выполняется как одиночный оператор с хеш-соединением и агрегацией, что эффективно обрабатывается планировщиком PostgreSQL.

  3. Запрос-2 использует коррелированный подзапрос, который выполняет 25 отдельных запросов COUNT (по одному на каждого клиента). При параллельных сессиях это создает дополнительную нагрузку.

  4. Эффективность при параллелизме:

  5. Запрос-1 лучше масштабируется благодаря хеш-агрегации и однократному сканированию таблиц.

  6. Запрос-2 генерирует множество индексных сканиваний (используя idx_orders_customer_id), что при 45 сессиях приводит к конкуренции за ресурсы ( latch-конфликты в кэше индексов).

  7. Статистика ресурсов:

  8. При 1000 заказах и 25 клиентах разница незначительна, но при экспоненциальном росте сессий запрос-2 сильнее нагружает CPU (многократные поиски в индексе) и RAM (больше служебных структур на сессию).

  9. Особенности PostgreSQL:

  10. Оптимизатор PostgreSQL эффективнее обрабатывает JOIN-агрегацию, чем вложенные подзапросы, при параллельных выполнениях. Версия 17 дополнительно улучшает параллельные хеш-соединения.

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

  ⚠️Результаты экспериментального нагрузочного тестирования

  Абсолютные значения операционной скорости и ожиданий

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

💥Среднее повышение операционной скорости при использовании коррелированного подзапроса 288%

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

Для данной виртуальной машины , данной версии СУБД , в ходе данного плана нагрузочного тестирования:

1. Производительность СУБД при использовании коррелированного подзапроса кардинально выше, чем при использовании JOIN.💥

Принято считать, что коррелированные подзапросы — это зло, ведущее к проблемам N+1, а JOIN — панацея для производительности. Статья описывает проверку догмы в ходе нагрузочного тестирования, будучи увереным в результатах еще до старта экспериментов.

Результат ошеломил: в некоторых сценариях коррелированный подзапрос показал кардинальное превышение производительности над классическим JOIN. Это наглядный пример того, как теоретическая стоимость запроса, которую мы видим в EXPLAIN, может быть совершенно не релевантна при оценке реальной производительности системы в целом.

Статья — это очередное напоминание всем разработчикам и DBA: в мире СУБД нет абсолютных истин, а любое, даже самое «логичное» правило, нужно проверять экспериментально.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Задача

Провести экспериментальную проверку гипотезы о влиянии коррелированного запроса на производительность СУБД .

В этом тесте PostgreSQL 16 быстрее выполнил вариант с JOIN + GROUP BY: ~0.415 ms против ~0.803 ms для коррелированного подзапроса.

План JOIN: Hash Right Join + HashAggregate с одним проходом по таблицам — меньше итераций и накладных, чем у подзапроса.

План подзапроса: 25 запусков под-плана с Bitmap Scan по orders (классический N+1-эффект), поэтому медленнее.

Вывод: в PostgreSQL коррелированные подзапросы легко деградируют в N+1; предпочитайте set-based JOIN и проверяйте планы через EXPLAIN ANALYZE.

Источник:

JOIN vs. Коррелированный подзапрос: Разрушаем миф о «N+1» на 4 СУБД

Экспериментальная проверка гипотезы

Виртуальная машина

CPU = 8

RAM = 8GB

Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

Результаты нагрузочного тестирования

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

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

💣Результат нагрузочного тестирования

Для данной виртуальной машины , данной версии СУБД и данного характера нагрузки среднее снижение операционной скорости в ходе нагрузочного тестирования, для сценария использующего JOIN составило 188%.💥

Продолжение

Опасный мираж оптимизации: почему нейросетевые советы по СУБД PostgreSQL убивают производительность под нагрузкой.