Postgres DBA

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

В этой статье показаны результаты нагрузочного тестирования PostgreSQL в условиях нагрузочного тестирования, которые привели к парадоксальному на первый взгляд выводу. Когда количество одновременных операций растет, а данные активно читаются, дорогостоящие индексы могут не справиться, превратившись из помощников во вредителей. И как оказалось, старый добрый Seq Scan — метод полного сканирования таблицы — неожиданно стал в итоге самым эффективным решением в этом сценарии.

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

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

CREATE TABLE pgbench_test

(

aid integer PRIMARY KEY ,

bid integer,

abalance integer,

filler character(84)

);

ALTER TABLE pgbench_test ADD CONSTRAINT "pgbench_test_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid);

INSERT INTO pgbench_test ( aid , bid , abalance , filler )

SELECT

id ,

floor(random() * 685 ) + 1 ,

floor(random() * (68500000 - 1 + 1)) + 1 ,

md5(random()::text)

FROM generate_series(1,1000000) id;

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

select test.abalance

from pgbench_accounts acc

join pgbench_test test on (test.bid = acc.bid )

where acc.aid = current_aid ;

Эксперимент-1 : Метод доступа Seq Scan

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

Стоимость плана = 21620.62

Gather (cost=1002.80..21620.62 rows=1460 width=4) (actual time=4.088..329.017 rows=1468 loops=1)

Workers Planned: 3

Workers Launched: 3

-> Hash Join (cost=2.80..20474.62 rows=471 width=4) (actual time=1.821..300.589 rows=367 loops=4)

Hash Cond: (test.bid = acc.bid)

-> Parallel Seq Scan on pgbench_test test (cost=0.00..19619.81 rows=322581 width=8) (actual time=0.692..263.390 rows=250000 loops=4)

-> Hash (cost=2.79..2.79 rows=1 width=4) (actual time=0.488..0.489 rows=1 loops=4)

Buckets: 1024 Batches: 1 Memory Usage: 9kB

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.472..0.474 rows=1 loops=4)

Index Cond: (aid = 51440641)

Planning Time: 1.977 ms

Execution Time: 329.301 ms

Эксперимент-2 : Метод доступа Bitmap Index Scan

CREATE INDEX pgbench_test_idx ON pgbench_test ( bid );

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

Стоимость плана = 1546.55

Nested Loop (cost=14.51..1546.55 rows=1460 width=4) (actual time=0.894..366.050 rows=1468 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.217..0.227 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Bitmap Heap Scan on pgbench_test test (cost=13.94..1529.17 rows=1460 width=8) (actual time=0.669..365.312 rows=1468 loops=1)

Recheck Cond: (bid = acc.bid)

Heap Blocks: exact=1407

-> Bitmap Index Scan on pgbench_test_idx (cost=0.00..13.57 rows=1460 width=0) (actual time=0.355..0.356 rows=1468 loops=1)

Index Cond: (bid = acc.bid)

Planning Time: 2.634 ms

Execution Time: 366.419 ms

Эксперимент-3 : Метод доступа Index Only Scan

CREATE INDEX idx_pgbench_test_bid_abalance ON pgbench_test(bid) INCLUDE (abalance);

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

Стоимость плана = 48.86

Nested Loop (cost=0.99..48.86 rows=1460 width=4) (actual time=0.771..1.186 rows=1432 loops=1)

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4) (actual time=0.736..0.737 rows=1 loops=1)

Index Cond: (aid = 51440641)

-> Index Only Scan using idx_pgbench_test_bid_abalance on pgbench_test test (cost=0.42..31.47 rows=1460 width=8) (actual time=0.029..0.289 rows=1432 loops=1)

Index Cond: (bid = acc.bid)

Heap Fetches: 0

Planning Time: 4.949 ms

Execution Time: 1.302 ms

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

Результаты экспериментов:

1. До 8-ми соединений - наибольшая производительность получена с использованием метода доступа Bitmap Index Scan.

2. После 8-ми соединений и до 22-х - наибольшая производительность получена с использованием метода доступа Seq Scan.

Причины эффективности Seq Scan при параллельной нагрузке

1. Увеличение нагрузки на подсистему ввода-вывода

• Без индекса: Sequential Scan читает данные большими последовательными блоками

• С индексом: Index Scans создают случайный доступ к диску

2. Конкуренция за буферный кэш

• Индекс занимает место в shared_buffers

• Вытесняет полезные данные из кэша

• Каждое соединение читает разные части индекса → больше промахов кэш

3. Блокировки в системных каталогах

