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

Экспериментальное сравнение двух ИИ-ассистентов при решении одной задачи оптимизации SQL-запроса к PostgreSQL: анализ планов выполнения, замеры времени (37 ms против 61 ms), выводы о стратегиях доступа к системным каталогам и гипотеза о причинах отставания одного из решений.

Предисловие

При работе со статистикой производительности PostgreSQL (расширение pgpro_stats_statements) иногда возникает задача исключить из выборки записи, для которых база данных уже удалена из системы, то есть datname IS NULL. На первый взгляд, это тривиальное условие, но способ его реализации может кардинально повлиять на скорость выполнения запроса.

Вопрос был задан двум популярным нейросетевым помощникам — DeepSeek и Ask Postgres — один и тот же исходный запрос с просьбой модифицировать его, чтобы строки с NULL в имени базы не попадали в результат. Оба ИИ предложили рабочие решения, однако их архитектурные подходы оказались принципиально разными.

В этой статье мы выполним оба запроса на реальной инсталляции PostgreSQL, сравним их планы выполнения (через EXPLAIN ANALYZE) и проанализируем, почему один из вариантов оказался заметно быстрее другого, а также выдвинем гипотезу о возможной причине менее эффективного решения.

Запрос нейросети

Модифицируй запрос "SELECT

dbid ,

( SELECT datname FROM pg_database WHERE oid = dbid ) AS datname ,

( SELECT rolname FROM pg_roles WHERE oid = userid ) ,

queryid ,

date_trunc( 'minute' , CURRENT_TIMESTAMP ) ,

wait_stats ,

calls ,

rows

FROM

pgpro_stats_statements( FALSE )

WHERE

dbid NOT IN

(

SELECT oid

FROM pg_database

WHERE

datname IN ('postgres' , 'template1' , 'template0' , 'pgpropwr' , 'performance_monitoring_db' )

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL" чтобы в результат не попадали строки , для которых datname IS NULL

Решение "DeepSeek"

Чтобы исключить строки, где datname равен NULL, можно добавить соответствующее условие в WHERE. Однако чтобы не выполнять подзапрос дважды (для выборки и фильтрации), лучше переписать запрос с использованием CROSS JOIN LATERAL или CTE.

Ниже приведён модифицированный запрос с CROSS JOIN LATERAL, который вычисляет datname один раз и автоматически исключает строки, для которых база данных не найдена (т.е. datname IS NULL):

sql

SELECT
s.dbid,
d.datname,
(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,
s.queryid,
date_trunc('minute', CURRENT_TIMESTAMP) AS ts,
s.wait_stats,
s.calls,
s.rows
FROM
pgpro_stats_statements(FALSE) s
CROSS JOIN LATERAL (
SELECT datname
FROM pg_database
WHERE oid = s.dbid
) d
WHERE
s.dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND s.dbid IS NOT NULL
AND s.userid IS NOT NULL
AND s.queryid IS NOT NULL;

Пояснение:
CROSS JOIN LATERAL работает как внутреннее соединение: если подзапрос не возвращает строку (т.е. oid = dbid отсутствует в pg_database), то вся строка из pgpro_stats_statements исключается из результата. Это автоматически отсекает случаи, когда datname был бы NULL.

Решение Ask Postgres

Для того чтобы исключить строки, в которых datname оказывается NULL (что как раз соответствует сценарию с удаленными базами данных, описанному ранее), проще всего перенести логику проверки в блок WHERE.

Так как datname в вашем запросе является вычисляемым полем (подзапросом), вы не можете обратиться к нему по имени в WHERE напрямую. Вам нужно либо продублировать подзапрос в условии, либо обернуть весь запрос в CTE (Common Table Expression) или подзапрос.

Вариант 1: Использование CTE (наиболее читаемый и современный способ)

Мы сначала выбираем все данные, присваиваем имя столбцу datname, а затем фильтруем результат.

Скопироватьsql
WITH stats_data AS (
SELECT
dbid,
(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,
(SELECT rolname FROM pg_roles WHERE oid = userid) AS rolname,
queryid,
date_trunc('minute', CURRENT_TIMESTAMP) AS ts,
wait_stats,
calls,
rows
FROM
pgpro_stats_statements(FALSE)
WHERE
dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND dbid IS NOT NULL
AND userid IS NOT NULL
AND queryid IS NOT NULL
)
SELECT *
FROM stats_data
WHERE datname IS NOT NULL;

Вариант 2: Добавление условия в основной WHERE (без изменения структуры)

Если вы не хотите использовать CTE, нужно добавить проверку существования записи в pg_database прямо в WHERE.

Скопироватьsql
SELECT
dbid,
(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,
(SELECT rolname FROM pg_roles WHERE oid = userid),
queryid,
date_trunc('minute', CURRENT_TIMESTAMP),
wait_stats,
calls,
rows
FROM
pgpro_stats_statements(FALSE)
WHERE
-- Условие, чтобы исключить строки, где базы больше нет в каталоге
EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)
AND dbid NOT IN (
SELECT oid
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')
)
AND dbid IS NOT NULL
AND userid IS NOT NULL
AND queryid IS NOT NULL;

Что изменилось и почему это работает:

1. Логика фильтрации: В исходном запросе условие dbid NOT IN (...) отсекало только конкретные системные базы, но пропускало любые другие dbid, включая те, которых вообще нет в pg_database (для которых подзапрос возвращал NULL).

2. EXISTS или CTE:В Варианте 1 мы просто фильтруем итоговый набор данных по значению datname.
   В Варианте 2 мы добавили EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid), что гарантирует: строка попадет в результат только в том случае, если dbid существует в системном каталоге. Это эквивалентно условию datname IS NOT NULL.
   

3. Оптимизация: Вариант с EXISTS обычно работает быстрее, так как планировщик PostgreSQL может эффективно оптимизировать проверку существования записи.

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

Вариант, предложенный DeepSeek

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join  (cost=3.41..227.49 rows=89 width=196) (actual time=27.978..36.352 rows=4258 loops=1)

Hash Cond: (s.dbid = pg_database.oid)

->  Function Scan on pgpro_stats_statements s  (cost=1.60..14.10 rows=493 width=64) (actual time=27.898..28.902 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 2)))

