Postgres DBA

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

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

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

Сравнить производительность тестовых запросов в ходе нагрузочного тестирования

Тестовый запрос-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;

Тестовый запрос-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;

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

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

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

"-- Create the customers table

CREATE TABLE customers (

customer_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL );

-- Insert 25 random customer records

INSERT INTO customers (name) VALUES ('Alice Smith'), ('Bob Johnson'), ('Charlie Brown'), ('Diana Prince'), ('Ethan Hunt'), ('Fiona Gallagher'), ('George Miller'), ('Hannah Abbott'), ('Ian Fleming'), ('Jane Austen'), ('Kevin Spacey'), ('Laura Croft'), ('Michael Jordan'), ('Nancy Drew'), ('Oliver Twist'), ('Penelope Cruz'), ('Quentin Tarantino'), ('Rachel Green'), ('Steve Rogers'), ('Tina Turner'), ('Ursula K. Le Guin'), ('Victor Hugo'), ('Wendy Darling'), ('Xavier Roberts'), ('Yvonne Craig');

-- Create the orders table

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INT,

order_date TIMESTAMP,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );

INSERT INTO orders (customer_id) WITH RECURSIVE data_rows (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM data_rows WHERE n < 1000 ) SELECT FLOOR(RANDOM() * 25) + 1 FROM data_rows;

-- Create index on orders table

CREATE INDEX idx_orders_customer_id ON orders (customer_id);".

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

"SELECT c.customer_id, COUNT(o.order_id) AS orders_count INTO test_rec FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;" .

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

"SELECT c.customer_id, (SELECT COUNT(o.order_id) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count INTO test_rec FROM customers c;" . Тестовая СУБД :

CPU = 8 , RAM = 8GB , Версия PostgreSQL 17.

Нагрузка растет экспоненциально от 10 до 45 одновременных сессий, длительность тестовой итерации 10 минут.

Вопрос - для какого тестового запроса производительность СУБД будет выше в ходе нагрузочного тестирования - при использовании тестового запроса-1 или при использовании тестового запроса-2 ?

1️⃣Ответ нейросети "Ask Postgres"

✅ Производительность будет выше при использовании тестового запроса-1 (LEFT JOIN + GROUP BY)

Обоснование

1. Запрос-1 (JOIN + GROUP BY) — оптимальный

• Один проход по таблице orders с использованием индекса idx_orders_customer_id.

• PostgreSQL 17 эффективно использует параллельные планы для JOIN и GROUP BY — при 45 сессиях нагрузка распределяется между ядрами CPU.

• Минимальная конкуренция за ресурсы: индекс сканируется один раз на запрос, а не 25 раз.

• Оптимизатор может применить Hash Join или Merge Join, что особенно эффективно при наличии индекса и небольшом количестве клиентов (25).

• Масштабируемость: при росте числа сессий производительность падает умеренно — линейно с числом запросов, а не с числом строк в orders.

Документация подтверждает:

«Subqueries can be transformed into semi-joins» — но только для некоррелир

ованных.

Коррелированные подзапросы (как в запросе-2) не преобразуются в JOIN автом

атически, особенно при использовании COUNT() — это ограничение оптимизатора.

2. Запрос-2 (коррелированный подзапрос) — неэффективный при нагрузке

• Для каждой из 25 строк customers выполняется отдельный подзапрос.

• При 45 сессиях — 1125 отдельных сканов индекса idx_orders_customer_id в секунду.

• Это вызывает: Конкуренцию за блоки индекса → локальные блокировки.

• Высокую нагрузку на CPU → много контекстных переключений.