• При использовании индекса PostgreSQL обращается к системным каталогам

• Увеличивается конкуренция за pg_index, pg_class

• Особенно заметно при многих одновременных соединениях

4. CPU overhead

• Обработка индекса требует больше CPU операций

• Bitmap Index Scan + Bitmap Heap Scan сложнее чем простой Seq Scan

• При росте соединений CPU становится узким местом

Предисловие

В сценариях с параллельными запросами и острой конкуренцией за ресурсы паттерн EXISTS показал себя как однозначно более эффективное решение для PostgreSQL.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Эксперименты с Демобазой 2.0

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

Начало экспериментов "IN vs EXISTS"

Очередной раунд тестов: EXISTS против IN в условиях параллелизма.

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

SELECT DISTINCT a.country

FROM airports_data a

JOIN routes r ON (r.arrival_airport = a.airport_code)

WHERE duration IN

(

'09:45:00' , '11:50:00' , '02:40:00' , '05:50:00' , '15:25:00' , '04:30:00' , '11:00:00' , '07:15:00' , '12:40:00' , '03:40:00' , '05:15:00' , '08:35:00' , '10:35:00' , '07:30:00' , '09:35:00' , '04:15:00' , '11:45:00' , '04:05:00' , '01:10:00' , '19:50:00' , '07:55:00' , '01:35:00' , '16:05:00' , '08:15:00' , '04:00:00' , '08:45:00' , '12:25:00' , '16:40:00' , '07:25:00' , '01:50:00' , '14:35:00' , '12:45:00' , '01:20:00' , '02:55:00' , '20:20:00' , '10:45:00' , '02:45:00' , '12:55:00' , '08:25:00' , '00:45:00' , '02:00:00' , '01:15:00' , '08:00:00' , '04:10:00' , '11:35:00' , '16:45:00' , '17:15:00' , '14:40:00' , '15:35:00' , '15:50:00' , '13:30:00' , '04:25:00' , '01:25:00' , '14:10:00' , '15:15:00' , '08:55:00' , '07:00:00' , '05:05:00' , '06:45:00' , '14:20:00' , '09:50:00' , '08:10:00' , '11:30:00' , '13:45:00' , '04:35:00' , '01:30:00' , '15:10:00' , '05:25:00' , '05:20:00' , '16:30:00' , '14:45:00' , '00:40:00' , '13:15:00' , '12:50:00' , '09:05:00' , '17:30:00' , '13:05:00' , '13:10:00' , '10:50:00' , '07:10:00' , '05:00:00' , '10:40:00' , '03:25:00' , '09:00:00' , '13:00:00' , '10:20:00' , '16:20:00' , '08:05:00' , '07:40:00' , '14:30:00' , '16:10:00' , '03:50:00' , '08:30:00' , '05:40:00' , '06:20:00' , '05:30:00' , '11:05:00' , '11:55:00' , '04:20:00' , '06:40:00' );

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

HashAggregate (cost=282.56..284.86 rows=230 width=54) (actual time=5.604..5.609 rows=17 loops=1)

Group Key: a.country

Batches: 1 Memory Usage: 40kB

-> Nested Loop (cost=0.54..276.22 rows=2534 width=54) (actual time=0.104..3.559 rows=2534 loops=1)

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.049..1.650 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:55:00,01:35:

00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:00,14:40:00,

15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,12:50:00,09:

05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:40:00}'::int

erval[]))

Rows Removed by Filter: 3258

-> Memoize (cost=0.29..0.39 rows=1 width=58) (actual time=0.000..0.000 rows=1 loops=2534)

Cache Key: r.arrival_airport

Cache Mode: logical

Hits: 2461 Misses: 73 Evictions: 0 Overflows: 0 Memory Usage: 11kB

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..0.38 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

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

SELECT DISTINCT a.country

FROM airports_data a

