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

LLM могут написать запрос, но не несут ответственности за его выполнение в 50 параллельных сессий.

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

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

Предисловие (стартовое событие эксперимента)

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

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

Тестовый запрос-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;

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

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 : с использованием коррелированного подзапроса

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;

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

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

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

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

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

"-- 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. План выполнения и эффективность

Запрос-1: LEFT JOIN + GROUP BY

• Преимущества:PostgreSQL может использовать индекс idx_orders_customer_id для эффективного соединения (Hash Join или Merge Join).
• GROUP BY на 25 строках (customers) — очень маленький набор, что делает агрегацию дешевой.
• Однопроходная обработка: один проход по orders с индексом, затем соединение с customers.
• Масштабируемость: при увеличении числа сессий, параллельные планы выполнения (PostgreSQL 17 поддерживает параллельные JOIN и GROUP BY) могут эффективно распределить нагрузку.
• Оптимизатор PostgreSQL 17 хорошо оптимизирует такие запросы — особенно при наличии индекса.

Запрос-2: Коррелированный подзапрос

• Недостатки:Для каждой строки из customers (25 строк) выполняется отдельный подзапрос.
• Каждый подзапрос: SELECT COUNT(o.order_id) FROM orders WHERE customer_id = ? — использует индекс, но 25 раз.
• Это 25 отдельных запросов к индексу, каждый из которых требует:Поиск по индексу (Index Only Scan)
• Подсчет строк
• Нет параллелизма между подзапросами — они выполняются последовательно в рамках одного запроса.
• Высокая фиксированная стоимость: даже при малом числе клиентов, количество операций растет линейно с числом клиентов, а не с числом заказов.
• При 45 сессиях — 45 × 25 = 1125 отдельных подзапросов к индексу в секунду, что создает конкуренцию за индексные блоки и может вызвать блокировки/ожидания.

Важно: PostgreSQL 17 улучшил оптимизацию коррелированных подзапросов, но они не превращаются в JOIN автоматически. Оптимизатор не "переписывает" подзапрос в JOIN, если он не уверен в эквивалентности (особенно при COUNT, NULL-обработке и т.п.).

2. Влияние нагрузки (10–45 сессий)

3. Практические тесты и документация

Согласно документации PostgreSQL 17 (используем get_postgresql_docs для подтверждения):

На основе анализа документации PostgreSQL 17 и сравнения двух запросов, можно сделать однозначный вывод:

✅ Производительность будет выше при использовании тестового запроса-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 → много контекстных переключений.
• Ограничение параллелизма — подзапросы выполняются последовательно внутри одного запроса.
• Даже при наличии индекса, количество операций растёт линейно с числом сессий — это экспоненциальная нагрузка на систему.

Документация PostgreSQL 17:

«Correlated subqueries are not inherently bad, but they can be less efficient than equivalent JOINs» — и не преобразуются в JOIN при наличии агрегаций (COUNT()).

Вывод

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

Для нагрузочных тестов с растущей параллельностью всегда используйте 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%

Ожидания Timeout/SpinDelay

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

Анализ метрик производительности инфраструктуры

Корреляция ожиданий СУБД и метрик vmstat

Чек-лист CPU

Процессы : r — количество процессов, которые находятся в состоянии ожидания (незанятые и готовые к выполнению).

Среднее увеличение процессов в состоянии ожидания, при использовании JOIN, в ходе нагрузочного тестирования составляет 14%.

CPU : us — процент циклов CPU, потраченных на пользовательские процессы.

Среднее увеличение процента циклов CPU, потраченных на пользовательские процессы, при использовании JOIN, в ходе нагрузочного тестирования составляет 17%.

CPU : sy — процент циклов CPU, потраченных на системные (ядро) процессы.

Среднее увеличение процента циклов CPU, потраченных на системные процессы, при использовании коррелированного подзапроса, в ходе нагрузочного тестирования составляет 122%.

Система : cs — количество переключений контекста в секунду.

Среднее увеличение количества переключений контекста , при использовании коррелированного подзапроса, в ходе нагрузочного тестирования составляет 233%.

Система : in — количество прерываний в секунду.

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

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

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

1. Производительность СУБД при использовании коррелированного подзапроса кардинально выше, чем при использовании JOIN.💥
2. Использование JOIN существенно повышает пользовательскую нагрузку на CPU.ℹ️
3. С ростом нагрузки - при использовании коррелированного подзапроса количество ожиданий SpinDelay снижается, при использовании JOIN - возрастает.
4. С ростом нагрузки - при использовании коррелированного подзапроса количество прерываний снижается, при использовании JOIN - возрастает.