• Ограничение параллелизма — подзапросы выполняются последовательно внутри одного запроса.

  📌 Рекомендация

  Для нагрузочных тестов с растущей параллельностью всегда используйте JOIN-версию (запрос-1).

  Коррелированные подзапросы с агрегациями — плохая практика в сценариях с высокой конкуренцией, даже если они выглядят «проще».

  Дополнительно: Для ещё большей производительности можно добавить CLUSTER на orders по customer_id, или использовать materialized view для кэширования агрегированных данных, если данные не меняются часто.

  ✅ Итог: Запрос-1 (LEFT JOIN + GROUP BY) обеспечивает значительно более высокую производительность при нагрузке 10–45 сессий.

  2️⃣Ответ нейросети "DeepSeek"

  Для данного сценария нагрузочного тестирования производительность будет выше при использовании тестового запроса-1 (с LEFT JOIN и GROUP BY). Обоснование:

  1. Оптимизация выполнения запроса:

  2. Запрос-1 выполняется как одиночный оператор с хеш-соединением и агрегацией, что эффективно обрабатывается планировщиком PostgreSQL.

  3. Запрос-2 использует коррелированный подзапрос, который выполняет 25 отдельных запросов COUNT (по одному на каждого клиента). При параллельных сессиях это создает дополнительную нагрузку.

  4. Эффективность при параллелизме:

  5. Запрос-1 лучше масштабируется благодаря хеш-агрегации и однократному сканированию таблиц.

  6. Запрос-2 генерирует множество индексных сканиваний (используя idx_orders_customer_id), что при 45 сессиях приводит к конкуренции за ресурсы ( latch-конфликты в кэше индексов).

  7. Статистика ресурсов:

  8. При 1000 заказах и 25 клиентах разница незначительна, но при экспоненциальном росте сессий запрос-2 сильнее нагружает CPU (многократные поиски в индексе) и RAM (больше служебных структур на сессию).

  9. Особенности PostgreSQL:

  10. Оптимизатор PostgreSQL эффективнее обрабатывает JOIN-агрегацию, чем вложенные подзапросы, при параллельных выполнениях. Версия 17 дополнительно улучшает параллельные хеш-соединения.

  Рекомендация: Для рабочих нагрузок с высоким параллелизмом предпочтительнее использовать запрос-1.

  ⚠️Результаты экспериментального нагрузочного тестирования

  Абсолютные значения операционной скорости и ожиданий

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

💥Среднее повышение операционной скорости при использовании коррелированного подзапроса 288%

⚠️Вывод - использовать нейросети для анализа и оптимизации производительности СУБД PostgreSQL под нагрузкой - нельзя.⚠️

Для данной виртуальной машины , данной версии СУБД , в ходе данного плана нагрузочного тестирования:

1. Производительность СУБД при использовании коррелированного подзапроса кардинально выше, чем при использовании 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 выравнивает время выполнения разных запросов

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

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

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

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

Предисловие

Нейросети для оптимизации баз данных часто предлагают математически верные, но практически проигрышные решения. В статье показано, как попытка применить покрывающий индекс (Index Only Scan) для таблицы с 1 миллионом строк и всего 685 уникальными значениями обернулась падением производительности на 7% под нагрузкой. Этот пример доказывает: для данных с низкой кардинальностью и высокой конкуренцией за ресурсы эффективность простых индексов или даже полного сканирования может быть выше. Все гипотезы, даже от ИИ, должны проходить экспериментальную проверку.

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

PG_EXPECTO

pg_expecto

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

CREATE TABLE pgbench_test

(

aid integer PRIMARY KEY ,

bid integer,

abalance integer,

filler character(84)

);

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;

Индекс

CREATE INDEX pgbench_test_idx ON pgbench_test ( bid );

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

select test.abalance

into test_rec

from pgbench_accounts acc

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

where acc.aid = current_aid ;

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

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

ℹ️ Метод доступа = Bitmap Index Scan on pgbench_test_idx

Рекомендация нейросети DeepSeek

Покрывающий индекс

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

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

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

(8 rows)

💥Стоимость плана выполнения = 48.86

💥Снижение стоимости плана выполнения 96%

ℹ️ Метод доступа = Index Only Scan using idx_pgbench_test_bid_abalance

Сравнение операционной скорости в Эксперимент-2(обычный индекс) и Эксперимент-3(покрывающий индекс)

Результаты

1. До нагрузки 15 соединений производительность СУБД при использовании покрывающего индекса(эксперимент-3) ниже производительности СУБД с использованием простого индекса (эксперимент-2) в среднем на 7%.