Rows Removed by Filter: 588

SubPlan 2

->  Seq Scan on pg_database pg_database_1  (cost=0.00..1.58 rows=5 width=4) (actual time=0.038..0.044 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

->  Hash  (cost=1.36..1.36 rows=36 width=68) (actual time=0.036..0.038 rows=37 loops=1)

Buckets: 1024  Batches: 1  Memory Usage: 12kB

->  Seq Scan on pg_database  (cost=0.00..1.36 rows=36 width=68) (actual time=0.020..0.025 rows=37 loops=1)

SubPlan 1

->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

Вариант, предложенные Ask Postgres

EXPLAIN ANALYZE

SELECT

dbid,

(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

-- Условие, чтобы исключить строки, где базы больше нет в каталоге

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join  (cost=3.41..356.54 rows=89 width=212) (actual time=26.218..60.295 rows=4258 loops=1)

Hash Cond: (pgpro_stats_statements.dbid = pg_database.oid)

->  Function Scan on pgpro_stats_statements  (cost=1.60..14.10 rows=493 width=64) (actual time=26.097..27.457 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 3)))

Rows Removed by Filter: 588

SubPlan 3

->  Seq Scan on pg_database pg_database_2  (cost=0.00..1.58 rows=5 width=4) (actual time=0.069..0.075 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

->  Hash  (cost=1.36..1.36 rows=36 width=4) (actual time=0.035..0.038 rows=37 loops=1)

Buckets: 1024  Batches: 1  Memory Usage: 10kB

->  Seq Scan on pg_database  (cost=0.00..1.36 rows=36 width=4) (actual time=0.021..0.026 rows=37 loops=1)

SubPlan 1

->  Seq Scan on pg_database pg_database_1  (cost=0.00..1.45 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=4258)

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

Сравнение вариантов

TEST SQL-1 : Запрос предложенный DeepSeek

TEST SQL-2 : Запрос предложенный Ask Postgres

Сравни эффективность и производительность тестовых запросов

TEST SQL-1

-------------------------------------------------------

EXPLAIN ANALYZE

SELECT

s.dbid,

d.datname,

(SELECT rolname FROM pg_roles WHERE oid = s.userid) AS rolname,

s.queryid,

date_trunc('minute', CURRENT_TIMESTAMP) AS ts,

s.wait_stats,

s.calls,

s.rows

FROM

pgpro_stats_statements(FALSE) s

CROSS JOIN LATERAL (

SELECT datname

FROM pg_database

WHERE oid = s.dbid

) d

WHERE

s.dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND s.dbid IS NOT NULL

AND s.userid IS NOT NULL

AND s.queryid IS NOT NULL ;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join (cost=3.41..227.49 rows=89 width=196) (actual time=27.978..36.352 rows=4258 loops=1)

Hash Cond: (s.dbid = pg_database.oid)

-> Function Scan on pgpro_stats_statements s (cost=1.60..14.10 rows=493 width=64) (actual time=27.898..28.902 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 2)))

