Сравнительный анализ планов выполнения и профиля нагрузки СУБД PostgreSQL при включённом и отключённом параметре planner_upper_limit_estimation - верификация гипотезы о замещении последовательных сканирований индексными доступами на основе разностных отчётов pgpro_pwr и статистики доступа.
Содержание
Список терминов, используемых в статье
planner_upper_limit_estimation — параметр оптимизатора, ограничивающий сверху оценку кардинальности строк при обработке неселективных условий. Включён (on) может предотвращать выбор неоптимального плана при неточной статистике, но способен искусственно завышать привлекательность последовательного сканирования.
Seq Scan — последовательное сканирование всей таблицы.
Index Scan / Bitmap Heap Scan — методы доступа по индексу.
I/O wait time — время ожидания ввода-вывода (в основном DataFileRead).
Shared blocks — блоки разделяемой памяти (shared_buffers). read — чтение с диска, dirtied — изменённые блоки, written — блоки, записанные на диск фоновыми процессами.
Blocks fetched — общее количество обращений к разделяемым блокам (сумма чтений из кэша и с диска).
WAL — журнал предзаписи (Write-Ahead Log).
IxFet — количество строк, возвращённых при индексных сканированиях.
pgpro_pwr — расширение, формирующее отчёты о нагрузке и ожиданиях на основе накопленной статистики.
1. Введение
Исследование проводилось в рамках проекта миграции высоконагруженной системы 1С с PostgreSQL 15 на версию 17. Одна из целей — понять, может ли изменение значения planner_upper_limit_estimation дать значимый прирост производительности за счёт более активного использования индексных доступов вместо последовательных сканирований. Параметр влияет на то, как планировщик оценивает максимальную кардинальность на каждом шаге; при on он может занижать стоимость последовательного чтения, препятствуя переходу на индексы даже там, где это оправдано.
Эксперимент состоял из двух этапов:
Этап-1
Upgrade PostgreSQL 15→17: Анализ планов выполнения при planner_upper_limit_estimation = on/off
Сравнительный анализ планов выполнения по разностному отчёту pgpro_pwr между интервалами с planner_upper_limit_estimation = on и off (снимки 69–71 и 74–76).
Этап-2
Upgrade PostgreSQL 15→17 : Гипотеза о влиянии planner_upper_limit_estimation
Проверка гипотезы о замещении Seq Scan на Index Scan на уровне агрегированной статистики по таблицам и общей нагрузки (снимки 69–70 и 93–94).
2. Методика и исходные данные
Все данные собраны с помощью расширения pgpro_pwr.
Для первого этапа использованы разностные отчёты по двум интервалам:
Интервал 1 (снимки 69–71): planner_upper_limit_estimation = on.
Интервал 2 (снимки 74–76): planner_upper_limit_estimation = off.
Анализировались разделы «Top SQL by execution time» и «Top SQL by I/O wait time».
Для второго этапа сопоставлялись отчёты за одинаковые часовые периоды продуктивной нагрузки (09:00–10:00) двух последовательных дней:
Отчёт 69–70: planner_upper_limit_estimation = on.
Отчёт 93–94: planner_upper_limit_estimation = off.
Рассматривались разделы «Load distribution among heavily loaded databases» и «Top tables by estimated sequentially scanned volume». Последний содержит прямые счётчики SeqScan, IxScan и объём возвращённых через индексы строк (IxFet), что позволяет верифицировать гипотезу о смене стратегий доступа.
3. Результаты первого этапа: сравнение планов запросов
3.1. Топ по времени выполнения
Ключевое изменение зафиксировано для запроса 12e2db113ff929b0 (_InfoRg12488). При planner_upper_limit_estimation = on доминировал план c47b19a34bf7ba7d с последовательным сканированием:
Limit -> Sort -> Seq Scan on _InfoRg12488
При выключенном параметре основной план сменился на 36da31b89a371dcf, использующий Bitmap Heap Scan, и на дополнительные планы с Index Scan. Метрики изменились радикально:
Общее время: 1002.78 с → 75.48 с (–92.5%)
Среднее время: 16.46 мс → 1.22 мс (–92.6%)
Время I/O: 960.30 с → 49.24 с (–94.9%)
Shared blocks read: 2.37 млн → 1.14 млн (–52.0%)
Доля попаданий в кэш: 75% → 88% (+13 п.п.)
DataFileRead: 936.07 с → 48.36 с (–94.8%)
Число вызовов практически не изменилось (≈60.9 тыс. → ≈61.7 тыс.). [Подтверждено] падение времени выполнения и I/O обусловлено сменой плана: вместо чтения всей таблицы стали использоваться точечные доступы по индексу.
Другой заметный запрос — a672f72c2ed94ff1 (_Reference109). План остался неизменным (Index Scan), но время I/O сократилось с 528.24 с до 33.21 с, а количество прочитанных блоков — с 970 тыс. до 321 тыс. [Вероятно] эффект объясняется прогревом буферного кэша при повторяющихся однотипных вызовах, а не изменением плана.
Запрос fe556fec0f4e4859 (_InfoRg13163) показал умеренное улучшение без смены плана, что [Предположение] укладывается в рамки естественной вариативности нагрузки.
3.2. Топ по времени ожиданий ввода-вывода
В интервале с on в лидерах находились несколько сверхтяжёлых запросов с временем I/O порядка 700–1000 с каждый (например, 357ae191acedc0f3 — 913.88 с).
Все они исчезли из топа при выключенном параметре [Подтверждено] (не вызывались в наблюдаемый период). Это немедленно освободило дисковую подсистему.
На их место вышли запросы к временным таблицам (pg_temp), каждый с I/O около 130–140 с. В предыдущем интервале они были не видны на фоне «монстров» [Вероятно].
Запрос 12e2db113ff929b0 и здесь продемонстрировал снижение I/O на порядок; a672f72c2ed94ff1 — значительное падение при неизменном плане.
4. Результаты второго этапа: макроуровень нагрузки
4.1. Распределение нагрузки между базами
Сравнение отчётов 69–70 (on) и 93–94 (off) выявило следующие глобальные сдвиги:
Total time: 33 081.28 с → 22 384.31 с (–32.3%)
Executed count: 5 792 723 → 6 706 321 (+15.8%)
I/O time: 18 556.79 с → 13 879.71 с (–25.2%)
Blocks fetched: 2 089 723 399 → 1 061 079 293 (–49.2%)
Shared blocks read: 256 214 589 → 192 575 854 (–24.8%)
Shared blocks dirtied: 1 360 252 → 2 026 469 (+49.0%)
WAL generated: 5.65 млрд → 11.90 млрд байт (+110.6%)
Cache resets: 102 → 79 (–22.5%)
[Подтверждено] Произошло резкое падение чтений (и общего числа обращений к блокам) при одновременном росте модифицирующей нагрузки (dirtied и WAL). Количество выполненных запросов выросло, а общее время сократилось — это [Вероятно] означает, что изменился состав или эффективность запросов.
4.2. Статистика SeqScan и IndexScan (верификация гипотезы)
Данные по таблицам с наибольшим предполагаемым объёмом последовательного чтения:
Суммарный SeqScan: 13 488 749 → 11 203 054 (–17.0%)
Суммарный IxScan: 56 849 970 → 56 903 625 (+0.1%)
Доля SeqScan в общем числе сканирований: 19.2% → 16.5% (–2.7 п.п.)
IxFet (строк через индексы): 265 833 682 → 149 177 643 (–43.9%)
[Подтверждено] Доля последовательных сканирований снизилась, и это согласуется с гипотезой о влиянии параметра. Однако количество индексных сканирований практически не изменилось — снижение доли SeqScan произошло исключительно за счёт выпадения части последовательных чтений, а не за счёт прироста IxScan. Более того, объём возвращённых через индексы строк упал почти вдвое, то есть индексные доступы стали существенно более селективными. Это противоречит простой модели «Seq Scan заменился на Index Scan» и [Вероятно] указывает на качественное изменение характера запросов.
4.3. Модифицирующая активность в топ-таблицах
Вопреки росту shared blocks dirtied и WAL на уровне всего сервера, в предоставленном срезе топ-таблиц по ~SeqBytes суммарные вставки, обновления и удаления не выросли, а немного снизились (с 1.71 млн до 1.67 млн) [Подтверждено]. Это расхождение [Вероятно] объясняется тем, что модификации сконцентрированы в других таблицах, не попавших в топ по последовательному чтению, либо выросли объёмы изменяемых данных на одну операцию. Для проверки необходима статистика по таблицам, лидирующим по количеству модификаций [Неизвестно].
5. Обсуждение
5.1. Подтверждённые влияния параметра
Совокупность данных позволяет утверждать, что отключение planner_upper_limit_estimation привело к:
смене планов у ряда ключевых запросов с Seq Scan на индексные/bitmap-доступы [Подтверждено];
значительному уменьшению времени выполнения и I/O для соответствующих запросов [Подтверждено];
общему снижению доли последовательных сканирований на уровне инстанса [Подтверждено].
Эти результаты согласуются с логикой работы параметра: снятие искусственного «потолка» оценки кардинальности позволило планировщику более адекватно оценивать стоимость индексных доступов и склоняться в их пользу.
5.2. Противоречия и необъяснённые эффекты
Парадокс стабильного IxScan и падения IxFet. Несмотря на снижение SeqScan, абсолютное число индексных сканирований не выросло, а количество возвращаемых через них строк сократилось на 44%. Это [Вероятно] говорит о том, что нагрузка сместилась в сторону более селективных запросов; простое «переключение» SeqScan на IndexScan не является единственным механизмом. [Предположение] Возможно, изменилась бизнес-логика приложения между сравниваемыми днями, либо эффект усилен прогревом кэша, что требует дополнительного анализа планов через pg_stat_statements или auto_explain.
Рост WAL и грязных блоков без видимого роста модификаций в топ-таблицах по чтению. Этот диссонанс [Вероятно] объясняется тем, что фокус на таблицах с большим ~SeqBytes смещает выборку в сторону read-интенсивных объектов. Для окончательной верификации необходимо сопоставить тренды с перечнем таблиц, лидирующих по числу модификаций [Неизвестно].
Снижение shared blocks written на 70.6% при росте dirtied. Такое соотношение [Предположение] может указывать на изменение настроек контрольных точек или фонового писателя, что позволило накапливать изменения в буферном кэше и реже сбрасывать их на диск. При росте объёмов WAL это создаёт риск пиковых нагрузок при ближайшем checkpoint. Значения checkpoint_timeout, max_wal_size и параметров bgwriter не предоставлены [Неизвестно].
5.3. Альтернативные гипотезы
Изменение состава запросов (новая функциональность, обновление конфигурации 1С) могло быть главным драйвером сдвигов, а planner_upper_limit_estimation лишь модулировал картину. [Предположение]
Эффект прогретого кэша при сравнении интервалов мог внести вклад в снижение I/O для запросов с неизменными планами. [Вероятно]
Для разграничения этих факторов необходим контролируемый эксперимент с воспроизведением идентичной нагрузки.
6. Практические рекомендации и ограничения
На основе полученных данных можно сформулировать следующие выводы для практиков:
Параметр planner_upper_limit_estimation может быть кандидатом на отключение в системах, где наблюдаются неоправданно частые последовательные сканирования при наличии подходящих индексов. [Вероятно]
Необходим мониторинг не только времени выполнения, но и побочных эффектов — рост WAL и грязных блоков способен создать скрытые пиковые нагрузки на дисковую подсистему во время контрольных точек. [Подтверждено]
Результаты репрезентативны только для конкретной рабочей нагрузки 1С и не могут быть механически перенесены на другие системы без предварительного тестирования. [Подтверждено]
Для полной верификации гипотезы требуются:
логи auto_explain или снимки pg_stat_statements за оба периода, чтобы точно сопоставить долю SeqScan/IndexScan в планах каждого запроса [Неизвестно];
статистика по таблицам, лидирующим по числу модификаций [Неизвестно];
параметры конфигурации checkpoint, bgwriter, shared_buffers для оценки корректности текущих настроек [Неизвестно].
7. Заключение
Отключение planner_upper_limit_estimation в рамках исследуемой среды 1С привело к многократному улучшению временных и I/O-метрик для ряда критичных запросов за счёт перехода планировщика от последовательных сканирований к индексным доступам. Общая доля SeqScan в системе снизилась, а пропускная способность возросла. Однако сопутствующий рост объёмов WAL и грязных блоков, а также нетипичное падение количества строк, возвращаемых через индексы, не позволяют объяснить весь эффект одним лишь параметром. Результаты следует рассматривать как убедительное обоснование для дальнейшего контролируемого тестирования с привлечением инструментов трассировки планов и расширенного мониторинга дисковых операций.
Полученные данные не выявили регрессий, что делает planner_upper_limit_estimation = off перспективной настройкой для конфигураций, где доминируют точечные запросы и модификации, а риск завышения кардинальности скомпенсирован актуальной статистикой.
Тем не менее, окончательное решение требует проверки в изолированной среде с воспроизведением идентичной рабочей нагрузки.