2. С ростом нагрузки после 15 соединений производительность СУБД при использовании покрывающего индекса(эксперимент-3) выше производительности СУБД с использованием простого индекса (эксперимент-2) в среднем на 22%.

Причины снижения производительности при использовании покрывающего индекса в эксперименте-3

1. Конкуренция за ресурсы блокировок

Покрывающий индекс значительно больше по размеру, чем простой индекс на bid

При Index Only Scan требуется проверка visibility map для каждого блока индекса

В условиях конкурентного доступа возникают блокировки на уровне:

• Буферного кэша

• Страниц индекса

• Visibility map

2. Проблемы с Visibility Map

При конкурентном доступе PostgreSQL должен постоянно обновлять visibility map

До 15 сессий конкуренция за обновление visibility map создает дополнительную нагрузку

В эксперименте-2 Bitmap Heap Scan менее чувствителен к актуальности visibility map

3. Эффект "теплого" кэша

В эксперименте-2 данные распределены между:

• Индексными страницами (меньший размер)

• Страницами таблицы

Это позволяет лучше использовать параллелизм на уровне кэша

В эксперименте-3 все данные сосредоточены в одном большом индексе, что создает "горячие точки" доступа

4. Статистика планировщика

Planning Time: 2.634 ms  -- эксперимент-2

Planning Time: 4.949 ms  -- эксперимент-3 (на 88% больше!)

Усложнение индекса требует больше времени на планирование

При множественных параллельных сессиях это накладные расходы суммируются.

5. Пороговый эффект

До 15 сессий:

• Накладные расходы на поддержание покрывающего индекса превышают выгоду

• Конкуренция за visibility map доминирует

После 15 сессий:

• Выгода от исключения обращений к heap перевешивает накладные расходы

• Система достигает точки безубыточности

6. Сравнение планов выполнения

✅ Вывод по одиночному запросу:

Эксперимент-3 в 280 раз быстрее - покрывающий индекс работает идеально для одного запроса.

Расчёт кардинальности для таблицы 'pgbench_test' по столбцу bid

pgbench_db=# select attname , n_distinct from pg_stats where tablename = 'pgbench_test' and attname ='bid' ;

attname | n_distinct

---------+------------

bid  |  685

(1 row)

pgbench_db=# select reltuples from pg_class where relname = 'pgbench_test';

reltuples

-----------

1e+06

(1 row)

⚠️Выводы:

1. При низкой и средней нагрузке накладные расходы на поддержание сложной структуры данных могут перевешивать ее преимущества.

2. Покрывающие индексы полезны при высокой кардинальности и редких запросах — не при низкой кардинальности и высокой параллельности.

3. Иногда "оптимизация" — это деградация.

⚠️Снижение стоимости запроса — это полезный индикатор потенциального улучшения производительности в рамках неизменной среды выполнения, но он не является ни необходимым (производительность можно повысить "мимо" планировщика), ни достаточным (планировщик может ошибаться) условием.

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

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

Предисловие

Каждый разработчик знает: если запрос медленный — нужно добавить индекс. Но что, если именно эти, на первый взгляд быстрые и «дешевые» запросы, становятся причиной деградации производительности всей системы под нагрузкой? В этой статье разбирается парадоксальная ситуация, когда следование классическим правилам оптимизации приводит к обратному эффекту, и объясняется, почему смотреть на стоимость одного запроса в отрыве от общей картины — опасно.

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Задача

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

План и сценарии нагрузочного тестирования

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL.

Изменение сценариев нагрузочного тестирования

PG_EXPECTO : Настройка сценариев нагрузочного тестирования СУБД PostgreSQL

Эксперимент-1 : Дополнительная тестовая таблица - без использования индекса

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

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;

Изменение тестового сценария-1

select test.abalance

into test_rec

from pgbench_accounts acc

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

where acc.aid = current_aid ;

План выполнения тестового запроса: cost = 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

(12 rows)

Эксперимент-2 : Создание индекса

CREATE INDEX pgbench_test_idx ON pgbench_test ( bid );

План выполнения тестового запроса: cost = 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

