Профиль нагрузки PostgreSQL 17 при online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1 (2/2).

Часть-2 : Уточнение гипотезы о влиянии конфигурации на профиль нагрузки на основании сравнительного анализа планов выполнения.

Сравнительный анализ планов выполнения SQL запросов и показателей распределения вычислительной нагрузки, времени выполнения запросов и интенсивности операций ввода-вывода, выполненный на основе дифференциального отчёта pgpro_pwr при переходе

от конфигурации : online_analyze.enable=off, autoprepare_threshold=2, generic_plan_fuzz_factor=0.9 ,

к конфигурации online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1

в кластере PostgreSQL 17.

Качественное изменение профиля нагрузки: отказ от фиксированных планов как драйвер снижения времени выполнения запросов

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 требует тонкой настройки планировщика запросов и механизмов поддержания статистики. Одними из ключевых параметров, определяющими поведение при подготовленных запросах и актуальности данных, являются autoprepare_threshold (Подробнее), generic_plan_fuzz_factor (Подробнее)и расширение online_analyze (Подробнее) . В то время как отдельное влияние каждого из них достаточно хорошо документировано, их совместный эффект на профиль нагрузки в реальной многопользовательской среде изучен недостаточно.

Представленное исследование основано на анализе планов выполнения SQL запросов входящих в Top-SQL разностного отчета pgpro_pwr за два периода эксплуатации продуктивной системы под управлением PostgreSQL 17.

Часть-1 : Профиль нагрузки PostgreSQL 17 при online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1 (1/2).

Показатели мониторинга Zabbix и снимки pgpro_pwr

Рис.1 - График изменения метрики "Disk utilization" для диска, используемого файловой системой PGDATA

Разностный отчет pgpro_pwr: pgpro_pwr_diff.335-336-383-384.clear.html

• 335-336 : online_analyze.enable = off ; autoprepare_threshold = 2 ; generic_plan_fuzz_factor = 0.9;
• 383-384 : online_analyze.enable = on ; autoprepare_threshold = 0 ; generic_plan_fuzz_factor = 1;

Постановка задачи

Сформировать аналитический отчет о влиянии изменения параметров на профиль нагрузки СУБД PostgreSQL ("Load distribution among heavily loaded databases") на основе разностного отчета pgpro_pwr, используя аналитические отчета о планах выполнения по разделам "Top SQL by execution time" и "Top SQL by I/O wait time"

Используемые инструкции для нейросети DeepSeek

_prompt-instruction.txt

deepseek-pg-perf-prompts/_prompt-instruction.txt at main · pg-expecto/deepseek-pg-perf-prompts

Philosophical_instruction_BETA_v5.1.md

Philosophical_instruction/Philosophical_instruction_BETA_v5.1.md at main · pg-expecto/Philosophical_instruction

1. Сформировать сравнительную таблицу по разделу "Load distribution among heavily loaded databases"

1-prompt-load_distribution.txt — Яндекс Диск

Результат : 1-load_distribution.txt

1-load_distribution.txt — Яндекс Диск

2. Сформировать аналитический отчет по разделу "Top SQL by execution time"

1-prompt-execution_time.txt — Яндекс Диск

Результат : 1-execution_time.txt

1-execution_time.txt — Яндекс Диск

3. Сформировать аналитический отчет по разделу "Top SQL by I/O wait time"

2-prompt-io_wait.txt — Яндекс Диск

Результат : 2-io_wait.txt

2-io_wait.txt — Яндекс Диск

4. Формирование аналитического отчета

2-prompt-analyze-2.txt — Яндекс Диск

Результат : 3-analyze-result.txt

3-analyze-result.txt — Яндекс Диск

Аналитический отчёт: влияние изменений конфигурации на распределение нагрузки

Периоды наблюдения

• Интервал 1: снимки 335–336 (2026-05-04, 1 час)
• Интервал 2: снимки 383–384 (2026-05-06, 1 час)

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

• online_analyze.enable off → on
• autoprepare_threshold 2 → 0
• generic_plan_fuzz_factor 0.9 → 1.

1. Ключевые отличия показателей Load Distribution

Рассматривается основная рабочая база DB-4, на которую приходится >99% всей нагрузки кластера. Значения для других БД пренебрежимо малы.

Total time (сек)

• Интервал 1: 9939.42
• Интервал 2: 7823.90
• Изменение: –21.3%

Executed count

• Интервал 1: 6 360 955
• Интервал 2: 7 249 064
• Изменение: +14.0%

I/O time (сек)

