Upgrade PostgreSQL 15→17: Анализ планов выполнения при planner_upper_limit_estimation = on/off

Сравнительный анализ производительности SQL-запросов и планов выполнения при включённом и отключённом параметре planner_upper_limit_estimation на основе разностных отчётов pgpro_pwr (снимки 69–71 vs 74–76)

Seq Scan при on. Index Scan при off

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

Предыдущая статья цикла

Предисловие

В рамках исследования миграции с PostgreSQL 15 на 17 (с использованием компонентов 1С) проведена оценка влияния параметра оптимизатора planner_upper_limit_estimation на эффективность исполнения запросов в рабочей нагрузке. Для анализа использованы разностные отчёты pgpro_pwr, фиксирующие статистику выполнения и планы в двух временных интервалах: снимки 69–71 (параметр включён, значение on) и снимки 74–76 (параметр отключён, значение off). Рассмотрены два ключевых раздела отчёта: «Top SQL by execution time» и «Top SQL by I/O wait time». В представленных материалах проведён сравнительный анализ запросов, изменивших план выполнения, а также оценены показатели времени выполнения, ввода-вывода и буферного кэша.

Задача

Проанализировать статистику выполнения SQL запросов по разделам "Top SQL by execution time" и "Top SQL by IO wait time" разностного отчета pgpro_pwr:

• Снимки 69-71 : planner_upper_limit_estimation = on

Период отчета по снимкам pgpro_pwr 69-71

• Снимки 74-76 : planner_upper_limit_estimation = off

Период отчета по снимкам pgpro_pwr 74-76

Входные данные для подготовки аналитического отчета

pgpro_pwr_diff.69-71-74-76.clear.html - разностный отчет pgpro_pwr по снимкам 69-71 и 74-76

Инструкция PG_EXPECTO

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

ТаблицаG.33.txt - описание раздела "Top SQL by execution time (SQL-запросы с наибольшей длительностью выполнения)" отчета pgpro_pwr

ТаблицаG.36.txt - описание раздела "Таблица G.36. Top SQL by I/O wait time (SQL-запросы с наибольшим временем I/O)" отчета pgpro_pwr

1. Аналитический отчет по разделу "Top SQL by execution time (SQL-запросы с наибольшей длительностью выполнения)" отчета pgpro_pwr

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

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

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

pgpro_pwr_diff.69-71-74-76.clear.html - разностный отчет pgpro_pwr по снимкам "69-71" и "74-76"

ТаблицаG.33.txt - описание раздела "Top SQL by execution time (SQL-запросы с наибольшей длительностью выполнения)" отчета pgpro_pwr

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

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

"Вероятно"

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

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

УТОЧНЕНИЕ

При подготовке ответа учитывай, что разное значение параметра planner_upper_limit_estimation в отчетах pgpro_pwr :

"69-71" - planner_upper_limit_estimation = on

"74-76" - planner_upper_limit_estimation = off

Задача:

# Для SQL запросов представленных в интервалах 1 и 2 в разделе "Top SQL by execution time"

# Проведи сравнительный анализ SQL запросов по статистике выполнения и ожиданий.

# Проведи сравнительный анализ планов выполнения

# Сформулируй общий итог по сравнению интервалов по разделу "Top SQL by execution time" с указанием последовательности тезисов для каждого вывода

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

Сравнительный анализ SQL-запросов по разделу «Top SQL by execution time» (интервалы 1: «69-71», 2: «74-76»)

1. Контекст и ключевое изменение конфигурации

В интервале 1 параметр planner_upper_limit_estimation = on, в интервале 2 – off. Этот параметр влияет на способ оценки числа строк, возвращаемых операциями с неселективными условиями: при включённом значении планировщик ограничивает оценку кардинальности на основе общей мощности отношения, что может менять общую стоимость плана запроса. Отключение даёт оптимизатору больше свободы, но потенциально повышает риск выбора неоптимального плана при неточной статистике.

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

Рассмотрены запросы, которые входили в топ общего времени выполнения хотя бы в одном из интервалов и присутствуют в обоих интервалах.

2.1. Запрос 12e2db113ff929b0 (_InfoRg12488)

Основной план в интервале 1 (c47b19a34bf7ba7d) и альтернативные планы в интервале 2 (36da31b89a371dcf, 13b0bc54e5eb0fe7)

Статистика выполнения для основного плана:

• Общее время (с): 1002.78 → 75.48 (снижение ~92%)
• Число вызовов: 60 919 → 61 703 (без существенных изменений)
• Среднее время (мс): 16.46 → 1.22 (снижение ~93%)
• Время чтения (с): 960.30 → 49.24 (снижение ~95%)
• Прочитано shared блоков: 2 374 910 → 1 139 749 (снижение ~52%)
• Доля попаданий в буферный кэш: 75% → 88% (рост)
• План в интервале 1: Limit -> Sort -> Seq Scan on _InfoRg12488
• Планы в интервале 2: Bitmap Heap Scan (основной) и ещё один план с Index Scan (_InfoRg12488_1)
• Ожидания: время ожидания DataFileRead сократилось с 936.07 с до 48.36 с; ожидания BufferMapping стали незначительными.

