Предисловие

В сценариях с параллельными запросами и острой конкуренцией за ресурсы паттерн 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

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории 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 или Коррелированного подзапроса - не оказывает влияния на производительность СУБД в целом и тестового сценария в частности.

Принято считать, что коррелированные подзапросы — это зло, ведущее к проблемам 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 убивают производительность под нагрузкой.

Без нагрузочного тестирования, максимально приближенного к продуктивной среде, любые выводы об эффективности индексов остаются лишь предположениями.

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

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

kznalp/PG_EXPECTO

pg_expecto

Характерные признаки неэффективности индекса

В PostgreSQL нет специфических wait events, которые прямо указывают на ненужность индекса, но следующие признаки в планах выполнения и статистике могут сигнализировать о проблеме:

1. Высокая стоимость обслуживания индекса

• Wait Events, связанные с записью на диск (например, WALWrite, BgWriterHibernate), могут участиться из-за частых обновлений индекса при INSERT/UPDATE/DELETE.

• В планах DML-запросов значительные затраты на Index Updates (строки -> Index Insert, -> Index Delete).

2. Низкая эффективность индекса

• Bitmap Index Scan с последующим Bitmap Heap Scan:

• Если Rows Removed by Index Recheck велико, индекс неточно фильтрует данные.

• Высокое значение Heap Blocks Fetched указывает на много случайных чтений.

• Index Scan с большим Actual Loops и высоким Cost по сравнению с Seq Scan.

3. Избыточность индекса

• Если индекс используется, но в плане появляется Sort или Group, хотя индекс должен обеспечивать порядок (например, для ORDER BY). Это может означать неоптимальность порядка колонок в индексе.

• Наличие нескольких индексов с пересекающимися колонками, где один индекс заменяет другой.

4. Статистика использования

• Запрос к pg_stat_user_indexes показывает низкое значение idx_scan при высоких idx_tup_read и idx_tup_fetch — индекс читает много строк, но редко используется.

5. Размер индекса

• Индекс занимает больше места, чем сама таблица (pg_relation_size), и не дает преимуществ в производительности.

Примеры неэффективных индексов:

• Индексы на колонки с малым количеством уникальных значений (например, boolean).

• Частичные индексы с избыточными условиями.

• Индексы, дублирующие функциональность других индексов.

ℹ️Конкретные признаки в планах выполнения и wait events, указывающие на неэффективность индекса по сравнению с Seq Scan:

1. Высокий процент отфильтрованных строк

-- Если индекс отбирает >5-10% таблицы, он часто проигрывает Seq Scan

Index Scan using idx_name on table (cost=0.43..1254.32 rows=50000 width=8)

Index Cond: (status = 'active')

-- rows=50000 при общем размере таблицы 100000 строк = 50% - слишком много для индекса

2. Большое количество Heap Fetches

Bitmap Heap Scan on orders (cost=184.55..17524.82 rows=8822 width=45)

Recheck Cond: (customer_id = 123)

Heap Blocks: exact=4200 -- Слишком много блоков таблицы прочитано

-> Bitmap Index Scan on idx_orders_customer_id

3. Низкая селективность в Bitmap Index Scan

Bitmap Index Scan on idx_low_selectivity (cost=0.00..1123.45 rows=80000 width=0)

Index Cond: (flag = true) -- Индекс на boolean поле обычно неэффективен

Количественные показатели неэффективности:

1. Сравнение стоимости в плане

-- Плохой случай: индекс дороже последовательного сканирования

Index Scan: (cost=0.43..2500.00 rows=45000)

Seq Scan: (cost=0.00..1500.00 rows=45000) -- Дешевле!

2. Статистика из pg_stat_user_indexes

-- Низкая эффективность индекса

SELECT schemaname, tablename, indexname,

idx_scan, idx_tup_read, idx_tup_fetch,

-- Эффективность: сколько строк возвращается на одно сканирование

CASE WHEN idx_scan > 0

