Upgrade PostgreSQL 15→17 : анализ проблемных SQL запросов

ℹ️Первая статья по циклу о аномальной утилизации CPU+IO после обновления версии СУБД, используемой для 1С, с версии 15 на 17.ℹ️

Практический кейс: миграция с PostgreSQL 15 на 17, анализ деградации производительности пяти SQL-запросов по отчётам pgpro_pwr (старая и новая версии), формирование таблиц соответствия, сравнение планов выполнения и итоговый вердикт.

DeepSeek видит больше

Max: PG_EXPECTO

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.

GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Предисловие

Обновление версии СУБД — не только новые возможности, но и риски неожиданной деградации производительности. Даже без изменения текста SQL-запросов планы выполнения могут стать неоптимальными из-за эволюции оптимизатора, роста объёмов данных или изменения частоты вызовов. В этом материале на реальном примере показывается, как нейросеть DeepSeek, следуя формализованной инструкции, анализирует отчёты pgpro_pwr, выявляет критические проблемы и формулирует точные рекомендации.

Задача

Подготовка аналитического отчета

Подготовка аналитического отчета, содержащего сравнительный анализ планов выполнения SQL-запросов, зафиксированных до и после обновления версии системы управления базами данных (с 15-й на 17-ю).

Эмпирическую базу исследования составляют данные, полученные из отчетов утилиты pgpro_pwr, собранных в идентичный временной интервал (09:00–10:00) в условиях продуктивной нагрузки на целевую СУБД:

• pgpro_pwr_5635_5636.clear.html — отчет, сформированный до обновления (версия 15);
• pgpro_pwr.20-21.clear.html — отчет, сформированный после обновления (версия 17).

ℹ️Формирование аналитического отчета осуществляется с применением двух типов инструкций:

• философской инструкции (задающей общеметодологическую рамку анализа),
• доменной инструкции pg_expecto (обеспечивающей предметно-ориентированную обработку данных ).

Шаг-1 : сформировать список SQL запросов по отчету "pgpro_pwr.20-21.clear.html"

Входные данные:

pgpro_pwr.20-21.clear.html - Именовать в ответе "отчет-20-21"

Задача:

Сформировать список проблемных SQL запросов из pgpro_pwr.20-21.clear.html, по разделу "SQL query statistics":

SQL TEXT | Query ID

☑️Результат

📋queryid-1.txt

Примечание: под "проблемными запросами" нейросеть поняла запросы из раздела "Total exec time". В будущем, для формирования списка , необходимо конкретно указывать раздел отчета , для сбора исходных "Query ID".

Шаг-2 : сформировать список соответствия для SQL запросов по отчетам "pgpro_pwr.20-21.clear.html" и "pgpro_pwr_5635_5636.clear.html "

Входные данные:

pgpro_pwr_5635_5636.clear.html - Именовать в ответе "отчет-5635-5636"

queryid-1.txt - Список проблемных запросов в "отчете-20-21"

Задача:

1) Для каждого SQL запроса из "queryid-1.txt" найди соответсвующий по тексту запроса SQL запрос из pgpro_pwr_5635_5636.clear.html

2) Сформируй таблицу соответствия :

SQL TEXT | Query ID (отчете-20-21) | Query ID (отчет-5635-5636)

☑️Результат

📋queryid-2.txt

Шаг-3 : Сравнительный анализ планов выполнения между отчетами pgpro_pwr

Входные данные:

pgpro_pwr.20-21.clear.html - Именовать в ответе "отчет-20-21"

pgpro_pwr_5635_5636.clear.html - Именовать в ответе "отчет-5635-5636"

queryid-2.txt - таблица соответствия "Query ID" в отчетах pgpro_pwr.20-21.clear.html и pgpro_pwr_5635_5636.clear.html

Задача:

Для каждого SQL запроса из queryid-2.txt провести сравнительный анализ соответствующих планов выполнения между отчетами pgpro_pwr_5635_5636.clear.html и pgpro_pwr.20-21.clear.html

☑️Результат

📋sql-result.txt

Ниже представлен сравнительный анализ планов выполнения для каждого из пяти SQL-запросов на основе данных из отчета-5635-5636 (старый период) и отчета-20-21 (новый период). Для наглядности информация структурирована в виде списков.

1. Запрос с _InfoRg12194 / _Reference240 (DISTINCT)

Query ID (старый): eefa949b98bdf014

Query ID (новый): d620b963a19bcab3

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

отчет-5635-5636:

• План: Hash Left Join → Seq Scan по _reference189 → Hash

отчет-20-21:

