Upgrade PostgreSQL 15→17 : анализ запросов IO

Методика разностного анализа производительности SQL-запросов на основе отчётов pgpro_pwr при невозможности прямого сопоставления по Query ID: эмпирическое исследование регрессии ввода-вывода при переходе с PostgreSQL 15 на PostgreSQL 17 в условиях продуктивной нагрузки (09:00–10:00)

Ожидания ввода-вывода: планы те же, задержки выше.

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 | Дзен

Предисловие

В практике сопровождения реляционных СУБД особую сложность представляет оценка влияния обновления версии на производительность прикладных запросов в случае, если стандартные механизмы идентификации (например, Query ID) не сохраняются между релизами. В настоящем исследовании предпринята попытка преодолеть указанное ограничение путём семантического сопоставления текстов SQL-запросов из двух отчётов pgpro_pwr, сформированных до (версия 15) и после (версия 17) обновления, в идентичный часовой интервал. Работа выполнена в рамках методологической рамки, задаваемой философской инструкцией (эпистемологический контроль, защита от галлюцинаций) и доменной инструкцией pg_expecto, ориентированной на статистический анализ ожиданий ввода-вывода.

Первая статья цикла:

Задача

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

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

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

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

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

Важное примечание

Стандартная методика использования разностного отчета pgpro_pwr не пригодна для данной задачи, по причине не соответствия "Query ID" в отчетах, после обновления версии СУБД.

Утилизация диска PGDATA

Рис.1 Метрика Disk utilization до и после обновления версии СУБД , при продуктивной нагрузке на СУБД.

Подготовка для каждого шага

Загрузка общей инструкции

ИНСТРУКЦИЯ v.2

Ты — эксперт по производительности СУБД PostgreSQL.

Твоя задача — анализировать входные данные и давать точный, предметный анализ результатов.

Правила:

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

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

Если в данных есть аномалии или противоречия — отметь их и объясни возможные сценарии, но без домыслов.

Если данных недостаточно — перечень необходимых дополнительных метрик или срезов.

Используй профессиональную терминологию (shared_buffers, effective_cache_size, seq scan, index scan, checkpoint, autovacuum, deadlocks и т.п.). При ссылке на параметры указывай их единицы измерения.

Указывай период наблюдения.

Если у тебя нет точной информации или данных недостаточно для уверенного ответа, не придумывай. Скажи: «Недостаточно данных для ответа».

Даже если таблицы нагляднее — используй только списки.

Стиль: деловой, технически точный, без лишних пояснений.

Если пользователь не предоставил сами данные, а только вопрос — запроси конкретные метрики и период наблюдения.

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

1. Загрузка общей инструкции

2. Промпт

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

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

Задача:

Сформировать список SQL запросов из pgpro_pwr.20-21.clear.html - по разделу "Top SQL by I/O wait time":

SQL TEXT | Query ID

☑️Результат

📋queryid-1.txt

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

1. Загрузка общей инструкции

2. Промпт

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

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

1. Загрузка общей инструкции

2. Промпт

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

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

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

Инструкция PG_EXPECTO

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

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

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

_pg_expecto_instruction.txt - доменная инструкция

Philosophical_instruction_BETA_v5.1.md - философская инструкция

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

При подготовке ответа с использованием "Philosophical_instruction_BETA_v5.1.md" заменяй цветовые символы на соответсвующие им текстовые сокращения:

"Подтверждено"

"Вероятно"

"Предположение"

"Неизвестно"

Задача:

# Сравнительный анализ SQL

## По каждому SQL выражению из отчета "sql-result.txt"

### Укажи "Query ID" в отчетах "отчета-5635-5636 (старый период)" и "отчета-20-21 (новый период)"

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

# Сформулируй гипотезу о влиянии профиля нагрузки на результаты анализа

# Сформулируй итоговый вывод

Для ответа используй списки , вместо таблиц.

Сравнительный анализ SQL-запросов по периодам

В анализе сопоставляются данные из отчёта 5635-5636 (старый период) и отчёта 20-21 (новый период). Общая тенденция — значительное увеличение времени выполнения и операций ввода-вывода для большинства запросов при схожих или даже снизившихся количествах вызовов.

Запрос 1

SELECT ... FROM _InfoRg12488 ... WHERE ... ORDER BY ... LIMIT $5

Query ID (старый / новый): b932f4ddf25919b4 / 12e2db113ff929b0

Сравнительный анализ:

План Index Scan: вызовы ~30 тыс., общее время выросло с 18.3 с до 590.2 с (в 32 раза), среднее время — с 0.6 мс до 19.6 мс.

