Postgres DBA

Предисловие

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

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

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