ИИ как опасный советчик: Почему нейросетям нельзя доверять настройку производительности PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).

Аннотация

В статье проводится сравнительный анализ эффективности использования оператора 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. Заключение

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