Вывод: радикальное снижение времени и I/O обусловлено сменой плана выполнения — вместо последовательного сканирования стал использоваться Bitmap-доступ, что более эффективно для селективных условий.

2.2. Запрос a672f72c2ed94ff1 (_Reference109)

Статистика выполнения:

• Общее время (с): 699.24 → 171.25
• Число вызовов: 240 → 240 (без изменений)
• Среднее время (с): 2.91 → 0.71
• Время ввода-вывода (с): 528.24 → 33.21
• Прочитано shared блоков: 970 956 → 321 531
• План: идентичен в обоих интервалах (Index Scan с агрегацией)
• Ожидания: DataFileRead снизились с 517.40 с до 32.89 с.

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

2.3. Запрос fe556fec0f4e4859 (_InfoRg13163)

Статистика выполнения:

• Общее время (с): 560.61 → 442.56
• Число вызовов: 7 334 → 6 474
• Среднее время (мс): 76.44 → 68.36
• План: не менялся (Index Scan)
• Время ввода-вывода практически отсутствует
• Ожидания незначительны.

Вывод: умеренное улучшение производительности при неизменном плане, скорее всего связанное с колебаниями нагрузки или объёма обрабатываемых данных.

3. Общий итог по разделу «Top SQL by execution time»

1. Суммарная длительность выполнения топ-запросов в интервале 2 значительно снизилась относительно интервала 1. Основной вклад внесли запросы, изменившие план выполнения с последовательного сканирования на индексный/bitmap-доступ.
2. Снижение I/O-нагрузки — главный фактор ускорения. У запросов с изменившимся планом резко уменьшилось чтение с диска; у запросов с прежним планом снижение I/O может объясняться эффектом «прогретого» кэша.
3. Запросы с неизменным планом и незначительным вводом-выводом показали относительно стабильную производительность, что указывает на отсутствие негативных эффектов от выключения planner_upper_limit_estimation.
4. Общая последовательность улучшений: смена плана (Seq Scan → Index/Bitmap Scan) → снижение чтений → снижение I/O-ожиданий → падение общего времени. Для планов без изменений: сохранение кэшированных данных → также падение I/O, но в меньшей степени.

4. Гипотеза о влиянии planner_upper_limit_estimation на результаты отчёта

Параметр planner_upper_limit_estimation = off в интервале 2 позволил планировщику точнее оценивать кардинальность и, как следствие, переключаться на более эффективные планы выполнения для ряда запросов, что привело к существенному ускорению их работы и снижению общей I/O-нагрузки.

(Степень уверенности: Вероятно)

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

1. Сравнение планов. Наиболее яркий пример — запрос 12e2db113ff929b0, который в интервале 1 (параметр on) использовал последовательное сканирование, а в интервале 2 (off) – индексный доступ, что невозможно без изменения оценки кардинальности. Отключение лимитирующей эвристики позволило планировщику принять решение о применении индекса.
2. Корреляция с I/O. Резкое падение объёмов чтения и времени ввода-вывода для запросов с изменившимся планом прямо связано с выбором более селективных методов доступа к данным.
3. Отсутствие ухудшений. Ни один из рассмотренных запросов не деградировал в интервале 2. Это говорит о том, что отключение параметра в условиях текущей рабочей нагрузки и статистики не привело к выбору «опасных» планов.
4. Стабильность планов без I/O-нагрузки. Запросы, чей план не изменился, не испытали негативного влияния, что свидетельствует о том, что параметр не оказал вредного воздействия на уже хорошо оптимизированные запросы.
5. Контекст настройки. Известно, что planner_upper_limit_estimation предназначен для ограничения рисков завышения кардинальности при неполной статистике, но может препятствовать выбору действительно лучшего плана. Отключение в интервале 2 сняло это ограничение и дало положительный эффект.

Для полного подтверждения гипотезы требуется анализ исходной статистики распределения данных и повторяемости эффекта в изолированной среде, однако наблюдаемые изменения планов и метрик хорошо согласуются с ожидаемым поведением планировщика.

2. Аналитический отчет по разделу "I/O wait time (SQL-запросы с наибольшим временем I/O)" отчета pgpro_pwr

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

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

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

pgpro_pwr_diff.69-71-74-76.clear.html - разностный отчет pgpro_pwr по снимкам "69-71" и "74-76"

ТаблицаG.36.txt - описание раздела "Таблица G.36. Top SQL by I/O wait time (SQL-запросы с наибольшим временем I/O)" отчета pgpro_pwr

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

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