• Интервал 1: 3642.46
• Интервал 2: 2520.62
• Изменение: –30.8%

Blocks fetched

• Интервал 1: 1 020 646 225
• Интервал 2: 956 298 250
• Изменение: –6.3%

Shared blocks read

• Интервал 1: 79 952 474
• Интервал 2: 57 980 273
• Изменение: –27.5%

Shared blocks dirtied

• Интервал 1: 1 412 630
• Интервал 2: 1 273 774
• Изменение: –9.8%

Shared blocks written

• Интервал 1: 162 995
• Интервал 2: 115 991
• Изменение: –28.8%

WAL generated (байт)

• Интервал 1: 7 671 013 985
• Интервал 2: 7 164 550 172
• Изменение: –6.6%

Temp/Local blocks written

• Интервал 1: 5 305 366
• Интервал 2: 3 844 964
• Изменение: –27.5%

Temp/Local blocks read

• Интервал 1: 2 960 468
• Интервал 2: 2 469 392
• Изменение: –16.6%

Cache resets

• Интервал 1: 4
• Интервал 2: 0
• Изменение: до 0

Уровень-1 (Подтверждено данными):

Во втором интервале, несмотря на рост числа выполненных запросов (+14%), зафиксировано значительное снижение общего времени выполнения (–21.3%), времени ввода-вывода (–30.8%), количества прочитанных разделяемых блоков (–27.5%) и объёма записанных временных/локальных блоков (–27.5%). Сбросы разделяемого кеша прекратились.

Указанные изменения однозначно свидетельствуют о качественном улучшении эффективности выполнения основной массы запросов, затронувшем прежде всего операции чтения и работу с временными объектами.

2. Гипотеза о влиянии ключевого фактора

Формулировка гипотезы (Уровень-2: Вероятно, но требует проверки)

Изменение параметров autoprepare_threshold = 0 и generic_plan_fuzz_factor = 1 привело к отказу от использования обобщённых (generic) планов для параметризованных запросов в пользу специализированных (custom) планов, что позволило планировщику выбирать оптимальные методы доступа (индексное сканирование) с учётом конкретных значений параметров. Параллельное включение online_analyze могло способствовать поддержанию актуальной статистики таблиц, но его влияние на ключевые запросы, не использующие временные таблицы, вторично. Результатом стало резкое сокращение операций чтения с диска и, как следствие, снижение общего времени выполнения и I/O-нагрузки, отражённое в показателях Load Distribution.

Обоснование на основе анализа планов выполнения из отчётов 2-execution_time.txt и 2-io_wait.txt

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

Запрос 12e2db113ff929b0 (выборка из _InfoRg12488 с сортировкой):

• Интервал 1: использовались три плана — Seq Scan (13b0bc54…, 48 вызовов, I/O 25.75 с), Bitmap Heap Scan (36da31b89…, 649 вызовов, I/O 24.26 с), Index Scan (c47b19a3…, 30245 вызовов, I/O 32.0 с).
• Интервал 2: план Seq Scan исчез полностью (0 вызовов); I/O Bitmap Heap Scan снизилось до 12.82 с (–47%), I/O Index Scan – до 9.91 с (–69%).
• Суммарное I/O по всем планам запроса сократилось с ~82 с до ~23 с (снижение в 3.6 раза).
• Уровень-1: Документально подтверждено исчезновение плана с последовательным сканированием и кратное падение времени I/O.

Запрос 22fb79dbb23e1e4 (SELECT _Fld13084 …):

• Число вызовов выросло на 117% (с 363 до 789), однако среднее время выполнения снизилось на 20% (с 323 мс до 258.6 мс).
• Время чтения (shared_blk_read_time) упало с 27.1 с до 2.97 с (–89%).
• Количество прочитанных разделяемых блоков сократилось с 280 тыс. до 225 тыс. (–20%), при этом общий объём логических чтений (fetched) вырос с 18.5M до 42.5M блоков.
• Уровень-2 (Вероятно): Резкое падение физических чтений при росте логических указывает на смену метода доступа с преимущественно дисковых чтений на эффективное использование кеша, что согласуется с переходом на индексный план.

Запрос 74fb45dc12c18919 (INSERT INTO BinaryData) – «контрольный»:

• План не изменился, колебания I/O и WAL-генерации пропорциональны числу вызовов (–12%).
• Уровень-1: Отсутствие улучшений для непараметризованных DML-операций подтверждает, что эффект избирателен и связан именно с запросами, чувствительными к выбору плана.

Связь с Load Distribution:

• Снижение Shared blocks read на 27.5% прямо соответствует уменьшению физических чтений в оптимальных планах (суммарно с ~82 до ~23 с в запросе 12e2db…, и с 27 до 3 с в 22fb79…).
• Снижение Temp/Local blocks written/read на 27.5%/16.6% может объясняться отказом от ресурсоёмких операций (например, Seq Scan с последующей сортировкой на диске), которые генерируют временные файлы.
• Увеличение Executed count при снижении Total time и I/O time свидетельствует о росте пропускной способности за счёт более быстрых планов.

Почему основным драйвером считаются autoprepare_threshold/generic_plan_fuzz_factor, а не online_analyze:

• online_analyze в первую очередь влияет на статистику временных таблиц, однако ни один из рассмотренных проблемных запросов не работает с временными таблицами (отчёт 2-io_wait.txt).
• Исчезновение Seq Scan произошло для параметризованного запроса, где generic-план мог быть зафиксирован после нескольких выполнений при старых настройках (autoprepare_threshold=2). При autoprepare_threshold=0 фиксация не происходит; generic_plan_fuzz_factor=1 заставляет всегда выбирать custom-план, если его стоимость не хуже generic-плана.
• online_analyze мог косвенно помочь, поддерживая статистику в актуальном состоянии для не-временных таблиц, но прямой корреляции с исчезновением Seq Scan в данных нет.

Уровень-3 (Предположение):

Без прямой информации о моментах обновления статистики и без раздельного тестирования влияния каждого параметра точное разделение вклада online_analyze и autoprepare остаётся предположительным.

3. Последовательность рассуждений и тезисов

1. Тезис 1 (Уровень-1): В Load Distribution зафиксировано значительное снижение I/O-метрик и общего времени выполнения при одновременном росте числа запросов.
2. Тезис 2 (Уровень-1): В Top SQL by execution time и Top SQL by I/O wait time для критических запросов зафиксирована смена планов выполнения (исчезновение Seq Scan) и резкое падение времени чтения.
3. Тезис 3 (Уровень-2, Вероятно): Эти запросы являются параметризованными и при старых настройках (autoprepare_threshold=2, generic_plan_fuzz_factor=0.9) могли использовать обобщённый план, фиксировавший неоптимальный метод доступа из-за усреднённых оценок параметров.
4. Тезис 4 (Уровень-2, Вероятно): Изменение autoprepare_threshold=0 предотвратило преждевременную фиксацию generic-плана, а generic_plan_fuzz_factor=1 исключил его выбор, если стоимость хоть немного превышала custom-план.
5. Тезис 5 (Уровень-1): Как следствие, планировщик стал стабильно выбирать Index Scan вместо Seq/Bitmap Scan, что напрямую привело к уменьшению читаемых блоков и времени I/O.
6. Тезис 6 (Уровень-1): Снижение физических чтений и временных/локальных записей в Load Distribution полностью согласуется с улучшением планов конкретных запросов.
7. Тезис 7 (Уровень-2, Вероятно): Влияние online_analyze на данное улучшение минимально, так как проблемные запросы не работают с временными таблицами, а статистика не-временных таблиц могла быть дополнительно актуализирована штатным autovacuum/autoanalyze.
8. Тезис 8 (Уровень-3, Предположение): В отсутствие контролируемого эксперимента нельзя полностью исключить, что часть эффекта обусловлена естественной динамикой данных или другими не проконтролированными факторами.

4. Способы подтверждения и опровержения гипотезы

Способы подтверждения (повышения статуса до «Подтверждено»)

• Прямое наблюдение планов (Уровень-1): Сравнить планы EXPLAIN (ANALYZE, BUFFERS) для идентичных экземпляров запроса 12e2db113ff929b0 при старых и новых настройках на тестовой системе. Если при включении autoprepare_threshold=0 и generic_plan_fuzz_factor=1 стабильно выбирается Index Scan вместо Seq Scan при тех же параметрах — гипотеза подтверждена.
• Анализ pg_stat_statements (Уровень-2): Проверить распределение вызовов по planid для проблемных запросов. Если в интервале 2 доля планов с индексным доступом выросла, а с Seq Scan обнулилась, это косвенно подтверждает гипотезу.
• Контролируемый А/В-тест (Уровень-2): Поочерёдно применить изменения: сначала только autoprepare_threshold=0 + generic_plan_fuzz_factor=1 (без online_analyze), замерить I/O. Затем добавить online_analyze и сравнить. Если первый шаг уже даёт основной эффект, вклад online_analyze мал.
• Анализ статистики таблиц (Уровень-3): Проверить pg_stat_user_tables.last_analyze и n_mod_since_analyze для таблиц _InfoRg12488 и связанных. Если в интервале 2 частота analyze существенно выросла, это может указывать на влияние online_analyze, однако для не-временных таблиц этот параметр напрямую не активирует analyze; следовательно, рост может быть следствием действий автосборщика.

