Расследование инцидентов производительности в PostgreSQL часто напоминает поиск иголки в стоге сена. Десятки тысяч запросов , и определить, какой именно из них стал «слабым звеном» системы, без специальных инструментов — крайне сложная задача.
В этой статье рассмотрим, как использование PG_EXPECTO позволяет кардинально ускорить этот процесс. Мы не будем гадать на основе снимков pg_stat_statements. Вместо этого мы научимся проактивно создавать «ловушки» на проблемные паттерны производительности. Когда инцидент происходит, PG_EXPECTO позволяет быстро найти проблемные SQL-запросы , предоставляя инженеру готовый список «подозреваемых» для дальнейшей оптимизации.
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub
kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL
Практическая иллюстрация применения методик использования pg_expecto :
Теоретическое предисловие:
Статистический анализ производительности СУБД PostgreSQL | Postgres DBA | Дзен
Обнаружение инцидента производительности СУБД, с помощью мониторинга Zabbix
Дата и время возникновения инцидента : 2025-11-01 10:22
Предварительные действия: сформировать сводный отчет оценки производительности и инфраструктуры СУБД
summary_report.sh '2025-11-01 10:22'
Часть-1: Выявление аномалий инфраструктуры
1.1 Корреляция "Ожидания СУБД - vmstat"
PG_EXPECTO : Корреляция ожиданий СУБД и показателей vmstat | Postgres DBA | Дзен
Результат отчета:
Нагрузка на СУБД подтверждается наличием корреляции с метриками vmstat
1.2 Статистика vmstat+iostat по файловой подсистеме /data
PG_EXPECTO : Корреляция ожиданий vmstat и iostat для дискового устройства | Postgres DBA | Дзен
Результат отчета:
Имеются проблемы производительности на запись для дискового устройства используемого для файловой системы /data
1.3 Статистика vmstat+iostat по файловой подсистеме /wal
PG_EXPECTO : Корреляция ожиданий vmstat и iostat для дискового устройства | Postgres DBA | Дзен
Результат отчета:
Существенных аномалий нет, но возможно, имеются перспективы для оптимизации.
1.4 Чек-лист IO
PG_EXPECTO : Чек-лист IO | Postgres DBA | Дзен
Результат отчета:
Аномалий - не обнаружено.
1.5 Чек-лист CPU
PG_EXPECTO : Чек-лист CPU | Postgres DBA | Дзен
Результат отчета:
Аномалий - не обнаружено.
1.6 Чек-лист RAM
PG_EXPECTO : Чек-лист RAM | Postgres DBA | Дзен
Результат отчета:
Аномалий - не обнаружено.
1.7 Результат анализа инфраструктуры
Аномалии инфраструктуры, оказывающие влияние на производительность СУБД:
Превышение времени отклика на запись для дискового устройства используемого для файловой системы /data
Часть-2: Корреляционный анализ производительности СУБД
2.1 Операционная скорость и ожидания СУБД в период, предшествующий инциденту
2.2 Корреляционный анализ ожиданий СУБД
Тип ожидания, имеющий наибольший коэффициент корреляции с ожиданиями СУБД - IPC
IPC: Серверный процесс ожидает взаимодействия с другим процессом. В wait_event обозначается конкретное место ожидания;
2.3 Диаграмма Парето по событиям ожидания СУБД - для типа ожидания IPC
PG_EXPECTO : Диаграмма Парето по событиям ожидания СУБД | Postgres DBA | Дзен
Гипотеза(спойлер)
Можно сразу предположить , что причина - отсутствие индексов.
2.4 Диаграмма Парето по ожиданиям SQL запросов для типа ожидания IPC
PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов | Postgres DBA | Дзен
Часть-3: Формирование списка проблемных SQL запросов, для последующей оптимизации
Список queryid SQL-запросов для оптимизации:
-1701015661318396920
3449463017331132112
-7715565454820708773
1374759154717555017
-678327810318891437
5459520954633506046
-3969322877824419761
3985919093425059746
Часть 4: Получение рекомендации нейросети по снижению ожиданий типа IPC
Запрос нейросети
Файл, сформированный отчетом : net.1.wait_event.prompt.txt
Выдели общие части из текста и найти смысловые совпадения. Сформируй краткий итог по необходимым мероприятиям в виде сводной таблицы.
Входной файл для нейросети сформированный отчетом : net.1.wait_event.IPC.txt
Результат работы нейросети DeepSeek
Часть 5: Анализ проблемных SQL запросов нейросетью
Запрос нейросети
Файл сформированный отчетом : net.2.sql.prompt.txt
Выдели ключевые паттерны SQL запросов , с уточнением - сколько раз встречается паттерн. Сформируй итоговую таблицу - какие паттерны используются для каждого queryid. Выдели ключевые особенности SQL запроса, использующего наибольшее количество паттернов.
Входной файл для нейросети сформированный отчетом : net.2.sql.IPC.txt
Результат работы нейросети DeepSeek
Итог
В результате проведённого анализа инцидента производительности СУБД установлена причина снижения производительности - рост ожиданий типа IPC и сформирован список проблемных SQL запросов для последующей оптимизации.
P.S. Пример использования нейросети для оптимизации SQL запроса.
Проблемный запрос для оптимизации, queryid = 1374759154717555017
Текст запроса
SELECT
"Table1"."col1",
"Table1"."col2",
"Table2"."col1" AS "Table2.col1",
"Table2"."col3" AS "Table2.col3",
"Table2"."col4" AS "Table2.col4",
"Table3"."col1" AS "a.col1",
"Table3"."col4" AS "a.col4",
"Table3"."col5" AS "a.col5",
"Table3"."col6" AS "a.col6"
FROM "public"."Table4" AS "Table1"
INNER JOIN "public"."Table5" AS "Table2" ON "Table1"."col1" = "Table2"."col4"
INNER JOIN "public"."Table4_a" AS "Table3" ON "Table1"."col1" = "Table3"."col4" AND "Table3"."col5" = 'X1' AND "Table3"."col6" = 'X2'
Таблицы участвующие в запросе
Table "public.Table4"
Column | Type | Collation | Nullable | Default
--------+----------------+-----------+----------+---------------------
col1 | uuid | | not null | generate1()
col2 | type1 | | not null |
Indexes:
"Table4_pkey" PRIMARY KEY, btree (col1)
Referenced by:
TABLE "Table5" CONSTRAINT "Table5_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
TABLE "Table4_a" CONSTRAINT "Table4_a_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
TABLE "Table4_meta" CONSTRAINT "Table4_meta_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
TABLE "templates_Table4" CONSTRAINT "templates_Table4_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
Table "public.Table5"
Column | Type | Collation | Nullable | Default
-----------+------+-----------+---------+---------------------
col1 | uuid | | not null | generate1()
col3 | uuid | | not null |
col4 | uuid | | not null |
Indexes:
"Table5_pkey" PRIMARY KEY, btree (col1)
"Table5_col3_col4_key" UNIQUE CONSTRAINT, btree (col3, col4)
Foreign-key constraints:
"Table5_col3_fkey" FOREIGN KEY (col3) REFERENCES plans(col1)
"Table5_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
Referenced by:
TABLE "Table4_statuses" CONSTRAINT "Table4_s_p_col4_fkey" FOREIGN KEY (p_col4) REFERENCES Table5(col1)
Table "public.Table4_a"
Column | Type | Collation | Nullable | Default
-----------+-------------------------+-----------+----------+---------------------
col1 | uuid | | not null | generate1()
col4 | uuid | | not null |
col5 | type2 | | not null |
col6 | text | | not null |
Indexes:
"Table4_a_pkey" PRIMARY KEY, btree (col1)
"col4_key" UNIQUE CONSTRAINT, btree (col4, col5)
Foreign-key constraints:
"Table4_a_col4_fkey" FOREIGN KEY (col4) REFERENCES Table4(col1)
План выполнения запроса
EXPLAIN ( ANALYZE , VERBOSE , COSTS , BUFFERS , TIMING , SUMMARY )
SELECT "Table1"."col1", "Table1"."col2", "Table2"."col1" AS "Table2.col1", "Table2"."col3" AS "Table2.col3", "Table2"."col4" AS "Table2.col4", "Table3"."col1" AS "a.col1", "Table3"."col4" AS "a.col4", "Table3"."key" AS "a.col5", "Table3"."col6" AS "a.col6"
FROM "public"."Table4" AS "Table1"
INNER JOIN "public"."Table5" AS "Table2" ON "Table1"."col1" = "Table2"."col4"
INNER JOIN "public"."Table4_a" AS "Table3" ON "Table1"."col1" = "Table3"."col4" AND "Table3"."col5" = 'X1' AND "Table3"."col6" = 'X2';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=51291.67..61070.22 rows=11 width=138) (actual time=212.782..228.904 rows=0 loops=1)
Output: "Table1".col1, "Table1".col2, "Table2".col1, "Table2".col3, "Table2".col4, a.col1, a.col4, a.col5, a.col6
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=38808
-> Nested Loop (cost=50291.67..60069.12 rows=5 width=138) (actual time=198.025..198.030 rows=0 loops=3)
Output: "Table1".col1, "Table1".col2, "Table2".col1, "Table2".col3, "Table2".col4, a.col1, a.col4, a.col5, a.col6
Inner Unique: true
Buffers: shared hit=38808
Worker 0: actual time=191.589..191.593 rows=0 loops=1
Buffers: shared hit=12830
Worker 1: actual time=191.283..191.288 rows=0 loops=1
Buffers: shared hit=10816
-> Parallel Hash Join (cost=50291.24..60066.84 rows=5 width=118) (actual time=198.023..198.027 rows=0 loops=3)
Output: "Table2".col1, "Table2".col3, "Table2".col4, a.col1, a.col4, a.col5, a.col6
Inner Unique: true
Hash Cond: ("Table2".col4 = a.col4)
Buffers: shared hit=38808
Worker 0: actual time=191.587..191.590 rows=0 loops=1
Buffers: shared hit=12830
Worker 1: actual time=191.281..191.285 rows=0 loops=1
Buffers: shared hit=10816
-> Parallel Seq Scan on public.Table5 "Table2" (cost=0.00..9045.05 rows=278305 width=48) (never executed)
Output: "Table2".col1, "Table2".col3, "Table2".col4
-> Parallel Hash (cost=50291.20..50291.20 rows=3 width=70) (actual time=197.528..197.529 rows=0 loops=3)
Output: a.col1, a.col4, a.col5, a.col6
Buckets: 1024 Batches: 1 Memory Usage: 0kB
Buffers: shared hit=38728
Worker 0: actual time=191.259..191.260 rows=0 loops=1
Buffers: shared hit=12790
Worker 1: actual time=190.969..190.970 rows=0 loops=1
Buffers: shared hit=10776
-> Parallel Seq Scan on public.Table4_a a (cost=0.00..50291.20 rows=3 width=70) (actual time=194.885..194.885 rows=0 loops=3)
Output: a.col1, a.col4, a.col5, a.col6
Filter: ((a.col5 = 'X1'::type2) AND (a.col6 = 'X2'::text))
Rows Removed by Filter: 1027564
Buffers: shared hit=38728
Worker 0: actual time=187.238..187.239 rows=0 loops=1
Buffers: shared hit=12790
Worker 1: actual time=187.176..187.176 rows=0 loops=1
Buffers: shared hit=10776
-> Index Scan using Table4_pkey on public.Table4 "Table1" (cost=0.42..0.46 rows=1 width=20) (never executed)
Output: "Table1".col1, "Table1".col2
Index Cond: ("Table1".col1 = "Table2".col4)
Query Identifier: 1374759154717555017
Planning:
Buffers: shared hit=217
Planning Time: 2.928 ms
Execution Time: 228.955 ms
(49 rows)
Запрос нейросети
Как можно оптимизировать SQL запрос, используя прилагаемые таблицы, участвующие в запросе и план выполнения запроса
Результат работы нейросети DeepSeek