• План: Nested Loop Left Join → Index Only Scan по _reference240_6 → Index Scan по _inforg17929_1

Ключевые метрики:

отчет-5635-5636:

• Вызовов: 24
• Общее время: 166.46 с
• Среднее время: 6935.85 мс
• Чтений с диска: ~22.3 GB
• Возвращено строк: 24

отчет-20-21:

• Вызовов: 270⬆️
• Общее время: 1270.64 с⬆️
• Среднее время: 4706.08 мс
• Чтений с диска: ~65.2 GB⬆️
• Возвращено строк: 1055⬆️

Вывод:

План радикально улучшился: вместо полного сканирования таблицы теперь используются эффективные индексные доступы. Среднее время выполнения одного запроса снизилось с 6.9 до 4.7 секунд. Однако рост числа вызовов в 11 раз привёл к увеличению общей нагрузки. Рекомендуется выяснить причину увеличения частоты обращений.

Категория по pg_expecto: Уровень-2 (проблема, на которую стоит обратить внимание).

2. Запрос с _Reference109 (Fld16284RRef OR Fld7677RRef)

Query ID (старый): a61a975f0d75464e

Query ID (новый): dcffaa3c1f2423b

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

отчет-5635-5636:

• План: Seq Scan по _reference109 с фильтром (NOT _fld11839) AND (NOT _marked) AND (NOT _fld18298) AND _fld7678rref = ANY(...)

отчет-20-21:

• План: Seq Scan по _reference109 с фильтром (_fld16284rref = $1) OR (_fld7677rref = $2)

Ключевые метрики:

отчет-5635-5636:

• Вызовов: 15
• Общее время: 24.28 с
• Среднее время: 1618.73 мс
• Чтений с диска: 0 (всё в кеше)

отчет-20-21:

• Вызовов: 1580💥
• Общее время: 1246.52 с⬆️
• Среднее время: 788.93 мс
• Чтений с диска: 0 (всё в кеше)

Вывод:

План не изменился (по-прежнему Seq Scan), но частота вызовов выросла в 100 раз💥. Среднее время выполнения уменьшилось вдвое, вероятно, из-за изменения данных или кеширования. Однако общее процессорное время (user_time) составило 1197 с – запрос стал основным потребителем CPU.⚠️

➡️Рекомендуется создать индекс на поля _fld16284rref и/или _fld7677rref, чтобы заменить Seq Scan на Index Scan.

Категория: Уровень-2.

3. Запрос с _InfoRg12488 (LIMIT с ORDER BY _Fld13806)

Query ID (старый): b932f4ddf25919b4

Query ID (новый): 12e2db113ff929b0

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

отчет-5635-5636:

• План: Bitmap Heap Scan по _inforg12488_1 → Sort (top-N heapsort)

отчет-20-21:

• План: Index Scan по _inforg12488_1 → Sort (аналогичная структура)

Ключевые метрики:

отчет-5635-5636:

• Вызовов: 777
• Общее время: 198.38 с
• Среднее время: 255.32 мс
• Чтений с диска: ~42.2 GB
• Возвращено строк: 1209

отчет-20-21:

• Вызовов: 30 185⬆️💥
• Общее время: 590.24 с⬆️
• Среднее время: 19.55 мс
• Чтений с диска: ~8.3 GB⬇️
• Возвращено строк: 2977

Вывод:

📈Производительность значительно улучшилась: среднее время выполнения сократилось более чем в 10 раз, а объём дисковых чтений уменьшился в 5 раз. Оптимизатор стал чаще выбирать прямой Index Scan, что более эффективно при текущих параметрах фильтрации.

Категория: Уровень-3 (положительная динамика).

4. Запрос с _InfoRg15516 (IN по _Fld15521RRef)

Query ID (старый): 54a4333e9d3a4220

Query ID (новый): 357ae191acedc0f3

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

• Оба отчёта: Seq Scan по _inforg15516 с фильтром _fld15521rref = ANY(...)

Ключевые метрики:

отчет-5635-5636:

• Вызовов: 1 (оценочно)
• Среднее время: ~37.3 мс
• Чтений с диска: ~30 MB

отчет-20-21:

• Вызовов: 22⬆️
• Общее время: 521.97 с
• Среднее время: 23 726.00 мс
• Чтений с диска: ~134 GB⬆️💥

Вывод:

💥Критическая деградация.💥

План остался прежним (Seq Scan), но объём читаемых данных и время выполнения выросли на порядки. Причина – рост таблицы или неселективные параметры фильтра.

➡️Рекомендуется срочно создать индекс по _InfoRg15516._Fld15521RRef.