План Bitmap Heap Scan: вызовы снизились с 777 до 726, общее время выросло с 198.4 с до 309.4 с (в 1.6 раза).

Время ввода-вывода (DataFileRead) в новом периоде для Index Scan составило 554 с против 6.5 с в старом.

Планы выполнения не изменились.

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

Тезисы, обосновывающие вывод:

Общее время выполнения Index Scan выросло в 32 раза при том же числе вызовов (Подтверждено).

Время ожидания ввода-вывода (DataFileRead) увеличилось на два порядка (Подтверждено).

Объём прочитанных с диска блоков значительно вырос (Подтверждено).

План не изменился, следовательно, причина — в данных или окружении, а не в оптимизаторе (Вероятно).

Запрос 2

INSERT INTO _InfoRg16813 (...) VALUES(...)

Query ID (старый / новый): 7f58891f6efcac66 / 11882bc12ecb7128

Сравнительный анализ:

Вызовы: 13 025 → 11 463 (снижение).

Общее время: 36.1 с → 89.0 с (рост в 2.5 раза).

Среднее время: 2.8 мс → 7.8 мс.

Время ввода-вывода: 27.4 с → 84.2 с.

Объём WAL вырос со 170 MB до 207 MB при меньшем числе вставок.

Итоговый вывод: Производительность значительно ухудшилась (Подтверждено).

Тезисы:

Рост общего времени выполнения более чем в 2 раза несмотря на снижение числа вызовов (Подтверждено).

Основной вклад вносит возросшее время ввода-вывода (Подтверждено).

Увеличение объёма WAL на одну вставку указывает на возможный рост размера строк или количества индексов (Вероятно).

Запрос 3

INSERT INTO _InfoRg15087 (...) VALUES(...)

Query ID (старый / новый): 141d19a4e6c00c / 9f20c29501736f7

Сравнительный анализ:

Вызовы: 5 350 → 4 218.

Общее время: 0.62 с → 1.44 с (рост в 2.3 раза).

Среднее время: 0.12 мс → 0.34 мс.

Время ввода-вывода: 0.10 с → 1.10 с.

Итоговый вывод: Умеренное ухудшение (Подтверждено).

Тезисы:

Абсолютные значения времени остаются небольшими, но относительный рост значителен (Подтверждено).

Увеличение времени ввода-вывода — основной фактор (Подтверждено).

Запрос 4

SELECT ... FROM _Document12720_VT13628 ... INNER JOIN ...

Query ID (старый / новый): 32f968e547de7f6e / 7e6a6b1d772cde5f

Сравнительный анализ:

Вызовы: 125 → 89.

Общее время: 59.7 с → 80.6 с (рост в 1.35 раза).

Среднее время: 477 мс → 906 мс (почти вдвое).

Время DataFileRead: 7.0 с → 42.4 с (рост в 6 раз).

План остался Nested Loop с последовательным сканированием _document12720_vt13628.

Итоговый вывод: Существенное ухудшение (Подтверждено).

Тезисы:

Среднее время выполнения выросло вдвое при снижении числа вызовов (Подтверждено).

Время чтения с диска увеличилось в 6 раз, что указывает на рост объёма данных в сканируемой таблице (Вероятно).

Отсутствие индекса на _Fld14445RRef приводит к полному сканированию, которое становится всё дороже (Предположение).

Запрос 5

INSERT INTO _InfoRg4755 (...) VALUES(...)

Query ID (старый / новый): 3c7585cbdf15f360 / f0a1f1b31a04c595

Сравнительный анализ:

Вызовы: 24 442 → 22 723.

Общее время: 11.5 с → 38.1 с (рост в 3.3 раза).

Время ввода-вывода: 7.9 с → 34.8 с.

Итоговый вывод: Значительное ухудшение (Подтверждено).

Тезисы:

Рост времени выполнения в 3.3 раза при сопоставимом числе вызовов (Подтверждено).

Деградация полностью обусловлена возросшим временем ввода-вывода (Подтверждено).

Запрос 6

SELECT ... FROM _Reference109 ... WHERE _IDRRef = $1

Query ID (старый / новый): fc1107d90ff53d75 / eeeb0840e40f70b8

Сравнительный анализ:

Вызовы: 40 067 → 46 650 (рост на 16%).

Общее время: 2.45 с → 32.29 с (рост в 13 раз).

Ключевое изменение плана: в старом периоде использовался индекс первичного ключа _reference109ng_pkey1, в новом — _reference109_no_pkey.