(10 rows)

Стоимость плана выполнения тестового запроса после создания индекса снизилась на 93%

Сравнительный анализ производительности СУБД в ходе нагрузочного тестирования в эксперименте-1 и эксперименте-2

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

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

Результаты

1. До нагрузки 8 соединений производительность СУБД при использовании индекса(эксперимент-2) превышает производительность СУБД без использования индекса (эксперимент-1) в среднем на 7%.

2. С ростом нагрузки после 8 соединений производительность СУБД при использовании индекса(эксперимент-2) ниже производительности СУБД без использования индекса (эксперимент-1) в среднем на 13%.

Возможные причины снижения производительности при использовании индекса в эксперименте-2

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 становится узким местом

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

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Предисловие

В эпоху, когда нейросети становятся первым источником знаний для многих разработчиков, особенно важно проверять их утверждения на практике. Один из таких вопросов — прямая связь между типами ожиданий в PostgreSQL и отсутствием индексов. AI-помощники часто дают логичные, но упрощённые ответы, которые могут ввести в заблуждение при решении реальных задач оптимизации. В этой статье мы проверим экспериментально, насколько обоснованно распространённое мнение о том, что IO-ожидания однозначно указывают на проблемы с индексацией.

Вопрос

Является ли ожидания типа IO необходимым и достаточным условием отсутствия индекса для таблицы или материализованного представления в СУБД PostgreSQL ?

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

Как и любая языковая модель, GigaChat не обладает собственным мнением и не транслирует мнение своих разработчиков. Ответ сгенерирован нейросетевой моделью на основе открытых данных, в которые могла попасть некорректная информация, способная повлиять на результат. Во избежание недоразумений, разговоры на некоторые темы временно ограничены

Ответ нейросети Aлиса

Ожидания типа IO не являются ни необходимым, ни достаточным условием для отсутствия индекса в таблице или материализованном представлении в PostgreSQL.

Это подтверждается следующими фактами:

* При наличии индекса также могут наблюдаться ожидания IO, но их количество и корреляция будут существенно ниже

* Основные признаки необходимости создания индекса при массовых операциях SELECT+UPDATE:
* Сильная корреляция ожиданий с типами IO/IPC
* Превышение количества ожиданий типа IPC над IO
* Использование Seq Scan или Parallel Seq Scan в плане выполнения запросов

* Отсутствие индекса приводит к последовательному сканированию таблицы (Seq Scan), что вызывает:
* Массовое чтение данных с диска (DataFileRead)
* Повышенные ожидания IO
* Низкую производительность запросов

Таким образом, хотя ожидания IO часто сопровождают работу с неиндексированными таблицами, их наличие/отсутствие само по себе не может служить единственным индикатором необходимости создания индекса. Требуется комплексный анализ планов запросов и корреляции различных типов ожиданий.

━━━━━━━━━━━━━━━━━━
На основе: dzen.ru postgrespro.ru tantorlabs.ru

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

В PostgreSQL ожидание типа IO (ввод-вывод) не является ни необходимым, ни достаточным условием для отсутствия индекса на таблице или материализованном представлении.

Пояснение:

1. Что такое ожидание IO?

Ожидание IO — это состояние, при котором процесс СУБД ожидает завершения операций чтения/записи на диске (например, чтение страницы данных с диска в буферный пул). Это метрика производительности, отражающая задержки, связанные с физическим доступом к данным, а не структурную особенность таблицы.

2. Связь с индексами

• Наличие или отсутствие индекса — это логическая структура, определяемая при создании таблицы или явно через CREATE INDEX.

• Ожидание IO возникает, когда PostgreSQL не может найти нужные данные в оперативной памяти (буферном пуле) и должен прочитать их с диска — независимо от наличия индекса.Без индекса: PostgreSQL может выполнять полное сканирование таблицы (Seq Scan), что приводит к большим IO-ожиданиям.
  С индексом: PostgreSQL может выполнять индексный сканирование (Index Scan), но если индекс не покрывает запрос, он всё равно может обращаться к таблице (Index + Heap Scan), что также вызывает IO.
  