Категория: Уровень-1 (критическая проблема)⚠️.

5. Запрос с _Reference15235 (IN по _Fld15266RRef)

Query ID (старый): 24ea632ceffbd7d9

Query ID (новый): 510eb4893c41718e

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

• Оба отчёта: Seq Scan по _reference15235 с фильтром _fld15266rref = $1

Ключевые метрики:

отчет-5635-5636:

• Вызовов: 24
• Общее время: 7.92 с
• Среднее время: 329.89 мс
• Чтений с диска: ~5.2 GB

отчет-20-21:

• Вызовов: 22
• Общее время: 459.58 с⬆️
• Среднее время: 20 889.86 мс
• Чтений с диска: ~94.8 GB⬆️💥

Вывод:

💥Ситуация аналогична предыдущей:💥

план не изменился, но объём данных, сканируемых при каждом выполнении, вырос многократно. Среднее время выполнения увеличилось с 0.33 с до 20.9 с.⚠️

Рекомендуется немедленно создать индекс по _Reference15235._Fld15266RRef.

Категория: Уровень-1 (критическая проблема).⚠️

Общие рекомендации (сводка)

💥Срочно (Уровень-1):

1. Создать индексы на _InfoRg15516(_Fld15521RRef) и _Reference15235(_Fld15266RRef).

⚠️Приоритетно (Уровень-2):

• Рассмотреть создание составного индекса на _Reference109 для полей _fld16284rref и _fld7677rref.
• Проанализировать рост частоты вызовов для запроса №1.

📈Положительная тенденция (Уровень-3):

1. Для запроса №3 план оптимален, динамика улучшения сохраняется.

Шаг-4 : Сформировать сводную таблицу "Query ID" , "Plan ID"

Входные данные:

pgpro_pwr.20-21.clear.html - Именовать в ответе "отчет-20-21"

pgpro_pwr_5635_5636.clear.html - Именовать в ответе "отчет-5635-5636"

queryid-2.txt - таблица соответствия "Query ID" между pgpro_pwr.20-21.clear.html и pgpro_pwr_5635_5636.clear.html

Задача:

Cформируй сводную таблицу:

SQL-TEXT | Query ID (отчет-20-21) | Query ID (отчет-5635-5636) | Plan ID (отчет-20-21) | Plan ID (отчет-5635-5636)

Шаг-5: Формирование аналитического отчета с использованием инструкций для нейросети DeepSeek

Инструкция PG_EXPECTO

Философская инструкция

Загрузить инструкции

Подготовить аналитический отчет

Входные данные:

sql-result.txt - отчет по анализу планов выполнения проблемых SQL запросов между отчетами "отчета-5635-5636 (старый период)" и "отчета-20-21** (новый период)"

Задача:

1) Проанализируй отчет.

2) Сформируй итоговый вывод о причинах деградации производительности SQL запросов в "отчете-20-21** (новый период)". По каждому выводу, приведи последовательность тезисов , на основании которых сделан был вывод.

ℹ️Вердикт: Деградация производительности в новом периоде вызвана комбинацией двух факторов:

1. ⚠️Резкий рост частоты выполнения ряда запросов (в 10–100 раз) без соответствующей оптимизации их планов.
2. ⚠️Отсутствие необходимых индексов для изменившихся условий фильтрации, что привело к полным сканированиям многократно выросших таблиц.

Ниже – детальный анализ по каждому запросу с цепочкой тезисов, ведущих к выводу.

1. Запрос с _InfoRg12194 / _Reference240 (DISTINCT)

Тезисы (на основе отчета):

• Подтверждено: В старом периоде выполнялся 24 раза, в новом – 270 раз (рост в 11 раз).⬆️💥
• Подтверждено: План изменился с Hash Join + Seq Scan на Nested Loop + Index Only Scan / Index Scan.
• Подтверждено: Среднее время одного выполнения снизилось с 6.9 с до 4.7 с.
• Подтверждено: Общее время выполнения выросло с 166 с до 1270 с, объём чтений – с 22.3 GB до 65.2 GB.💥

☑️Вывод по запросу 1:

План стал эффективнее для единичного вызова, однако из-за кратного увеличения числа вызовов суммарная нагрузка возросла.

Вероятно: Причина роста общего времени – увеличение частоты обращений, а не ухудшение плана. ℹ️Рекомендуется выяснить бизнес-причину роста вызовов и, возможно, добавить кеширование на уровне приложения.

2. Запрос с _Reference109 (Fld16284RRef OR Fld7677RRef)

Тезисы:

• Подтверждено: Число вызовов выросло с 15 до 1580 (в 105 раз).⬆️💥
• Подтверждено: План не изменился – по-прежнему Seq Scan по таблице _reference109.
• Подтверждено: Среднее время выполнения снизилось с 1.6 с до 0.79 с (вероятно, данные попали в кеш – shared_blks_read = 0 в новом периоде).
• Подтверждено: Общее процессорное время (user_time) выросло до 1197 с, запрос стал основным потребителем CPU.💥

☑️Вывод по запросу 2:

📉Ухудшение общей производительности вызвано массовым ростом числа вызовов при сохранении неоптимального плана (Seq Scan). Даже при кешировании данных последовательное сканирование на каждое выполнение создаёт высокую нагрузку на CPU.

Вероятно: Необходим индекс по полям _fld16284rref и _fld7677rref (возможно, составной), чтобы заменить Seq Scan на Index Scan и снизить потребление CPU.

3. Запрос с _InfoRg12488 (LIMIT с ORDER BY)

Тезисы:

• Подтверждено: Число вызовов выросло с 777 до 30 185 (в 39 раз).⬆️💥
• Подтверждено: План улучшился: чаще используется прямой Index Scan вместо Bitmap Heap Scan.
• Подтверждено: Среднее время снизилось с 255 мс до 20 мс (более чем в 10 раз).
• Подтверждено: Объём чтений упал с 42.2 GB до 8.3 GB.

☑️Вывод по запросу 3:

📈Положительная динамика. Несмотря на резкий рост вызовов, план стал настолько эффективнее, что общая нагрузка выросла умеренно (с 198 с до 590 с).

👍Подтверждено: Проблем с этим запросом нет. Дополнительных действий не требуется.

4. Запрос с _InfoRg15516 (IN по _Fld15521RRef)

Тезисы:

• Подтверждено: В старом периоде выполнялся редко (с общим временем ~0.8 с и чтением 30 MB).
• Подтверждено: В новом периоде – 22 вызова, но общее время 522 с, среднее – 23.7 секунды.
• Подтверждено: Объём чтений вырос до 134 GB (против 30 MB ранее).
• Подтверждено: План остался Seq Scan по таблице _inforg15516.

☑️Вывод по запросу 4:

📉Критическая деградация. 💥Причина – отсутствие индекса по полю _Fld15521RRef в условиях резко возросшего объёма данных (таблица значительно выросла или параметры фильтра затрагивают большую её часть). Каждое выполнение вынуждено читать всю таблицу.

Вероятно: Срочно создать индекс по _InfoRg15516._Fld15521RRef. Проверить статистику таблицы и актуальность ANALYZE.

5. Запрос с _Reference15235 (IN по _Fld15266RRef)

Тезисы:

• Подтверждено: Число вызовов примерно одинаково (24 → 22).
• Подтверждено: Среднее время выросло с 0.33 с до 20.9 с (в 63 раза).
• Подтверждено: Объём чтений увеличился с 5.2 GB до 94.8 GB.
• Подтверждено: План не изменился – Seq Scan по _reference15235.

☑️Вывод по запросу 5:

📉Критическая деградация из-за роста объёма данных при сохранении неоптимального плана.💥 Таблица _Reference15235 существенно увеличилась, и Seq Scan стал недопустимо дорогим.

Вероятно: Необходим индекс по полю _Fld15266RRef. Проверить, не требует ли обновления статистика планировщика.

Итоговый вывод о причинах общей деградации

☑️Причина 1: Многократный рост частоты вызовов