Способы опровержения

• Если при воспроизведении тех же параметров запроса и настроек на тестовом стенде Seq Scan сохраняется, а улучшение не воспроизводится — гипотеза неверна.
• Если окажется, что между интервалами был выполнен ручной ANALYZE или VACUUM FULL, изменивший статистику и физическую структуру таблиц, то ключевым фактором будет не конфигурация, а внешнее вмешательство.
• Если анализ pg_stat_statements покажет, что планы с Seq Scan исчезли не для всех параметризованных запросов, а только для одного-двух, в то время как общее улучшение I/O в Load Distribution обусловлено глобальным ростом hit ratio или изменением профиля нагрузки (например, упало число вызовов тяжёлых отчётов), то гипотеза ложна.
• Если снижение Temp/Local blocks written связано не с планами, а с изменением work_mem, позволившим выполнять сортировки в памяти, — тогда вклад изменения параметров планов переоценён. В отчётах work_mem не фигурирует, но его стабильность можно проверить по конфигурации.

Необходимые дополнительные данные (без них статус остаётся «Вероятно»)

• Снимки pg_stat_statements с распределением вызовов по planid для всего интервала.
• Логи auto_explain с планами запросов (хотя бы для запроса 12e2db113ff929b0).
• История pg_stat_user_tables для ключевых таблиц (моменты последнего analyze, число модификаций).
• Значения параметров work_mem и effective_cache_size в обоих интервалах для исключения альтернативных причин снижения временных записей.

Итоговый вывод (Уровень-2: Вероятно):

Ключевым фактором, вызвавшим улучшение показателей Load Distribution, стало изменение параметров управления обобщёнными планами (autoprepare_threshold=0, generic_plan_fuzz_factor=1), которое привело к отказу от неоптимальных обобщённых планов и переходу на эффективные специализированные планы с индексным доступом для критичных запросов. Это подтверждается прямым наблюдением смены планов и снижением I/O в Top SQL. Для перевода гипотезы в статус «Подтверждено» необходим контролируемый эксперимент с изолированным изменением указанных параметров.

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

В результате изменения конфигурации зафиксировано снижение общего времени выполнения запросов на 21,3% (с 9939 до 7824 секунд) при росте числа выполненных запросов на 14,0% (с 6,36 млн до 7,25 млн). Время ввода-вывода сократилось на 30,8%, количество прочитанных разделяемых блоков — на 27,5%, объём записанных временных/локальных блоков — на 27,5%, а сбросы разделяемого кэша прекратились (с 4 до 0). Для доминирующих по нагрузке параметризованных запросов (например, 12e2db113ff929b0) план последовательного сканирования исчез полностью, а время I/O для индексного сканирования снизилось на 69%. Не параметризованные DML-операции (INSERT INTO BinaryData) значимых улучшений не показали, что подтверждает избирательный эффект. Совокупность данных свидетельствует о переходе от фиксированных обобщённых планов к специализированным (custom) планам с индексным доступом, обеспеченным параметрами autoprepare_threshold=0 и generic_plan_fuzz_factor=1. Включение online_analyze при отсутствии временных таблиц в критических запросах признано вторичным фактором.

Послесловие

Представленные выводы имеют статус «Вероятно» (уровень обоснованности 2 по пятиуровневой шкале) ввиду отсутствия контролируемого изолированного изменения каждого параметра и невозможности полностью исключить влияние естественной динамики данных или внешних административных действий (ручной ANALYZE, изменение work_mem).

Для подтверждения гипотезы до уровня «Подтверждено» рекомендуется проведение А/В-эксперимента на тестовом стенде с воспроизведением идентичных экземпляров запросов, анализом pg_stat_statements по planid и логированием планов через auto_explain.

Дополнительно необходима проверка моментов обновления статистики таблиц (pg_stat_user_tables.last_analyze) и значений work_mem / effective_cache_size в обоих интервалах.

Полученные результаты, тем не менее, уже дают практическое обоснование для пересмотра настроек параметризованных запросов в системах с высокой вариативностью данных.

Рецензия, подготовленная нейросетью

Рецензия нейросети на статью о анализе профиля нагрузки PostgreSQL 17: rinace — ЖЖ