"Вероятно"

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

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

УТОЧНЕНИЕ

При подготовке ответа учитывай, что разное значение параметра planner_upper_limit_estimation в отчетах pgpro_pwr :

"69-71" - planner_upper_limit_estimation = on

"74-76" - planner_upper_limit_estimation = off

Задача:

# Для SQL запросов представленных в интервалах 1 и 2 в разделе "Top SQL by I/O wait time"

# Проведи сравнительный анализ SQL запросов по статистике выполнения и ожиданий.

# Проведи сравнительный анализ планов выполнения

# Сформулируй общий итог по сравнению интервалов по разделу "Top SQL by I/O wait time" с указанием последовательности тезисов для каждого вывода

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

Сравнительный анализ SQL-запросов по разделу «Top SQL by I/O wait time» (интервалы 1: «69-71», 2: «74-76»)

1. Контекст и ключевое изменение конфигурации

В интервале 1 параметр planner_upper_limit_estimation = on, в интервале 2 – off. Этот параметр ограничивает верхнюю оценку кардинальности строк для неселективных условий; его отключение даёт планировщику большую свободу при выборе порядка и способа доступа к данным, но может повысить риск ошибок оценки в условиях неточной статистики.

2. Сравнительный анализ запросов по статистике выполнения и ожиданиям I/O

Рассматриваются запросы, лидировавшие по времени I/O в каждом из интервалов, с акцентом на те, что присутствуют в обоих периодах.

Запрос 12e2db113ff929b0 (_InfoRg12488), основной план в интервале 1 c47b19a34bf7ba7d

Интервал 1:

• Время I/O: 960.30 с (все операции чтения)
• Прочитано shared-блоков: 2.37 млн (≈18.1 ГБ)
• Число вызовов: 60 919
• Общее время выполнения: 1002.78 с
• Ожидания: DataFileRead составили 936.07 с

Интервал 2 (тот же план):

• Время I/O: 49.24 с
• Прочитано shared-блоков: 1.14 млн (≈8.7 ГБ)
• Число вызовов: 61 703
• Общее время: 75.48 с
• Ожидания: DataFileRead 48.36 с
• В интервале 2 также появились альтернативные планы для этого же запроса с общим I/O около 119 с.

Вывод: Снижение I/O в десятки раз при практически одинаковом числе вызовов произошло за счёт изменения плана выполнения (см. раздел «Планы»).

Запрос a672f72c2ed94ff1 (_Reference109), план 4ed965dc8142880d

Интервал 1:

• Время I/O: 528.24 с (чтение)
• Прочитано shared-блоков: 970 956 (≈7.4 ГБ)
• Число вызовов: 240
• Общее время: 699.24 с
• Ожидания: DataFileRead 517.40 с

Интервал 2 (тот же план):

• Время I/O: 33.21 с
• Прочитано блоков: 321 531 (≈2.5 ГБ)
• Число вызовов: 240
• Общее время: 171.25 с
• Ожидания: DataFileRead 32.89 с

Вывод: План не изменился, однако объём чтения и время I/O резко сократились, что, наиболее вероятно, связано с переходом части данных в буферный кэш при повторяющихся однотипных вызовах.

Тяжёлые запросы интервала 1, исчезнувшие из топа I/O во втором интервале

• 357ae191acedc0f3 (_InfoRg15516): I/O 913.88 с, 46 вызовов.
• 1a4c9aee07b2a9a7 (_InfoRg14041): I/O 1000.97 с, 46 вызовов.
• 510eb4893c41718e (_Reference15235): I/O 798.82 с, 46 вызовов.
• d620b963a19bcab3 (сложный запрос с _InfoRg12194): I/O 726.57 с, 509 вызовов.
• Все эти запросы полностью отсутствуют во втором интервале (вероятно, не вызывались), что само по себе резко освободило подсистему ввода-вывода.

Новые лидеры по I/O в интервале 2

В топе второго интервала появились несколько однотипных запросов, обращающихся к _InfoRg12756 с временными таблицами (pg_temp.tt...), каждый с временем I/O около 130-140 с (например, e1017575acb5d25c — 143.89 с, 41ac4e36ae5a6c78 — 143.71 с, 3dcd08bab145acb3 — 130.78 с). В интервале 1 они не фигурировали в топе по I/O, поскольку их доля была незначительна на фоне гигантских чтений тяжёлых запросов. Со снижением общей I/O-нагрузки эти запросы «вышли на поверхность» как новые основные потребители ввода-вывода.

3. Сравнительный анализ планов выполнения

Запрос 12e2db113ff929b0

Интервал 1 (план c47b19a34bf7ba7d): Limit -> Sort -> Seq Scan on _InfoRg12488. Полное последовательное сканирование таблицы.