• Затронутые запросы: #1, #2, #3
• Обоснование: Количество выполнений увеличилось в 11–105 раз. Даже при улучшении плана (#1, #3) или сохранении плана (#2) общая нагрузка выросла пропорционально.

☑️Причина 2: Отсутствие необходимых индексов при росте объёма данных

• Затронутые запросы: #4, #5
• Обоснование: План остался Seq Scan, но таблицы значительно выросли (объём чтений увеличился с мегабайт до гигабайт). Среднее время выполнения выросло в 60–70 раз.

☑️Причина 3: Неоптимальный план для массовых вызовов

• Затронутый запрос: #2
• Обоснование: Seq Scan при 1580 вызовах в кеше создаёт чрезмерную нагрузку на CPU, не снижая время выполнения пропорционально.

Рекомендации (на основе отчета):

• Немедленно: создать индексы для запросов #4 (_InfoRg15516._Fld15521RRef) и #5 (_Reference15235._Fld15266RRef).
• В ближайшее время: оценить целесообразность составного индекса для запроса #2 (_Reference109._fld16284rref, _fld7677rref).
• Мониторинг: выяснить бизнес-причины роста вызовов запросов #1 и #2; рассмотреть кеширование на стороне приложения.
• Профилактика: выполнить ANALYZE для таблиц, показавших значительный рост данных.

ℹ️Оценка достоверности информации

• ☑️Подтверждено: Фактические значения времени, чтений и планов – взяты напрямую из предоставленного отчета (внешний источник в контексте, противоречий не обнаружено).
• ✅Вероятно: Интерпретации (например, «таблица выросла», «данные в кеше») – основаны на косвенных признаках и логике, но не подтверждены прямыми метриками (размер таблиц, pg_stat_user_tables). Для окончательного подтверждения требуется дополнительная информация.
• ❓Предположение: Гипотезы о бизнес-причинах роста вызовов – без данных о приложении являются лишь предположением.

При необходимости готов провести более глубокий анализ при предоставлении системных метрик (pg_stat_*, iostat, конфигурация PostgreSQL).

Дополнительно: аналитика по профилю нагрузки

На основании анализа можно ли сделать вывод , что профиль нагрузки во время "отчета-20-21 (новый период)" кардинально изменился ?

☑️Вывод: Да, профиль нагрузки кардинально изменился. Основанием служат резкие изменения в частоте выполнения ключевых запросов и объёмах обрабатываемых данных.

Обоснование (тезисы из отчета)

• ☑️Подтверждено: Запрос с _Reference109 вырос по числу вызовов с 15 до 1580 (в 105 раз)💥, став основным потребителем CPU в новом периоде.
• ☑️Подтверждено: Запрос с _InfoRg12488 вырос с 777 до 30 185 вызовов (в 39 раз), хотя план улучшился.
• ☑️Подтверждено: Запрос с _InfoRg12194 / _Reference240 вырос с 24 до 270 вызовов (в 11 раз).
• ☑️Подтверждено: Запросы с _InfoRg15516 и _Reference15235 демонстрируют не рост частоты, а многократное увеличение объёма обрабатываемых данных (чтения выросли с мегабайт до десятков и сотен гигабайт), что также свидетельствует об изменении характера нагрузки (например, рост таблиц или изменение параметров фильтрации).
• ✅Вероятно: Совокупность этих изменений указывает на сдвиг профиля нагрузки от единичных тяжёлых запросов к массовым более лёгким запросам с одновременным появлением новых "тяжёлых" запросов из-за отсутствия индексов.

ℹ️Ограничения достоверности

• ☑️Подтверждено: Цифры по частоте вызовов и объёмам чтения взяты из отчета.
• ✅Вероятно: Утверждение о "кардинальном изменении профиля" является логическим обобщением подтверждённых данных, но для абсолютной уверенности желательно сопоставить с системными метриками (например, распределение времени CPU по типам запросов, общее количество транзакций в периоды).
• ❓Неизвестно: Бизнес-причины изменений (новый функционал, рост базы, изменение пользовательской активности) без дополнительной информации остаются за пределами анализа.

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

Общий технический итог

Проведённый сравнительный анализ планов выполнения SQL-запросов на основе отчётов pgpro_pwr, сформированных до (PostgreSQL 15) и после (PostgreSQL 17) миграции, выявил разнонаправленную динамику производительности.

ℹ️Для пяти исследуемых запросов установлено, что ключевыми факторами деградации стали:

• ⚠️многократный (в 11–105 раз) рост частоты вызовов при сохранении неоптимальных планов последовательного сканирования,
• ⚠️отсутствие индексов по фильтруемым полям на фоне объективного увеличения объёмов табличных данных.

ℹ️В то же время для одного из запросов зафиксировано улучшение плана (переход с Bitmap Heap Scan на прямой Index Scan), что позволило компенсировать 39-кратный рост вызовов и подтвердило эффективность современного оптимизатора PostgreSQL 17 при наличии корректной индексной поддержки.

Послесловие

ℹ️Таким образом, представленная методика анализа — от выделения проблемных запросов по статистике pgpro_pwr до установления соответствия идентификаторов и сравнения планов — может быть формализована в виде инструкции для нейросетей класса DeepSeek, что открывает возможность автоматизации регулярных аудитов производительности при миграциях и обновлениях СУБД.

⚠️Следует, однако, учитывать, что окончательный вердикт о причинах деградации требует дополнительной валидации с привлечением системных метрик (pg_stat_user_tables, результаты EXPLAIN (ANALYZE, BUFFERS) в реальном времени) и данных о бизнес-логике приложения. ℹ️Предложенный подход не заменяет эксперта-администратора, но служит эффективным инструментом первичной диагностики и приоритезации проблем.