Rows Removed by Filter: 588

SubPlan 2

-> Seq Scan on pg_database pg_database_1 (cost=0.00..1.58 rows=5 width=4) (actual time=0.038..0.044 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

-> Hash (cost=1.36..1.36 rows=36 width=68) (actual time=0.036..0.038 rows=37 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 12kB

-> Seq Scan on pg_database (cost=0.00..1.36 rows=36 width=68) (actual time=0.020..0.025 rows=37 loops=1)

SubPlan 1

-> Index Scan using pg_authid_oid_index on pg_authid (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = s.userid)

Planning Time: 0.412 ms

Execution Time: 37.153 ms

(17 rows)

-------------------------------------------------------

TEST SQL-2

-------------------------------------------------------

EXPLAIN ANALYZE

SELECT

dbid,

(SELECT datname FROM pg_database WHERE oid = dbid) AS datname,

(SELECT rolname FROM pg_roles WHERE oid = userid),

queryid,

date_trunc('minute', CURRENT_TIMESTAMP),

wait_stats,

calls,

rows

FROM

pgpro_stats_statements(FALSE)

WHERE

-- Условие, чтобы исключить строки, где базы больше нет в каталоге

EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid)

AND dbid NOT IN (

SELECT oid

FROM pg_database

WHERE datname IN ('postgres', 'template1', 'template0', 'pgpropwr', 'performance_monitoring_db')

)

AND dbid IS NOT NULL

AND userid IS NOT NULL

AND queryid IS NOT NULL;

QUERY PLAN

-------------------------------------------------------------------------------------

Hash Join (cost=3.41..356.54 rows=89 width=212) (actual time=26.218..60.295 rows=4258 loops=1)

Hash Cond: (pgpro_stats_statements.dbid = pg_database.oid)

-> Function Scan on pgpro_stats_statements (cost=1.60..14.10 rows=493 width=64) (actual time=26.097..27.457 rows=4288 loops=1)

Filter: ((dbid IS NOT NULL) AND (userid IS NOT NULL) AND (queryid IS NOT NULL) AND (NOT (hashed SubPlan 3)))

Rows Removed by Filter: 588

SubPlan 3

-> Seq Scan on pg_database pg_database_2 (cost=0.00..1.58 rows=5 width=4) (actual time=0.069..0.075 rows=5 loops=1)

Filter: (datname = ANY ('{postgres,template1,template0,pgpropwr,performance_monitoring_db}'::name[]))

Rows Removed by Filter: 32

-> Hash (cost=1.36..1.36 rows=36 width=4) (actual time=0.035..0.038 rows=37 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 10kB

-> Seq Scan on pg_database (cost=0.00..1.36 rows=36 width=4) (actual time=0.021..0.026 rows=37 loops=1)

SubPlan 1

-> Seq Scan on pg_database pg_database_1 (cost=0.00..1.45 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=4258)

Filter: (oid = pgpro_stats_statements.dbid)

Rows Removed by Filter: 36

SubPlan 2

-> Index Scan using pg_authid_oid_index on pg_authid (cost=0.14..2.36 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=4258)

Index Cond: (oid = pgpro_stats_statements.userid)

Planning Time: 0.474 ms

Execution Time: 61.394 ms

(21 rows)

-------------------------------------------------------

Анализ DeepSeek

Сравнение эффективности и производительности запросов

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

1. Ключевое различие в способе получения имени базы данных (datname)

• TEST SQL-1
  Использует CROSS JOIN LATERAL (SELECT datname FROM pg_database WHERE oid = s.dbid).
  Оптимизатор преобразовал это в Hash Join между функцией pgpro_stats_statements и таблицей pg_database по s.dbid = pg_database.oid. В результате имя базы выбирается однократным последовательным сканированием pg_database и хэш-таблицей, без дополнительных подзапросов на каждую строку.

• TEST SQL-2
  Помещает подзапрос прямо в список SELECT:
  (SELECT datname FROM pg_database WHERE oid = dbid).
  Это коррелированный скалярный подзапрос, который выполняется для каждой выходной строки (4258 раз). Каждый вызов делает Seq Scan по pg_database (cost=0.00..1.45, loops=4258).
  Несмотря на малое количество строк в pg_database (37), накопленные издержки существенно замедляют запрос.

2. Планы выполнения и время

TEST SQL-1

• Основной метод чтения: Hash Join по s.dbid = pg_database.oid

• Получение datname: через тот же Hash Join (ноль отдельных вызовов)

• Получение rolname: 4258 индексных сканирований pg_authid_oid_index

• Проверка NOT IN: один SubPlan 2 – одно последовательное сканирование pg_database

• Общее время выполнения: 37.153 ms

• Время планирования: 0.412 ms

TEST SQL-2

• Основной метод чтения: Hash Join (для EXISTS) + два скалярных подзапроса

• Получение datname: 4258 последовательных сканирований pg_database (в SubPlan 1)

• Получение rolname: 4258 индексных сканирований pg_authid_oid_index

• Проверка NOT IN: один SubPlan 3 – одно последовательное сканирование pg_database

• Общее время выполнения: 61.394 ms (≈ на 65% медленнее)

• Время планирования: 0.474 ms

3. Избыточность условий

TEST SQL-2 содержит явную проверку EXISTS (SELECT 1 FROM pg_database WHERE oid = dbid). Оптимизатор реализовал её через тот же Hash Join, который одновременно служит и для соединения. Однако в TEST SQL-1 аналогичная проверка неявно выполняется самим CROSS JOIN LATERAL, так как при отсутствии базы подзапрос не вернёт строку, и запись будет исключена. Таким образом, EXISTS в SQL-2 избыточен и не даёт преимущества, а основное замедление вызвано именно скалярным подзапросом для datname.

Вывод

TEST SQL-1 значительно эффективнее благодаря использованию латерального соединения вместо скалярного подзапроса в SELECT. Это позволяет оптимизатору получить имя базы данных один раз через Hash Join, избегая многократных последовательных сканирований pg_database.
Рекомендация: для похожих отчётов всегда выносить коррелированные подзапросы во FROM с помощью JOIN LATERAL или обычных соединений – это радикально снижает накладные расходы при большом числе строк.

Анализ Ask Postgres

Анализ планов выполнения показывает, что TEST SQL-1 значительно эффективнее, чем TEST SQL-2. Несмотря на то, что оба запроса решают одну и ту же задачу, они используют разные стратегии извлечения данных из системных каталогов.

Сравнение ключевых показателей

• Время выполнения (Execution Time):TEST SQL-1: 37.153 ms
  TEST SQL-2: 61.394 ms
  Результат: SQL-1 быстрее примерно на 40%.
  

• Метод получения имени базы данных (datname):TEST SQL-1: Использует CROSS JOIN LATERAL, который оптимизатор преобразовал в Hash Join.
  TEST SQL-2: Использует скалярный подзапрос в блоке SELECT, что привело к Seq Scan.
  

• Интенсивность доступа к pg_database:TEST SQL-1: Сканирует таблицу один раз для всего набора данных.
  TEST SQL-2: Обращается к таблице 4258 раз (по одному разу для каждой строки результата).
  

Детальный разбор причин разницы в производительности

1. Механизм получения имени базы данных (datname)

• В SQL-1 (Эффективно): Благодаря CROSS JOIN LATERAL база данных один раз сканирует таблицу pg_database, строит хэш-таблицу в памяти и за один проход сопоставляет её со всеми строками из pgpro_stats_statements.

• В SQL-2 (Неэффективно): Скалярный подзапрос в SELECT выполняется как SubPlan 1. Это означает, что для каждой из 4258 строк результата сервер заново запускает последовательное сканирование таблицы pg_database. Это создает огромную лишнюю нагрузку на CPU и память.

2. Работа с фильтрацией (EXISTS vs JOIN)

• В SQL-2 условие EXISTS в блоке WHERE действительно отфильтровало строки (через Hash Join), но это не помогло ускорить получение имени базы.

• Проблема SQL-2: База данных дважды делает одну и ту же работу. Первый раз в WHERE (через EXISTS), чтобы проверить наличие базы, и второй раз в SELECT (через подзапрос), чтобы извлечь её имя. В SQL-1 эта работа объединена в одну операцию JOIN.

3. Доступ к ролям (rolname)

• В обоих запросах доступ к pg_authid реализован через Index Scan по первичному ключу (pg_authid_oid_index). Это максимально эффективный способ, поэтому в этой части производительность запросов идентична.

Итоговый вывод

TEST SQL-1 является оптимальным выбором, так как он использует множественную операцию (Join) вместо итеративной (Scalar Subquery).

Рекомендация: Для фильтрации NULL значений (удаленных баз) используйте подход из SQL-1. Поскольку CROSS JOIN LATERAL (в данной конфигурации) работает как INNER JOIN, он автоматически отсекает все строки, для которых dbid не найден в pg_database. Дополнительные проверки IS NOT NULL в таком случае становятся избыточными, но не вредными.

Итог: сравнение нейросетей DeepSeek и Ask Postgres

DeepSeek (TEST SQL-1):

• Время выполнения: 37.153 ms

• Способ получения datname: CROSS JOIN LATERAL → преобразован в Hash Join

• Количество обращений к pg_database: 1 последовательное сканирование

• Наличие избыточных условий: нет (фильтрация через LATERAL неявная)

• Читаемость кода: чуть сложнее (требует знания LATERAL)

• Рекомендация к использованию: да (оптимальный по производительности)

Ask Postgres (TEST SQL-2):

• Время выполнения: 61.394 ms (примерно на 65% дольше, чем DeepSeek)

• Способ получения datname: скалярный подзапрос в SELECT → выполняется 4258 раз

• Количество обращений к pg_database: 4258 последовательных сканирований (SubPlan 1)

• Наличие избыточных условий: есть (EXISTS + подзапрос в SELECT — двойная работа)

• Читаемость кода: проще и интуитивнее

• Рекомендация к использованию: нет (только для очень малых выборок)

Общий вывод: DeepSeek предложил значительно более эффективное решение. Основное преимущество — использование латерального соединения, которое позволило оптимизатору PostgreSQL применить Hash Join вместо многократных коррелированных подзапросов.

Гипотеза о причине неэффективного решения, предложенного нейросетью Ask Postgres

Почему Ask Postgres выдал менее оптимальный вариант, тогда как DeepSeek сразу выбрал CROSS JOIN LATERAL?

Возможные причины:

1. Асимметрия обучающей выборки
   Ask Postgres мог быть обучен преимущественно на простых, «классических» запросах, где скалярные подзапросы в SELECT встречаются часто и на малых объёмах данных не вызывают проблем. DeepSeek же, вероятно, получил больше примеров с продвинутой оптимизацией и использованием LATERAL.

2. Отсутствие явного указания на объём данных
   Исходный запрос не содержал информации о том, что функция pgpro_stats_statements(FALSE) возвращает сотни или тысячи строк. Ask Postgres, вероятно, не сделал допущение о большом количестве записей и поэтому не стал искать метод, избегающий перебора.

3. Предпочтение краткости и прямолинейности
   Решение Ask Postgres (EXISTS в WHERE + подзапрос в SELECT) короче по символам и не требует знания конструкции LATERAL. Нейросеть могла выбрать путь наименьшего сопротивления, отдав приоритет простоте кода, а не производительности.

4. Недостаточная глубина анализа плана выполнения
   В отличие от человека, ИИ не выполняет мысленный EXPLAIN и не оценивает затраты на многократные Seq Scan. Если в обучающих данных не было достаточного числа примеров с разбором планов для подобных ситуаций, нейросеть склонна генерировать «среднестатистический» работающий запрос без учёта кардинальности.

5. Архитектурная особенность Ask Postgres
   Возможно, этот помощник сильнее заточен на синтаксическую точность и соответствие стандартам SQL, а не на специфические трюки оптимизации для PostgreSQL (где LATERAL и CROSS JOIN LATERAL позволяют эффективно обходить проблемы коррелированных подзапросов).

Послесловие

Проведённый эксперимент наглядно демонстрирует, что даже небольшие различия в написании SQL-запроса могут приводить к серьёзной разнице в производительности — в нашем случае почти 40% преимущества у решения DeepSeek. Однако не менее интересен сам факт того, что нейросети, обученные на огромных массивах текстов, могут генерировать неоптимальные планы там, где, казалось бы, хватает стандартной эвристики («не используй коррелированные подзапросы в SELECT для тысяч строк»). Это не означает, что Ask Postgres плох, но подчёркивает важность для инженера не слепо доверять ИИ, а всегда проверять реальные планы выполнения. В конечном счёте, лучший результат достигается в диалоге: человек ставит задачу, нейросеть предлагает вариант, а опытный DBA уточняет и направляет.

Практический вывод для инженеров:
При работе с ИИ-ассистентами всегда полезно давать дополнительный контекст о размере данных и требовать не просто работающего, а производительного решения. А ещё лучше — знать приёмы вроде LATERAL самому и проверять планы через EXPLAIN ANALYZE.

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

Материал подготовлен с помощью нейросети DeepSeek. Не для публикации на Хабре.

От интегральной корреляции к событийно-ориентированному пространству состояний: методология сбора и кластеризации raw-событий ожидания PostgreSQL, построение марковской модели переходов между агрегированными wait-состояниями, адаптивное забывание и комбинированный прогноз риска деградации производительности на основе цепочек блокировок.

1. Анализ существующей реализации и выявление ограничений

Текущее состояние модели :

• Пространство состояний: 189 дискретных состояний, определяемых комбинацией:

• correlation (скоррелированность операционной скорости и времени ожидания, шаг 0.1 от –1.0 до +1.0)

• os_trend (тренд операционной скорости: –1, 0, +1)

• wait_trend (тренд времени ожидания: –1, 0, +1)

• Источник данных: таблица cluster_stat_median (агрегированные метрики производительности кластера)

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

• Прогноз риска: поглощающая матрица для аварийных состояний (отрицательная корреляция + снижение os_trend + рост wait_trend)

Ограничения текущей модели для анализа цепочек ожиданий:

• Не использует напрямую события ожидания PostgreSQL (wait_event_type / wait_event из pg_stat_activity)

• Работает с обобщённой корреляцией, что даёт интегральный риск, но не позволяет диагностировать конкретные цепочки блокировок (например, LWLock:BufferContent → IO:DataFileRead)

• Частота дискретизации (1 минута) может быть недостаточной для захвата быстрых переходов между событиями ожидания (субминутные паттерны)

2. Расширение источников данных: сбор цепочек ожиданий

2.1. Внедрение сбора raw-событий ожидания

Использовать расширение pg_wait_sampling (доступно с PostgreSQL 9.6+) для периодического снимка событий ожидания всех процессов

Создать таблицу wait_event_snapshots со следующими колонками:

ts (TIMESTAMPTZ NOT NULL) – время снимка

pid (INT NOT NULL) – идентификатор процесса

wait_event_type (TEXT) – тип события ожидания

wait_event (TEXT) – конкретное событие

state (TEXT) – состояние процесса

query_id (BIGINT) – идентификатор запроса

Настроить фоновый сбор (например, каждые 5–10 секунд) через background worker

2.2. Формирование цепочек ожиданий по процессам

Для каждого процесса (pid) за период активной сессии построить временную последовательность событий ожидания:

• Сгладить шум: убрать быстрое переключение между несущественными состояниями (фильтр скользящего большинства или минимальная длительность)

Сохранять цепочки в таблицу wait_event_chains:

chain_id (BIGSERIAL) – первичный ключ

pid (INT) – идентификатор процесса

start_ts (TIMESTAMPTZ) – время начала цепочки

end_ts (TIMESTAMPTZ) – время окончания цепочки

events (TEXT[]) – массив wait_event в порядке следования

3. Определение пространства состояний на основе событий ожидания

3.1. Агрегация событий в значимые состояния

• Слишком много raw-событий (более 200). Необходимо кластеризовать их в разумное число состояний (10–30) на основе:

• Группировки по wait_event_type (Lock, LWLock, IO, Client, Activity, Extension…)

• Дополнительной детализации для самых частых типов (например, отдельные состояния для LWLock:BufferContent, LWLock:WALWrite)

• Экспертных правил из документации PostgreSQL

Создать справочник wait_state_descriptions с колонками:

state_id (SMALLINT PRIMARY KEY) – идентификатор состояния

state_name (TEXT NOT NULL) – например, 'LWLock_BufferContent', 'IO_DataFileRead'

wait_event_type (TEXT) – тип события

wait_event (TEXT) – событие

is_absorbing (BOOLEAN DEFAULT FALSE) – флаг аварийного/поглощающего состояния

3.2. Функция приведения snapshot’а к состоянию

Реализовать get_wait_state_for_process(pid, ts) RETURNS SMALLINT, которая для данного процесса в момент времени возвращает идентификатор состояния на основе текущего wait_event (или NULL, если процесс активен)

Для агрегации по кластеру: основное состояние системы в момент времени – это наиболее часто встречающееся wait_event_type среди всех активных процессов (или состояние с максимальным временем ожидания)

4. Модификация модели цепи Маркова для анализа цепочек ожиданий

4.1. Новая таблица переходов для wait-событий

Аналог transition_log, но с более высокой частотой (каждые 5–10 секунд):

id (BIGSERIAL PRIMARY KEY)

ts (TIMESTAMPTZ NOT NULL)

from_state (SMALLINT NOT NULL)

to_state (SMALLINT NOT NULL)

process_pid (INT NULL) – опционально для индивидуальных цепочек

Индексы по (ts, from_state) и (from_state, to_state)

4.2. Обучение цепи (адаптация mchain_train_step)

Создать отдельную функцию wchain_train_step(), вызываемую с частотой сбора (например, каждые 10 секунд)

Логика:

• Получить текущее состояние системы на основе агрегированных wait events

• Если предыдущее состояние существует – записать переход в wait_transition_log

• Обновить wait_frequencies (аналог markov_frequencies для wait-состояний)

• Периодически (например, раз в 10 шагов) пересчитывать вероятности и применять забывание

4.3. Оценка марковского свойства для wait-цепочек

Добавить диагностическую функцию check_markov_property_wait(), которая для реальных цепочек вычисляет:

• Среднюю длину корреляции (на основе partial autocorrelation)

• Сравнение вероятностей переходов первого и второго порядка (тест отношения правдоподобия)

• Результат сохранять в markov_config как wait_markov_verified

5. Прогнозирование риска на основе цепочек ожиданий

5.1. Определение аварийных состояний в wait-пространстве

Аварийными считать состояния, соответствующие:

• Длительным блокировкам (Lock:transactionid, Lock:tuple)

• Деградации ввода-вывода (IO:DataFileRead с высоким временем)

• Сочетаниям, предшествующим deadlock’ам (по историческим данным)

• В таблице wait_state_descriptions установить флаг is_absorbing = TRUE

5.2. Функции прогноза риска (адаптация mchain_predict_risk_k)

Создать wchain_predict_risk_k(k INT) с использованием поглощающей матрицы wait_absorbing

Шаг прогноза – интервал дискретизации (например, 10 секунд). Для удобства добавить обёртки:

• wchain_predict_risk_1min

• wchain_predict_risk_5min

• (вычисляют количество шагов)

Учитывать возможность неизвестного состояния (например, если текущее состояние не встречалось в обучении) – возвращать априорный риск

5.3. Комбинированный риск

Итоговый риск инцидента производительности можно рассчитывать как взвешенную сумму:

• Риск на основе корреляции (старая модель) – для общих трендов

• Риск на основе wait-цепочек – для специфических блокировок

• Веса настраиваются через markov_config (например, wait_model_weight = 0.7)

6. Расширение таблиц конфигурации и метаданных

6.1. Новая конфигурация для wait-модели

Добавить в markov_config (или создать wait_markov_config) следующие поля:

• wait_sampling_interval_sec (INT DEFAULT 10) – интервал сбора wait-событий

• wait_min_transitions_for_forgetting (INT DEFAULT 5000) – порог числа переходов для включения забывания

• wait_absorbing_states (TEXT[]) – список названий аварийных состояний

6.2. Таблицы для wait-частот и вероятностей

• wait_frequencies (from_state, to_state, frequency)

• wait_probabilities

• wait_absorbing

• Все строятся по аналогии с существующими, но с ключом по wait_state_id

6.3. Журнал забывания для wait-модели

• Можно использовать единую таблицу apply_forgetting_log с дополнительной колонкой model_type ('correlation' / 'wait_chain')

7. Адаптация механизма забывания и достаточности данных

7.1. Раздельная проверка достаточности

Реализовать wchain_check_sufficiency(), проверяющую:

• Общее число переходов в wait_transition_log ≥ порога

• Стабильность вероятностей для wait-состояний (аналог mchain_forecast_reliability)

• Автоматическое включение забывания для wait-модели через wchain_enable_forgetting_when_sufficient()

7.2. Адаптивный alpha для wait-модели

Использовать те же принципы:

• alpha = base_alpha * exp(-days_since_incident / half_life)

• Базовый alpha может быть другим (например, 0.05 для более быстрой адаптации к изменяющимся паттернам блокировок)

• Параметры: wait_base_alpha, wait_min_alpha, wait_incident_half_life_days

8. Интеграция с существующими функциями очистки и логирования

Расширить mchain_clean_transition_log (или создать wchain_clean_transition_log) для удаления старых записей из wait_transition_log

Адаптировать mchain_clean_apply_forgetting_log для фильтрации по модели

Использовать общую таблицу mchain_error_log для ошибок в wait-функциях

9. Мониторинг и отладка для wait-цепочек

9.1. Функции текущего состояния

• wchain_get_current_state() – возвращает wait_state_id текущего агрегированного состояния системы

• wchain_get_process_chain(pid, interval) – показывает цепочку ожиданий для конкретного процесса за заданный интервал

9.2. Отчёт достоверности

Дополнить mchain_reliability_report() секцией по wait-модели:

• Рейтинг достоверности для wait-цепочек (0–5)

• Рекомендации по настройке частоты сбора

10. Поэтапный план внедрения

Подготовка (1 неделя)

• Создание таблиц для сбора snapshot’ов

• Написание скрипта сбора

Разработка состояний (1 неделя)

• Анализ wait-событий на реальной нагрузке

• Кластеризация событий

• Создание справочника wait_state_descriptions и функции get_wait_state_id()

Реализация базовой цепи (2 недели)

1. Создание таблиц wait_frequencies, wait_transition_log

• Функция wchain_train_step (без забывания)

• Тестовое обучение

Прогнозирование (1 неделя)

• Реализация wchain_predict_risk_k и поглощающей матрицы

• Проверка на исторических данных о инцидентах

Забывание и достаточность (1 неделя)

• Адаптация wchain_apply_forgetting

• wchain_check_sufficiency

• Интеграция с markov_config

Интеграция с существующей моделью (1 неделя)

• Функция комбинированного риска

• Настройка весов

• Автоматический выбор модели

Тестирование и документирование (2 недели)

• Нагрузочное тестирование

• Сравнение точности прогнозов старой и новой модели

• Написание документации

11. Ожидаемые результаты

• ➡️Возможность предсказывать инциденты производительности, связанные с конкретными цепочками блокировок (например, «через 10 минут высокая вероятность deadlock из-за накопления LWLock:BufferContent»)

• ➡️Повышение точности прогноза за счёт использования более детерминированных сигналов (wait events) вместо косвенной корреляции

• ➡️Диагностические отчёты: «ваша система 80% времени проводит в состоянии IO:DataFileRead, переход в LWLock:WALWrite с вероятностью 0.3 ведёт к деградации за 15 минут»

• ➡️Единая архитектура, позволяющая в будущем добавлять другие источники состояний (например, статистику индексов, размер очереди блокировок)

12. Рекомендации по дальнейшему развитию

• Использовать скрытые марковские модели (HMM) для учёта ненаблюдаемых факторов (например, внутренних очередей ОС)

• Внедрить неоднородные цепи Маркова с учётом времени суток и дня недели (циклическая нагрузка)

• Автоматическое определение аварийных состояний на основе исторических инцидентов (обучение с учителем)

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