Аннотация
В статье проводится сравнительный анализ эффективности использования оператора JOIN и коррелированного подзапроса в СУБД PostgreSQL в условиях высокой параллельной нагрузки. На основе экспериментальных данных опровергаются универсальные рекомендации систем искусственного интеллекта и выявляются ключевые причины ошибок нейросетевых моделей.
ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub
kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL
pg-expecto/pg_expecto: Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
1. Постановка задачи
Рассматривается вопрос выбора оптимального паттерна для выполнения запросов при высокой параллельной нагрузке на СУБД: использование JOIN или коррелированного подзапроса.
Были получены рекомендации от нейросетевых моделей:
«Ask Postgres»: Для нагрузочных тестов с растущей параллельностью всегда используйте версию с JOIN. Коррелированные подзапросы с агрегациями — плохая практика в сценариях с высокой конкуренцией.
«DeepSeek»: Для данного сценария производительность будет выше при использовании запроса с LEFT JOIN и GROUP BY.
2. Детали эксперимента
Полное описание эксперимента:
Опасный мираж оптимизации: почему нейросетевые советы по СУБД PostgreSQL убивают производительность под нагрузкой. | Postgres DBA | Дзен
Тестовый запрос с использованием JOINSELECT 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;План выполнения с использованием JOINHashAggregate (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Тестовый запрос с использованием коррелированного подзапроса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
Сравнение производительности СУБД в ходе нагрузочного тестирования
В ходе сравнительного нагрузочного тестирования была измерена операционная скорость СУБД при использовании оператора 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. Критические факторы, упускаемые нейросетями
Влияние на shared_buffers: Множественные последовательные сканирования вытесняют полезные данные из кэша.
Lock contention: Конкуренция за одни и те же ресурсы (блокировки).
Распределение операций ввода-вывода: Индексные чтения лучше распределены.
Параметры PostgreSQL: Значения work_mem, shared_buffers, random_page_cost и других настроек существенно влияют на результат.
5. Причины ошибок в рекомендациях нейросетевых моделей
Нейросети обучаются на синтетических или упрощённых данных, для которых характерны:
Небольшой объём наборов данных (TPC-H, TPC-DS).
Низкая параллельность запросов (1–10 соединений).
Идеализированные индексы.
Отсутствие блокировок и конкуренции за ресурсы (ЦП, ввод-вывод).
В результате модель вырабатывает универсальное правило «JOIN всегда лучше», которое не работает в реальных условиях высокой конкуренции.
6. Заключение
Рекомендации, сгенерированные нейросетями, основаны на общих эвристиках и не могут учитывать всех особенностей конкретной эксплуатационной среды. Они не заменяют глубокого анализа планов выполнения запросов и понимания архитектуры СУБД под нагрузкой. Проведенный эксперимент наглядно демонстрирует важность практического тестирования и невозможность слепого следования автоматизированным советам.
Тестовый запрос и идея экспериментов, взяты из статьи
https://habr.com/p/965482/
Источник: habr.com