WHERE EXISTS (

SELECT 1

FROM routes r

WHERE r.arrival_airport = a.airport_code

AND r.duration IN (

'09:45:00', '11:50:00', '02:40:00', '05:50:00', '15:25:00', '04:30:00', '11:00:00', '07:15:00',

'12:40:00', '03:40:00', '05:15:00', '08:35:00', '10:35:00', '07:30:00', '09:35:00', '04:15:00',

'11:45:00', '04:05:00', '01:10:00', '19:50:00', '07:55:00', '01:35:00', '16:05:00', '08:15:00',

'04:00:00', '08:45:00', '12:25:00', '16:40:00', '07:25:00', '01:50:00', '14:35:00', '12:45:00',

'01:20:00', '02:55:00', '20:20:00', '10:45:00', '02:45:00', '12:55:00', '08:25:00', '00:45:00',

'02:00:00', '01:15:00', '08:00:00', '04:10:00', '11:35:00', '16:45:00', '17:15:00', '14:40:00',

'15:35:00', '15:50:00', '13:30:00', '04:25:00', '01:25:00', '14:10:00', '15:15:00', '08:55:00',

'07:00:00', '05:05:00', '06:45:00', '14:20:00', '09:50:00', '08:10:00', '11:30:00', '13:45:00',

'04:35:00', '01:30:00', '15:10:00', '05:25:00', '05:20:00', '16:30:00', '14:45:00', '00:40:00',

'13:15:00', '12:50:00', '09:05:00', '17:30:00', '13:05:00', '13:10:00', '10:50:00', '07:10:00',

'05:00:00', '10:40:00', '03:25:00', '09:00:00', '13:00:00', '10:20:00', '16:20:00', '08:05:00',

'07:40:00', '14:30:00', '16:10:00', '03:50:00', '08:30:00', '05:40:00', '06:20:00', '05:30:00',

'11:05:00', '11:55:00', '04:20:00', '06:40:00'

));

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

Unique (cost=299.91..300.27 rows=73 width=54) (actual time=3.071..3.114 rows=17 loops=1)

-> Sort (cost=299.91..300.09 rows=73 width=54) (actual time=3.069..3.076 rows=73 loops=1)

Sort Key: a.country

Sort Method: quicksort Memory: 25kB

-> Nested Loop (cost=191.75..297.65 rows=73 width=54) (actual time=2.457..2.942 rows=73 loops=1)

-> HashAggregate (cost=191.47..192.19 rows=73 width=4) (actual time=2.408..2.421 rows=73 loops=1)

Group Key: r.arrival_airport

Batches: 1 Memory Usage: 24kB

-> Seq Scan on routes r (cost=0.25..185.13 rows=2534 width=4) (actual time=0.048..1.834 rows=2534 loops=1)