3. Необходимость и достаточность

• Не является необходимым: Таблица без индекса может не вызывать IO-ожиданий, если все данные уже в буферном пуле (например, после нескольких повторных запросов).

• Не является достаточным: Наличие IO-ожиданий не означает отсутствие индекса — индексированные запросы тоже могут вызывать IO, если индекс или данные не кэшированы.

4. Вывод

Ожидание IO — это результат выполнения запроса, а не причина или признак отсутствия индекса.
Отсутствие индекса может способствовать увеличению IO-ожиданий, но не является единственной причиной, и наличие IO-ожиданий не доказывает отсутствие индекса.

Итог: Ожидание типа IO — независимый параметр производительности, не связанный логически с наличием индекса как с необходимым или достаточным условием.

См. документацию: [PostgreSQL Performance Monitoring] — раздел о pg_stat_activity и pg_stat_statements.

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

Отличный и очень точный вопрос. Короткий ответ: нет, ожидания типа IO не являются ни необходимым, ни достаточным условием отсутствия индекса.

Давайте разберем это подробно, так как понимание этого различия — ключ к грамотному анализу производительности PostgreSQL.

1. Почему это не необходимое условие?

Необходимое условие означает: "Если индекса нет, то мы обязательно увидим IO ожидания".

Это неверно. Отсутствие индекса может проявляться другими типами ожиданий, или вообще не проявляться в мониторинге ожиданий в конкретный момент времени.

Пример:
Допустим, у вас есть таблица users без индекса в столбце email. Вы выполняете запрос:

SELECT * FROM users WHERE email = 'test@example.com';

Планировщик будет вынужден выполнить Sequential Scan (Seq Scan). Что при этом происходит?

• Данные уже в RAM (кеш): Если таблица небольшая и полностью находится в shared_buffers (кэш PostgreSQL) или в оперативной кэш-памяти ОС, то чтение будет происходить из памяти. В этом случае вы увидите ожидания типа CPU, а не IO (например, CPU или, более конкретно, BufferPin). Запрос будет быстрым, но он все равно загрузит CPU необходимостью просканировать всю таблицу.

• Конкуренция за блокировки: Во время полного сканирования PostgreSQL должна получить доступ к каждому блоку данных. Если в этот момент с этими блоками работают другие процессы (например, на запись), ваш запрос может ждать освобождения легковесной блокировки (LWLock), а не самого ввода-вывода.

Вывод: Отсутствие индекса может приводить к высоким CPU ожиданиям или ожиданиям Lock, если данные уже кэшированы. Таким образом, IO-ожидания не являются необходимым признаком.

2. Почему это не достаточное условие?

Достаточное условие означает: "Если мы видим IO ожидания, то это гарантированно означает, что не хватает индекса".

Это тоже неверно. IO-ожидания — это очень общий симптом, который указывает на то, что СУБД много читает с диска. Причин для этого может быть множество, и отсутствие индекса — лишь одна из них.

Другие частые причины IO-ожиданий:

1. Недостаточный размер кэша (shared_buffers): Даже если по всем запросам есть идеальные индексы, если ваш рабочий набор данных не помещается в оперативной памяти, PostgreSQL будет вынуждена постоянно "подкачивать" нужные блоки с диска, вытесняя другие. Это вызовет Read ожидания (например, datafile read).

2. Неподходящий индекс или неоптимальный запрос:
   Запрос использует индекс, но он недостаточно селективен, и PostgreSQL приходится читать много случайных страниц с диска (это часто видно как Bitmap Heap Scan после Bitmap Index Scan).
   Запрос SELECT * FROM ... с индексом, но по условию отбирается много строк. PostgreSQL решает, что дешевле прочитать всю таблицу последовательно, чем делать много случайных обращений по индексу.

3. Отсутствие индекса для части данных: У вас есть индекс, но он не покрывает все варианты использования. Например, вы ищете по WHERE status = 'pending', и индекс есть, но 99% строк имеют этот статус. Seq Scan будет эффективнее, и он вызовет IO.