THEN round(idx_tup_read::numeric / idx_scan, 2)

ELSE 0 END as tuples_per_scan

FROM pg_stat_user_indexes

WHERE idx_tup_read::numeric / idx_scan > 10000; -- Слишком много строк на сканирование

ℹ️Типичные сценарии неэффективных индексов:

1. Индексы на низкоселективные колонки

-- Индекс на поле с 2-3 значениями

CREATE INDEX idx_gender ON users(gender); -- 'M', 'F', NULL2. Неправильный порядок колонок в составном индексе

-- Запрос: WHERE status = 'active' AND created_at > '2023-01-01'

CREATE INDEX idx_created_status ON orders(created_at, status); -- Неоптимально

CREATE INDEX idx_status_created ON orders(status, created_at); -- Оптимально

3. Индексы на часто обновляемые таблицы

-- На таблице с частыми INSERT/UPDATE индекс может замедлять запись

UPDATE sessions SET last_activity = NOW() WHERE user_id = 123;

-- Каждое обновление требует изменения индекса

Диагностика:

1. Сравнение стоимости индекса vs seq scan

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM table WHERE indexed_column = 'value';

-- Затем принудительно отключите индекс для сравнения:

SET enable_indexscan = off;

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM table WHERE indexed_column = 'value';

RESET enable_indexscan;

2. Анализ распределения данных

-- Селективность индекса

SELECT indexed_column, count(*),

round(100.0 * count(*) / (SELECT count(*) FROM table), 2) as pct

FROM table

GROUP BY indexed_column

ORDER BY count DESC;

Когда индекс становится невыгодным:

• 👍Селективность < 5% - обычно выгоден индекс

• Селективность 5-20% - зависит от размера таблицы и распределения данных

• Селективность > 20% - обычно выгоден Seq Scan

• Маленькие таблицы (< 1000 строк) - индексы обычно не нужны

• Частые массовые обновления - стоимость поддержки индекса может превышать пользу

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

Предисловие

В эпоху повсеместного увлечения искусственным интеллектом многие пытаются использовать нейросети в качестве экспертных систем для оптимизации производительности СУБД. Эта статья — трезвый взгляд на опасность слепого доверия к AI-предсказаниям в критически важных областях управления базами данных.

На конкретном примере двух альтернативных запросов к PostgreSQL мы продемонстрируем, как нейросеть, анализируя планы выполнения и стоимость запросов, сформировала убедительную, но абсолютно ложную гипотезу о 85-95% превосходстве одного плана над другим. Реальное нагрузочное тестирование при растущей параллельной нагрузке (от 5 до 22 соединений) показало совершенно иную картину, опровергающую все теоретические выкладки.

Эта статья — предостережение для DBA и разработчиков: аппроксимация результатов и анализ стоимости планов не могут заменить реальные эксперименты в условиях, приближенных к производственным. Нейросети остаются ценным инструментом, но не истиной в последней инстанции, когда дело касается производительности PostgreSQL под нагрузкой.

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

PG_EXPECTO

pg_expecto

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

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

Дано: СУБД с ресурсами CPU=8, RAM=8GB , PostgreSQL 17.

Изменение нагрузки по итерациям:

1 итерация - 5 параллельных соединений

2 итерация - 8 параллельных соединений

3 итерация - 9 параллельных соединений

4 итерация - 10 параллельных соединений

5 итерация - 12 параллельных соединений

6 итерация - 13 параллельных соединений

7 итерация - 15 параллельных соединений

8 итерация - 18 параллельных соединений

9 итерация - 22 параллельных соединений.

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

"

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

FROM overbook;

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

"

Aggregate (cost=9825.94..9825.95 rows=1 width=40) (actual time=262.702..262.707 rows=1 loops=1)

-> Hash Join (cost=9431.95..9825.94 rows=1 width=0) (actual time=262.696..262.701 rows=0 loops=1)