Filter: (duration = ANY ('{09:45:00,11:50:00,02:40:00,05:50:00,15:25:00,04:30:00,11:00:00,07:15:00,12:40:00,03:40:00,05:15:00,08:35:00,10:35:00,07:30:00,09:35:00,04:15:00,11:45:00,04:05:00,01:10:00,19:50:00,07:

55:00,01:35:00,16:05:00,08:15:00,04:00:00,08:45:00,12:25:00,16:40:00,07:25:00,01:50:00,14:35:00,12:45:00,01:20:00,02:55:00,20:20:00,10:45:00,02:45:00,12:55:00,08:25:00,00:45:00,02:00:00,01:15:00,08:00:00,04:10:00,11:35:00,16:45:00,17:15:

00,14:40:00,15:35:00,15:50:00,13:30:00,04:25:00,01:25:00,14:10:00,15:15:00,08:55:00,07:00:00,05:05:00,06:45:00,14:20:00,09:50:00,08:10:00,11:30:00,13:45:00,04:35:00,01:30:00,15:10:00,05:25:00,05:20:00,16:30:00,14:45:00,00:40:00,13:15:00,

12:50:00,09:05:00,17:30:00,13:05:00,13:10:00,10:50:00,07:10:00,05:00:00,10:40:00,03:25:00,09:00:00,13:00:00,10:20:00,16:20:00,08:05:00,07:40:00,14:30:00,16:10:00,03:50:00,08:30:00,05:40:00,06:20:00,05:30:00,11:05:00,11:55:00,04:20:00,06:

40:00}'::interval[]))

Rows Removed by Filter: 3258

-> Index Scan using airports_data_pkey on airports_data a (cost=0.28..1.46 rows=1 width=58) (actual time=0.006..0.006 rows=1 loops=73)

Index Cond: (airport_code = r.arrival_airport)

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

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

Среднее превышение операционной скорости при использовании EXISTS составило 23.25%.

Ожидания СУБД

Среднее снижение ожиданий СУБД при использовании EXISTS составило 90.60%.

Итог

Использование паттерна EXIST повышает производительность СУБД в среднем на 20%.

Предисловие

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

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Методология эксперимента

Для тестирования прогностической способности нейросети была развернута тестовая среда PostgreSQL 17 с конфигурацией CPU=8 ядер, RAM=8GB. Создана таблица pgbench_test с 1 млн записей, выполнялся запрос с соединением по внешнему ключу. Анализировались два метода доступа: последовательное сканирование (Seq Scan) и индексное сканирование (Index Only Scan) с покрывающим индексом idx_pgbench_test_bid_abalance.

Подробности эксперимента(прогноз и анализ нейросети)

Использование нейросети для прогноза производительности СУБД PostgreSQL

Прогноз нейросети и его несоответствие реальности

Нейросеть, проанализировав планы выполнения запросов, выдала категоричный прогноз:

• Index Only Scan: оптимальная производительность (~2.5 ms) даже при 22 параллельных сессиях

• Seq Scan: катастрофическая деградация производительности (+400%) при превышении 10 сессий

Реальные результаты нагрузочного тестирования показали принципиально иную картину:

• Среднее преимущество Seq Scan составило 9%

• В самой финальной фазе теста Index Only Scan демонстрировал лучшую производительность

• Оба метода показали сопоставимую устойчивость к нагрузке

Критический анализ ошибок нейросетевого прогноза

1. Неспособность моделировать динамическое кэширование

Нейросеть основывалась на статической стоимостной модели, игнорируя эффект прогрева БД. В реальности Seq Scan выигрывал от полного размещения данных в shared_buffers, что нивелировало его основной недостаток - физический I/O.

2. Игнорирование конкуренции за индексные структуры

Модель недооценила contention в B-деревьях при высокой параллельности. Index Only Scan, предсказанный как идеальное решение, столкнулся с блокировками страниц индекса при одновременном доступе 22 сессий.

3. Ошибочная оценка масштабируемости

Нейросеть переоценила линейность индексного доступа и недооценила эффективность параллельного Seq Scan. Распределенная нагрузка workers оказалась стабильнее концентрированной нагрузки на индекс.

Системные ограничения нейросетей в экспертизе СУБД

Проведенный эксперимент выявил фундаментальные проблемы применения AI для анализа производительности БД:

Статичность моделей - нейросети работают с моментальными снимками системы, не учитывая временные аспекты работы СУБД.

Игнорирование конкурентного доступа - модели не способны адекватно предсказать поведение системы при одновременном доступе множества процессов.

Неучет аппаратных ограничений - прогноз не учитывал реальные особенности управления памятью и планирования задач в PostgreSQL.

Рекомендации и выводы

Для эффективного использования нейросетей в экспертизе производительности PostgreSQL необходимо:

1. Обогащение данных обучения динамическими метриками (pg_stat_, pg_statio_)

2. Учет временны́х характеристик - продолжительности теста, эффекта прогрева

3. Моделирование реальной конкуренции - тестирование в условиях, приближенных к продуктивным.

4. Верификация прогнозов обязательным нагрузочным тестированием

Заключение

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

Результаты новых исследований указывают на необходимость создания отдельного направления по оптимизации параллельных процессов в СУБД PostgreSQL

Казань, 21.11.2025 – По результатам серии экспериментов, проведенных, был выявлен фундаментальный пробел в современных методологиях оптимизации производительности систем управления базами данных (СУБД). Установлено, что традиционные паттерны и методики оптимизации демонстрируют резкое снижение эффективности или полную неприменимость в средах с высоким уровнем параллельной обработки транзакций.

Эмпирические данные свидетельствуют о том, что при значительной конкурентной нагрузке, когда множество процессов обращаются к данным одновременно, классические подходы, такие как тонкая настройка отдельных запросов или индексация, оказываются недостаточными. Вместо ожидаемого линейного роста производительности наблюдаются нелинейные эффекты, включая интенсивную борьбу за ресурсы (contention), блокировки (locks) и деградацию общей пропускной способности системы.

На основании полученных результатов был сделан вывод о назревшей необходимости системного пересмотра принципов анализа и оптимизации СУБД. Для обеспечения устойчивой работы высоконагруженных информационных систем на базе СУБД PostgreSQL требуется выделение и глубокая проработка нового специализированного подраздела, посвященного исключительно оптимизации параллельных процессов (Parallel Processes Optimization).

Введение данной дисциплины предполагает фокусировку на таких аспектах, как:

• Анализ и минимизация конфликтов блокировок на уровне строк и таблиц.

• Оптимизация работы планировщика задач и управления памятью в условиях высокой конкуренции.

• Разработка специализированных метрик для диагностики узких мест, специфичных для параллельной работы.

• Создание рекомендаций по проектированию схемы данных и логики приложений, ориентированных на параллелизм.

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

Контактная информация :

• Ринат Сунгатуллин

• kznalp@yandex.ru

• Postgres DBA

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Аннотация

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

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