Количество прочитанных блоков выросло с 2 292 до 34 526, время ввода-вывода — с 0.11 с до 28.49 с.

Итоговый вывод: Критическое ухудшение из-за смены плана выполнения (Подтверждено).

Тезисы:

Оптимизатор выбрал менее эффективный индекс, что привело к многократному росту чтений с диска (Подтверждено).

Время выполнения выросло в 13 раз при увеличении числа вызовов всего на 16% (Подтверждено).

Необходимо проверить актуальность статистики и причины выбора неоптимального индекса (Вероятно).

Запрос 7

INSERT INTO _InfoRg12511 (...) VALUES(...)

Query ID (старый / новый): 23b3d7b49da6bbc9 / b4a7d2a1fa39b507

Сравнительный анализ:

Вызовы: 11 137 → 9 416.

Общее время: 14.0 с → 28.0 с (рост в 2 раза).

Время ввода-вывода: 11.1 с → 25.9 с.

Итоговый вывод: Ухудшение (Подтверждено).

Тезисы:

Двукратный рост времени выполнения при снижении числа вызовов (Подтверждено).

Увеличение времени ввода-вывода — основная причина (Подтверждено).

Запрос 8

SELECT _IDRRef FROM _Reference13132 WHERE _Fld13210 = $1

Query ID (старый / новый): 24ea632ceffbd7d9 / 139a45834a25c812

Сравнительный анализ:

Вызовы: 24 → 17.

Общее время: 7.9 с → 25.3 с (рост в 3.2 раза).

Среднее время: 330 мс → 1 488 мс.

Время ввода-вывода: 4.3 с → 21.1 с.

План — последовательное сканирование из-за отсутствия индекса на _Fld13210.

Итоговый вывод: Значительное ухудшение (Подтверждено).

Тезисы:

Рост времени выполнения в 3.2 раза при снижении числа вызовов (Подтверждено).

Полное сканирование таблицы становится всё более затратным по мере роста данных (Вероятно).

Отсутствие индекса — критическая проблема для данного запроса (Подтверждено).

Запрос 9

DELETE FROM BinaryData WHERE f_num < $1 AND f_num > $2

Query ID (старый / новый): 9d685da1f2ad825c / 67ff13d311a03aaf

Сравнительный анализ:

Вызовы: 353 → 252.

Общее время: 16.3 с → 24.2 с (рост в 1.5 раза).

Время ввода-вывода: 1.9 с → 18.9 с (рост в 10 раз).

Итоговый вывод: Ухудшение, особенно по вводу-выводу (Подтверждено).

Тезисы:

Десятикратный рост времени ввода-вывода при удалении (Подтверждено).

Общее время выросло, несмотря на снижение числа вызовов и объёма WAL (Подтверждено).

Запрос 10

INSERT INTO _InfoRg12488 (...) VALUES(...)

Query ID (старый / новый): d14e394f88545ee8 / 5119d72e81020dad

Сравнительный анализ:

Вызовы: 7 701 → 7 171 (практически не изменилось).

Общее время: 4.5 с → 22.9 с (рост в 5 раз).

Время ввода-вывода: 2.0 с → 21.6 с.

Итоговый вывод: Резкое ухудшение (Подтверждено).

Тезисы:

Пятикратный рост времени выполнения при неизменном числе вызовов (Подтверждено).

Причина — резкое увеличение времени ввода-вывода (Подтверждено).

Запрос 11

SELECT ... FROM _InfoRg13163 ... WHERE ...

Query ID (старый / новый): 54a4333e9d3a4220 / fe556fec0f4e4859

Сравнительный анализ:

Вызовы: 3 006 → 3 224.

Общее время: 251.3 с → 239.5 с (небольшое снижение).

Среднее время: 83.6 мс → 74.3 мс.

Время ввода-вывода = 0 в обоих периодах (все данные в кеше).

Итоговый вывод: Производительность стабильна, без деградации (Подтверждено).

Тезисы:

Общее и среднее время остались на прежнем уровне (Подтверждено).

Запрос не затрагивает диск, поэтому не подвержен влиянию возросшей нагрузки на ввод-вывод (Вероятно).

Запрос 12

INSERT INTO _InfoRg12756 (...) VALUES(...)

Query ID (старый / новый): 966b06c5e3b1f2cb / 3700c935e8779144

Сравнительный анализ:

Вызовы: 2 575 → 2 316.

Общее время: 3.4 с → 7.7 с (рост в 2.3 раза).

Время ввода-вывода: 1.8 с → 7.1 с.