Hash Cond: ((f.route_no = r.route_no) AND (seats.airplane_code = r.airplane_code))

Join Filter: (r.validity @> f.scheduled_departure)

Rows Removed by Join Filter: 217

-> Nested Loop (cost=9407.50..9796.79 rows=567 width=19) (actual time=218.641..259.306 rows=11355 loops=1)

-> Hash Join (cost=9407.22..9623.25 rows=567 width=8) (actual time=218.539..235.320 rows=11355 loops=1)

Hash Cond: (segments.fare_conditions = seats.fare_conditions)

Join Filter: ((count(*)) > (count(*)))

Rows Removed by Join Filter: 66545

-> HashAggregate (cost=9366.21..9507.87 rows=14166 width=20) (actual time=217.266..219.770 rows=10888 loops=1)

Group Key: segments.flight_id, segments.fare_conditions

Batches: 1 Memory Usage: 1425kB

-> Seq Scan on segments (cost=0.00..6654.55 rows=361555 width=12) (actual time=0.071..90.350 rows=361489 loops=1)

-> Hash (cost=40.71..40.71 rows=24 width=20) (actual time=1.228..1.230 rows=20 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 10kB

-> HashAggregate (cost=40.47..40.71 rows=24 width=20) (actual time=1.205..1.211 rows=20 loops=1)

Group Key: seats.airplane_code, seats.fare_conditions

Batches: 1 Memory Usage: 24kB

-> Seq Scan on seats (cost=0.00..27.41 rows=1741 width=12) (actual time=0.059..0.420 rows=1741 loops=1)

-> Index Scan using flights_pkey on flights f (cost=0.28..0.31 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=11355)

Index Cond: (flight_id = segments.flight_id)

-> Hash (cost=15.78..15.78 rows=578 width=33) (actual time=0.631..0.632 rows=578 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 45kB

-> Seq Scan on routes r (cost=0.00..15.78 rows=578 width=33) (actual time=0.083..0.375 rows=578 loops=1)

Planning Time: 5.779 ms

Execution Time: 263.774 ms

"

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

"

SELECT

COUNT(*) AS overbookings,

CASE WHEN COUNT(*) > 0 THEN 'ERROR: overbooking' ELSE 'Ok' END AS verdict

FROM (

SELECT

f.flight_id,

sb.fare_conditions,

sb.seats_cnt AS seats_booked,

(

SELECT COUNT(*)

FROM bookings.seats s

WHERE s.airplane_code = r.airplane_code

AND s.fare_conditions = sb.fare_conditions

) AS seats_available

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_cnt

FROM bookings.segments

GROUP BY flight_id, fare_conditions

) sb ON sb.flight_id = f.flight_id

WHERE sb.seats_cnt > (

SELECT COUNT(*)

FROM bookings.seats s

WHERE s.airplane_code = r.airplane_code

AND s.fare_conditions = sb.fare_conditions

)

) overbook;

"

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

"

Aggregate (cost=334506.18..334506.19 rows=1 width=40) (actual time=12894.579..12899.785 rows=1 loops=1)

CTE seats_agg

-> HashAggregate (cost=40.47..40.71 rows=24 width=20) (actual time=0.751..0.757 rows=20 loops=1)

Group Key: seats.airplane_code, seats.fare_conditions

Batches: 1 Memory Usage: 24kB

-> Seq Scan on seats (cost=0.00..27.41 rows=1741 width=12) (actual time=0.024..0.203 rows=1741 loops=1)

-> Hash Join (cost=326910.78..334463.31 rows=862 width=0) (actual time=12894.575..12899.777 rows=0 loops=1)

Hash Cond: (segments.flight_id = f.flight_id)

Join Filter: ((count(*)) > (SubPlan 2))

Rows Removed by Join Filter: 249660

-> Finalize HashAggregate (cost=315588.67..318101.77 rows=251310 width=20) (actual time=4473.982..4907.592 rows=249660 loops=1)

Group Key: segments.flight_id, segments.fare_conditions

Batches: 1 Memory Usage: 28177kB

-> Gather (cost=1000.44..308049.37 rows=1005240 width=20) (actual time=18.306..4125.482 rows=253416 loops=1)

Workers Planned: 4

Workers Launched: 4

-> Partial GroupAggregate (cost=0.44..206525.37 rows=251310 width=20) (actual time=0.606..4288.777 rows=50683 loops=5)

Group Key: segments.flight_id, segments.fare_conditions

-> Parallel Index Only Scan using idx_segments_flight_fare on segments (cost=0.44..167521.05 rows=4865495 width=12) (actual time=0.072..2456.531 rows=3892859 loops=5)

Heap Fetches: 271389

-> Hash (cost=11309.28..11309.28 rows=1026 width=8) (actual time=4365.785..4365.789 rows=99609 loops=1)

Buckets: 131072 (originally 2048) Batches: 1 (originally 1) Memory Usage: 4915kB

-> Nested Loop (cost=0.42..11309.28 rows=1026 width=8) (actual time=0.061..4277.802 rows=99609 loops=1)

-> Seq Scan on routes r (cost=0.00..148.02 rows=5502 width=33) (actual time=0.019..1.508 rows=5502 loops=1)

-> Index Scan using flights_route_no_scheduled_departure_key on flights f (cost=0.42..2.02 rows=1 width=19) (actual time=0.408..0.772 rows=18 loops=5502)

Index Cond: (route_no = r.route_no)

Filter: (r.validity @> scheduled_departure)

Rows Removed by Filter: 168

SubPlan 2

-> CTE Scan on seats_agg sa (cost=0.00..0.60 rows=1 width=8) (actual time=0.005..0.011 rows=1 loops=249660)

Filter: ((airplane_code = r.airplane_code) AND (fare_conditions = segments.fare_conditions))

Rows Removed by Filter: 19

Planning Time: 5.223 ms

Execution Time: 12929.075 ms

"

Сформулируй и обоснуй гипотезу - на сколько в процентном отношении будет отличатся производительность СУБД при использовании тестового запроса №2 по сравнению с использованием тестового запроса №1 ?

Ответ нейросети DeepSeek

На основании анализа планов выполнения и условий тестирования, сформулирую гипотезу о влиянии плана выполнения на производительность.

Гипотеза

Производительность тестового запроса №2 будет на 85-95% хуже (медленнее) по сравнению с тестовым запросом №1 в условиях параллельной нагрузки, причем этот разрыв будет увеличиваться с ростом количества соединений.

Обоснование гипотезы

1. Анализ планов выполнения

Запрос №1 (263.774 ms):

• Использует эффективные хэш-соединения

• Минимальное количество операций вложенных циклов

• Оптимальная работа с агрегациями

• Отсутствие параллельных операций, что снижает конкуренцию за ресурсы

Запрос №2 (12929.075 ms - в 49 раз медленнее):

• Использует дорогостоящие параллельные операции (Gather, Workers)

• Множественные вложенные циклы и подзапросы

• Высокая стоимость повторяющихся операций (SubPlan выполняется 249660 раз)

• Значительное использование памяти (Memory Usage: 28177kB)

2. Влияние параллельной нагрузки

С учетом ресурсов системы (CPU=8, RAM=8GB):

• При 5-8 соединениях: Запрос №2 будет значительно медленнее из-за дорогостоящих параллельных операций, но система еще не насыщена

• При 9-12 соединениях: Конкуренция за CPU усилится, параллельные операции в запросе №2 начнут конкурировать между собой и с другими сессиями

• При 13-22 соединениях: Система достигнет насыщения CPU, что катастрофически скажется на производительности запроса №2

3. Прогнозируемая динамика производительности

4. Критические факторы

1. Конкуренция за CPU: Параллельные операции в запросе №2 создают дополнительную нагрузку, которая усугубляется с ростом числа соединений

2. Использование памяти: Запрос №2 требует значительно больше памяти (28MB vs 1.4MB для агрегаций), что может привести к свопингу при высокой нагрузке

3. Блокировки ресурсов: Множественные вложенные операции увеличивают время удержания блокировок

4. Эффект каскадного замедления: При насыщении системы время выполнения запроса №2 будет расти непропорционально быстрее, чем у запроса №1

Заключение

Запрос №1 демонстрирует оптимальный план выполнения для условий высокой параллельной нагрузки, в то время как запрос №2 содержит ресурсоемкие операции, которые становятся "узким местом" при увеличении числа одновременных соединений. Разрыв в производительности будет составлять 85-95% в пользу запроса №1 и будет увеличиваться с ростом нагрузки на систему.

Ответ нейросети Ask Postgres

Для формирования и обоснования гипотезы о различии производительности между тестовым запросом №1 и тестовым запросом №2 при параллельной нагрузке, проанализируем предоставленные планы выполнения и их ключевые характеристики.

1. Сравнение производительности по данным выполнения

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

• Запрос №1:
  Execution Time = 263.774 ms

• Запрос №2:
  Execution Time = 12929.075 ms

Разница во времени выполнения:

\Delta T = 12929.075 - 263.774 = 12665.301 \text{ ms}

Процентное отличие производительности (как относительное замедление запроса №2 относительно запроса №1):

\text{Замедление (%)} = \left( \frac{12929.075}{263.774} - 1 \right) \times 100% \approx \left( 49.01 - 1 \right) \times 100% = 4801%

То есть запрос №2 выполняется в ~49 раз медленнее, чем запрос №1.

Гипотеза:
При использовании тестового запроса №2 производительность СУБД PostgreSQL 17 будет снижена на 4800–4900% по сравнению с запросом №1, даже при низкой нагрузке, и это замедление будет экспоненциально усиливаться при увеличении параллельных соединений из-за неэффективной структуры подзапросов и повторных сканирований.

2. Обоснование гипотезы

Ключевые причины разницы в производительности

A. Запрос №1 — эффективная структура с предварительной агрегацией

• Использует CTE (seats_available, seats_booked, overbook) для однократного вычисления агрегированных данных.

• Все JOINs выполняются на предварительно сгруппированных наборах данных (10888 и 20 строк).

• Hash Join и Nested Loop работают с малыми наборами данных.

• Нет повторных подзапросов — все данные вычислены один раз и кэшированы в хеш-таблицах.

• Память используется эффективно: Batches = 1, Memory Usage < 2 MB.

B. Запрос №2 — катастрофически неэффективная структура

• Подзапрос в WHERE (WHERE sb.seats_cnt > (SELECT COUNT(*) FROM seats ...)) выполняется для каждой строки результата JOIN (249660 раз!).

• Подзапрос ссылается на CTE seats_agg, который, хотя и вычисляется один раз, сканируется 249660 раз через CTE Scan — это O(n×m) сложность.

• Параллелизм не помогает: хотя используется 5 рабочих процессов (Workers Launched: 4), они не могут компенсировать экспоненциальный рост числа операций.

• Повторное сканирование routes и flights в подзапросе — 5502 × 18 = 99609 строк, что уже само по себе тяжело, но усугубляется подзапросом.

• Память: 28 MB на группировку, 4.9 MB на хеш — но это не главное; главное — количество операций.

• Heap Fetches: 271389 — указывает на высокую стоимость Index Only Scan из-за неоптимального доступа к данным.

C. Масштабируемость при параллельной нагрузке

• Запрос №1: линейная масштабируемость. При увеличении соединений с 5 до 22 — время растёт умеренно, так как все данные уже агрегированы.

• Запрос №2: квадратичная/экспоненциальная масштабируемость.
  Каждое новое соединение увеличивает количество одновременных выполнений подзапроса.
  При 22 соединениях — 22 × 249660 = ~5.5 млн подзапросов.
  Это приведёт к: Переполнению shared_buffers
  Конкуренции за CPU и I/O
  Росту времени выполнения до > 100 секунд (экстраполяция)
  

Пример экстраполяции:
При 22 соединениях, если каждый запрос занимает 12.9 сек, то общее время выполнения в системе (если все параллельны) может достигать 22 × 12.9 = 284 секунды.
При запросе №1 — 22 × 0.26 = 5.7 секунд.

3. Количественная гипотеза

Гипотеза:
При увеличении параллельных соединений с 5 до 22, производительность СУБД PostgreSQL 17 при использовании запроса №2 будет снижена на 4800–4900% по сравнению с запросом №1, и это замедление будет усиливаться квадратично с ростом нагрузки.

Обоснование в процентах:

• Базовое замедление (при 1 соединении):
  $$ \frac{12929}{263.774} \approx 49.01 \Rightarrow \text{4801% замедление} $$

• При параллельной нагрузке (22 соединения):Запрос №1: ~5.7 сек (22 × 0.263)
  Запрос №2: ~284.4 сек (22 × 12.929)
  Отношение: $ \frac{284.4}{5.7} \approx 49.9 $ → 4890% замедление
  

Вывод:
Гипотеза подтверждается: запрос №2 демонстрирует 4800–4900% замедление по сравнению с запросом №1, и это различие будет сохраняться и усиливаться при росте параллельной нагрузки.

4. Рекомендация

Запрос №1 — оптимален.
Запрос №2 — следует переписать в стиле №1, убрав подзапросы в WHERE и используя JOIN с предварительно агрегированными CTE.
Использование подзапросов в условиях WHERE с агрегацией — антипаттерн в PostgreSQL, особенно при параллелизме.

Источники подтверждения

• [PostgreSQL Documentation: Performance Tips — Avoid Subqueries in WHERE]

• [Postgres Pro: Optimizing Complex Queries with CTEs and Joins]

• [Why NOT to use correlated subqueries in WHERE]

Итог:
Гипотеза: Запрос №2 будет работать в 48–49 раз медленнее, чем запрос №1 — это 4800–4900% замедление.
Это не артефакт, а фундаментальная архитектурная ошибка в построении запроса.

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

Среднее снижение операционной скорости при использовании тестового запроса №2 составило 2.83%.

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

1. Некорректная интерпретация планов выполнения

План выполнения показывает потенциальную производительность, а не реальную под нагрузкой:

• cost - это оценка оптимизатора, а не реальное время

• План строится для изолированного выполнения, без учета конкуренции за ресурсы

• Отсутствуют данные о реальном влиянии на подсистемы ввода-вывода

2. Игнорирование эффектов параллельной нагрузки

Критические факторы, не учтенные в гипотезе:

Конкуренция за ресурсы:

• При 22 соединениях на 8 CPU возникает очередь выполнения

• Parallel workers в запросе №2 конкурируют между собой

• Увеличивается время ожидания блокировок

Проблемы с памятью:

• Запрос №2 использует 28MB для агрегаций

• При множественных выполнениях возникает давление на shared_buffers

• Возможен вытеснение кэша

3. Методологическая ошибка - экстраполяция без валидации

Гипотеза экстраполировала данные:

• От единичного выполнения → к параллельному

• Без учета нелинейной деградации производительности

• Игнорировала точку насыщения системы

4. Реальные результаты эксперимента

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

• Минимальную разницу в производительности между запросами

• Оба запроса упираются в общие ограничения системы

• Параллельные операции не дают преимущества под нагрузкой

5. Системные ограничения доминируют над различиями запросов

При параллельной нагрузке:

• Латентность дисковой подсистемы становится определяющим фактором

• Нагрузка на CPU выравнивает время выполнения разных запросов

• Эффект от оптимизации нивелируется общей нагрузкой

Ключевой вывод

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

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