4. Проблемы с вводом-выводом самой системы:
   Медленный диск (HDD вместо SSD).
   Высокая конкуренция за дисковый ресурс от других приложений.
   Неправильная настройка ОС или файловой системы.

5. Очень большие запросы: Запросы, которые сознательно читают гигантские объемы данных (аналитические отчеты, агрегации), всегда будут вызывать IO-ожидания, и это нормально. Создание индексов здесь может даже навредить, замедлив вставку данных.

Связь между отсутствием индекса и IO-ожиданиями

Несмотря на то что условие не является ни необходимым, ни достаточным, сильная корреляция между отсутствием индекса и IO-ожиданиями, безусловно, существует.

• Когда данные не влезают в оперативную память, последовательное сканирование большой таблицы без индекса — это один из самых верных способов создать мощную нагрузку на подсистему ввода-вывода и, как следствие, высокие Read ожидания.

Как правильно анализировать?

1. Используйте EXPLAIN (ANALYZE, BUFFERS): Это ваш главный инструмент. Запустите этот запрос для медленного запроса. Он покажет:
   Seq Scan — прямое указание на полное сканирование таблицы (верный признак возможного отсутствия нужного индекса).
   Количество прочитанных блокков (shared hit vs shared read):
   shared hit — данные были в кэше.
   shared read — данные пришлось читать с диска (здесь и рождаются IO-ожидания).

2. Используйте специализированные представления для анализа ожиданий:
   Включите расширение pg_stat_statements и используйте такие представления, как pg_stat_database, pg_stat_all_tables, а также системные представления для анализа событий ожидания (например, pg_stat_activity с фильтром по wait_event_type).

3. Алгоритм диагностики:
   Шаг 1: Найдите запросы с самым большим временем выполнения или наибольшим количеством вызовов (pg_stat_statements).
   Шаг 2: Проанализируйте план выполнения этих запросов через EXPLAIN (ANALYZE, BUFFERS).
   Шаг 3: Если в плане видите Seq Scan по большой таблице — это кандидат на создание индекса.
   Шаг 4: Если при этом в плане много shared read — это подтверждает, что проблема усугубляется медленным диском (IO-ожидания).

Заключение

Ожидания типа IO — это важный симптом, который часто сопровождает проблему отсутствия индексов на больших таблицах, но он не является однозначным диагностическим критерием.

Правильный подход — это не гадание по типам ожиданий, а анализ планов выполнения конкретных медленных запросов, который однозначно покажет, использует ли PostgreSQL Seq Scan из-за отсутствия подходящего индекса.

Экспериментальная проверка необходимого и достаточного условия отсутствия индекса

Исходные данные: анализ инцидент производительности СУБД

Использование PG_EXPECTO для выявления проблемных SQL запросов при анализе инцидента производительности СУБД PostgreSQL

SQL запросы, вызывающие 80% ожиданий типа IO - 43 запроса

Вопрос по ожиданиям IO

Как среди списка SQL запросов - определить запросы для которых в таблицах не хватает индексов ?

Гипотеза

Используя результаты ранее сделанных экспериментов :

PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Использование метода доступа Seq Scan | Parallel Seq Scan совместно с использованием параллельных процессов( Workers Planned ,Workers Launched ) в плане выполнения запросов, обнаруженных в ходе корреляционного анализа.

...

ℹ️При возникновении данных условий могут возникнуть массовые ожидания IPC/BgWorkerShutdown.

Можно сделать предположение:

Подтверждающим признаком, сужающим круг поиска SQL запросов, для оптимизации которых, необходимо создать индексы в таблицах - является ожидание BgWorkerShutdown.

Проверка гипотезы

SQL запросы, вызывающие 80% ожиданий типа IPC

SQL запросы, вызывающие ожидания типа IO и IPС :

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

ИТОГ

Одновременная корреляция ожидания IPC/BgWorkerShutdown и IO/DSMFillZeroWrite может служить надежным признаком необходимости добавления индексов для таблиц, участвующих в запросах, выявленных в ходе анализа инцидента производительности СУБД, значительно сужая область оптимизации по ожиданиям IO.