Итоговый вывод: Ухудшение (Подтверждено).

Тезисы:

Рост общего времени более чем вдвое при снижении числа вызовов (Подтверждено).

Основной фактор — увеличение времени ввода-вывода (Подтверждено).

Гипотеза о влиянии профиля нагрузки

Формулировка гипотезы (Вероятно):

Наблюдаемая повсеместная деградация производительности запросов в новом периоде не является следствием изменения планов выполнения (за исключением Запроса 6), а вызвана значительным ростом времени операций ввода-вывода с диском. Это может быть обусловлено одним или несколькими из следующих факторов:

1. Рост объёма данных в таблицах (например, _InfoRg12488, _Document12720_VT13628, _Reference13132), что увеличивает количество читаемых блоков при сканированиях.
2. Увеличение конкурентной нагрузки на дисковую подсистему (больше одновременных операций чтения/записи), что повышает задержки.
3. Изменение конфигурации PostgreSQL, влияющее на ввод-вывод.
4. Фрагментация индексов или таблиц, приводящая к менее эффективному доступу к данным.
5. Устаревшая статистика планировщика, из-за чего в Запросе 6 был выбран неоптимальный индекс.

Обоснование:

• В 11 из 12 запросов планы выполнения остались неизменными (Подтверждено).
• Во всех проблемных запросах резко выросло время ожидания DataFileRead или общее время ввода-вывода (Подтверждено).
• Запрос 11, полностью работающий в кеше, не показал деградации (Подтверждено).

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

Общее заключение (Подтверждено данными отчётов, интерпретация — Вероятно):

Производительность SQL-запросов в период 20-21 существенно снизилась по сравнению с периодом 5635-5636. Ключевая причина — многократное увеличение времени ожидания ввода-вывода при чтении данных с диска. Планы выполнения запросов в основном не изменились, что исключает регресс оптимизатора как основную причину (кроме единичного случая с Запросом 6).

Рекомендации (Предположение):

1. Проверить и обновить статистику для всех затронутых таблиц (ANALYZE), особенно для _InfoRg12488, _Document12720_VT13628, _Reference13132, _Reference109.
2. Провести обслуживание индексов (REINDEX) для индексов, используемых в деградировавших запросах (например, _inforg12488_1, _reference109_no_pkey).
3. Проанализировать производительность дисковой подсистемы (iostat, vmstat) в новом периоде: возросла ли утилизация, увеличились ли задержки (await).
4. Рассмотреть создание отсутствующих индексов для запросов с полным сканированием (Запрос 4 — индекс на _Fld14445RRef, Запрос 8 — индекс на _Fld13210).
5. Проверить конфигурацию PostgreSQL на предмет изменений параметров, влияющих на ввод-вывод и контрольные точки (checkpoint_timeout, max_wal_size, bgwriter).

Ограничения анализа (Предположение):

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

Технический итог

Из 12 проанализированных SQL-запросов, отобранных по разделу «Top SQL by I/O wait time» в новом отчёте (20-21), для 11 зафиксировано статистически значимое ухудшение производительности при неизменных или снизившихся количествах вызовов. Ключевой фактор деградации — многократный рост времени ожидания ввода-вывода (DataFileRead): от 1,6 до 32 раз в зависимости от запроса. Планы выполнения остались неизменными для всех запросов, кроме одного (запрос 6, SELECT из _Reference109), где оптимизатор переключился с индекса первичного ключа на менее селективный индекс, что привело к росту прочитанных блоков с 2 292 до 34 526. Единственный запрос, не показавший деградации (SELECT из _InfoRg13163), характеризовался нулевым временем ввода-вывода в обоих периодах, что подтверждает дискретный характер наблюдаемой регрессии.

Послесловие

Таким образом, совокупность полученных данных свидетельствует в пользу гипотезы о том, что обновление версии СУБД само по себе не вызвало деградации оптимизатора (за исключением единичного случая выбора неоптимального индекса). Наиболее вероятной причиной повсеместного роста времени ввода-вывода является увеличение объёмов данных в целевых таблицах, возросшая конкурентная нагрузка на дисковую подсистему или изменение параметров конфигурации, влияющих на кеширование и контрольные точки. Для верификации гипотезы требуются дополнительные метрики: размер таблиц и индексов, показатели утилизации диска (iostat, await), логи контрольных точек и автоочистки, а также актуальная конфигурация shared_buffers и effective_cache_size. Представленная методика сопоставления запросов по тексту в отсутствие единого Query ID может быть рекомендована как временное аналитическое решение при миграциях между мажорными версиями PostgreSQL.