Интервал 2: доминирующим стал план 36da31b89a371dcf с Bitmap Heap Scan по тому же отношению, что позволило существенно сократить число операций чтения и время I/O. Также использовались Index Scan-стратегии.

Переход от последовательного сканирования к индексному/bitmap-доступу является основной причиной падения времени ввода-вывода для этого запроса.

Запрос a672f72c2ed94ff1

План не изменился (Index Scan по _Reference109_27 с агрегацией). Сокращение I/O достигнуто без модификации плана — целиком за счёт буферизации.

Запросы-новички топа интервала 2

• Все они используют вложенные циклы (Nested Loop) с индексным доступом к _InfoRg12756 и последовательным сканированием временной таблицы. Такие планы генерируют заметный ввод-вывод за счёт чтения временных данных, что в отсутствие тяжеловесных запросов вывело их в лидеры.

4. Общий итог по сравнению интервалов (последовательность тезисов)

1. Суммарное время ввода-вывода для топ-запросов во втором интервале многократно ниже, чем в первом. Максимальное I/O-время одного запроса снизилось с ~1000 с до ~140 с.
2. Снижение достигнуто главным образом за счёт смены планов у запроса-лидера первого интервала (12e2db113ff929b0) — переход от Seq Scan к Bitmap/Index Scan резко уменьшил объём дисковых операций.
3. Дополнительный вклад внесло полное исчезновение нескольких сверхтяжёлых по вводу-выводу запросов, которые в интервале 2 не выполнялись.
4. Запрос с неизменным планом (a672f72c2ed94ff1) продемонстрировал радикальное падение I/O за счёт кэширования данных в разделяемой памяти.
5. В результате ослабления доминирования прежних «монстров» по I/O на передний план вышли запросы к временным таблицам, которые в первом интервале были незаметны.

5. Гипотеза о влиянии planner_upper_limit_estimation

Отключение planner_upper_limit_estimation во втором интервале позволило планировщику пересмотреть оценки кардинальности и выбрать более эффективные индексные/bitmap-доступы для ключевых запросов, что привело к драматическому снижению I/O-нагрузки и высвободило дисковую подсистему для других операций. (Степень уверенности: Вероятно)

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

1. Смена планов коррелирует с отключением параметра. Главный пример – запрос 12e2db113ff929b0, где при on использовался Seq Scan, а при off — Bitmap/Index Scan. Лимитирующая эвристика параметра мешала планировщику отдать предпочтение индексному доступу, искусственно завышая привлекательность последовательного чтения.
2. Прямая связь плана и I/O. Переход на индексный доступ напрямую уменьшил количество считываемых блоков и время I/O, что подтверждается десятикратным падением этих метрик.
3. Устойчивость неизменных планов. Запрос a672f72c2ed94ff1 сохранил план и снизил I/O за счёт кэширования, что говорит о том, что параметр не оказал негативного влияния на планы, где индексный доступ уже использовался.
4. Отсутствие регрессий. Ни один запрос не ухудшил свои показатели во втором интервале, что свидетельствует о том, что снятие ограничения не привело к нестабильности или выбору заведомо плохих планов при текущей статистике.
5. Снятие «потолка» оценки. При включённом planner_upper_limit_estimation планировщик мог считать последовательное сканирование чрезмерно дешёвым из-за ограничения сверху оценки числа строк. Отключение позволило получить более реалистичные оценки стоимости и принять оптимальное решение.

Таким образом, изменение параметра planner_upper_limit_estimation является наиболее вероятной причиной наблюдаемой положительной динамики в разделе «Top SQL by I/O wait time».

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

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

При отключении planner_upper_limit_estimation (интервал 74–76) наблюдалось многократное снижение общего времени выполнения и времени I/O для запросов, ранее использовавших последовательное сканирование:

наиболее показательный пример — запрос 12e2db113ff929b0 (_InfoRg12488), у которого общее время сократилось на 92 % (с 1002,78 с до 75,48 с), а время ожидания ввода-вывода (DataFileRead) — на 95 % (с 936,07 с до 48,36 с) за счёт смены плана с Seq Scan на Bitmap Heap Scan или Index Scan.

Запросы с неизменными планами (например, a672f72c2ed94ff1) также продемонстрировали снижение I/O, обусловленное эффектом кэширования, а не изменением плана.

При этом ни один из анализируемых запросов не показал ухудшения производительности, а ранее доминировавшие по I/O тяжеловесные запросы в интервале 74–76 отсутствовали вовсе, что позволило выявить новый «лидер» — группу запросов к временным таблицам (pg_temp).

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

Послесловие

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

В контексте миграции PostgreSQL 15→17 и интеграции с 1С учёт данного параметра может рассматриваться как потенциальная точка оптимизации, требующая предварительного тестирования на модельной нагрузке.