The post has been translated automatically. Original language: Russian
Methodology for analyzing pgpro_pwr reports using PG_EXPECTO and DeepSeek: PostgreSQL 17 execution plans when using online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1.
Empirical reconstruction of the cause-and-effect mechanism: why the rejection of premature fixation of general plans turned out to be a decisive factor in reducing disk load, and updating statistics in real time is only a concomitant condition.
Part-1: PostgreSQL 17 load profile at online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1(1/2)
Part-2: PostgreSQL 17 load profile at online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1 (2/2)
Annotation.
A comparative analysis of the two operational periods of the PostgreSQL 17 cluster with radically different scheduler and autoanalysis settings revealed a complex picture. Changing autoprepare_threshold parameters (More details) , generic_plan_fuzz_factor (More details) and online_analyze (More details) This resulted in a 21% reduction in total query execution time, a 31% decrease in I/O time, and a complete disappearance of shared cache flushes, while increasing the number of operations performed by 14%.
A detailed comparison of the implementation plans made it possible to clarify the mechanism: The key driver of the improvement was the rejection of premature fixation of general plans, rather than updating statistics on its own. The article summarizes the course of the research and formulates a final, empirically based hypothesis, equipped with confidence levels.
1. Introduction
Fine—tuning the query scheduler is an eternal topic for PostgreSQL administrators. The online_analyze extension, the threshold for automatic preparation of general autoprepare_threshold plans, and the generic_plan_fuzz_factor fuzziness factor are individually well documented, but their combined effect in a live multiuser environment has so far remained poorly understood.
The present study fills this gap by relying on the pgpro_pwr differential report, taken in two hourly intervals on a productive PostgreSQL 17 cluster.
Fig.1 - Graph of changes in the "Disk utilization" metric for a disk used by the PGDATA file system
The first interval (images 335-336) corresponded to the configuration:
- online_analyze.enable = off,
- autoprepare_threshold = 2,
- generic_plan_fuzz_factor = 0.9.
In the second interval (images 383-384), the following changes were applied:
- online_analyze.enable = on,
- autoprepare_threshold = 0,
- generic_plan_fuzz_factor = 1.
No other server settings have been changed.
2. What the macro statistics showed
The main database (codenamed DB‑4) accumulated more than 99% of the cluster's total load, so its metrics became the central object of analysis. The transition to the new settings was accompanied by statistically significant and unidirectional shifts:
- The total execution time decreased by 21.3% (from 9939 to 7824 seconds), while the number of completed requests increased by 14.0% (from 6.36 to 7.25 million).
- The I/O time has dropped even more noticeably, by 30.8%.
- Reading shared blocks from disk (Shared blocks read) decreased by 27.5%, while writing temporary/local blocks decreased by 27.5%.
- The shared cache resets recorded four times in the first period were completely absent in the second.
- The average time per request decreased from 1.56 to 1.08 ms.
On a secondary DB‑5 database, with an almost constant number of user queries, the generation of pre—recording logs (WAL) increased by 16% - an effect that logically can be attributed to the background activity of online_analyze, which generates additional records during autoanalysis of modifiable tables.
Already at this stage, it could be argued that the combination of changes led to a qualitative improvement in the efficiency of working with the disk, but the cause-and-effect relationships required a deeper check.
3. Primary hypothesis: current statistics and stabilization plans
Based on the documentation, the following hypothesis was formulated.
The autoprepare_threshold = 0 parameter forces the scheduler to immediately switch to using a generic plan for prepared queries, and generic_plan_fuzz_factor = 1 allows such a plan as liberally as possible, even at a slightly higher estimated cost. At the same time, online_analyze = on ensures that table statistics are always up-to-date immediately after DML operations. The logical consequence, as expected, is the stabilization of execution plans and a more accurate choice of optimal access methods — in particular, the predominance of index scans instead of sequential ones, which explains the decrease in physical reads and the increase in the cache hit ratio (Hit ratio increased from 92.2% to 93.9%).
This hypothesis had a right to life, but it had a serious flaw: it was not supported by a direct analysis of the plans themselves, but was based only on macro indicators of load distribution.
The second stage of the study was required to move from assumption to proof.
4. Analysis of execution plans
Comparing the "Top SQL by execution time" and "Top SQL by I/O wait time" reports for both periods allowed us to directly see how the behavior of specific queries has changed.
The picture turned out to be different than originally thought.
The most revealing was the parameterized query 12e2db113ff929b0, which extracts data from the _InfoRg12488 table with sorting:
- In the first interval, it was carried out with three different plans: Sequential scan (Seq Scan) — 48 calls, Bitmap Heap Scan — 649 calls and Index Scan - 30,245 calls.
- In the second interval, the Seq Scan plan disappeared completely, the Bitmap Heap Scan I/O time decreased by 47%, and the Index Scan by 69%. The total I/O of this request decreased 3.6 times.
Another query, 22fb79dbb23e1e4, with a twofold increase in the number of calls, showed an 89% drop in physical read time and a 20% reduction in blocks read from disk with a significant increase in the total volume of logical calls (fetched). This behavior is typical for the transition from predominantly disk reads to efficient operation through a buffer cache, which directly indicates a change in the access method.
A non-parameterized INSERT INTO BinaryData served as a "control" example: its plan and unit I/O costs remained unchanged, and small fluctuations were proportional to the change in the number of calls. This fact has become a strong evidence that the observed effect is selective and is related specifically to requests that are sensitive to the choice between generic and specialized plans.
5. Refined mechanism: rejection of early fixation of the generic plan
A joint analysis of the plans forced a revision of the original explanation. It turned out that the key role was played not by the transition to common plans, but, on the contrary, by preventing their premature fixation.
The reconstructed chain of events.
With the old settings (autoprepare_threshold = 2, generic_plan_fuzz_factor = 0.9), after two executions, the scheduler created a general plan based on the averaged parameter values and then used it, even if in some cases its cost was higher than that of a specialized (custom) plan. If, for some reason, the generic plan used sequential scanning at the formation stage (for example, due to unsuccessful parameters in the first calls), this suboptimal plan was fixed and reproduced repeatedly.
The new parameters (autoprepare_threshold = 0, generic_plan_fuzz_factor = 1) have changed the dynamics: The trigger threshold is immediate, but at the same time the general plan's acceptance condition has become stricter. It is accepted only if its cost does not exceed the average cost of specialized (custom) plans. As a result, in many cases, the system continues to use the custom plan for each call, adapting the access method to specific parameter values. For the queries under consideration, this meant a stable choice of index scanning where previously Seq Scan could "skip".
Thus, the decrease in physical reads and temporary writes recorded in macro statistics was a direct consequence of the disappearance of suboptimal plans.
online_analyze's contribution, on the contrary, turned out to be secondary: the main problematic queries do not work with temporary tables, and the statistics of permanent tables are supported by regular autoanalysis.
The growth of WAL in DB‑5, however, is logically explained precisely by the activity of online_analyze, which generates additional records with frequent analysis of modifiable data.
6. Confidence levels and necessary verification
Following the methodology adopted in the study, each statement was provided with a level of validity.
- Confirmed (level 1): the actual values of metrics from pgpro_pwr reports, the presence of specific execution plans, their disappearance or change.
- Probably (level 2): the causal relationship between the change in autoprepare_threshold/generic_plan_fuzz_factor parameters and the change in execution plans, as well as the interpretation of this change as the main reason for the improvement of macro indicators.
- Assumption (level 3): accurate separation of the contribution of each of the three parameters in the absence of a controlled experiment and real-time statistical table data.
To transfer the hypothesis to the "Confirmed" status, the author recommends conducting an isolated A/B test on a stand with identical query instances, a detailed analysis of pg_stat_statements by plan IDs, and logging via auto_explain. Additionally, it is necessary to check the moments of the last table analyses (pg_stat_user_tables.last_analyze) and the values of work_mem/effici_cache_size in both intervals in order to exclude alternative explanations.
7. Conclusion
The conducted research clearly demonstrates that in environments with high data variability and intensive use of prepared queries, the management parameters of generic plans can radically change the I/O profile. The second period showed not just a reduction in the load on the disk subsystem, but a qualitative transition to more efficient caching and the use of indexes. At the same time, updating statistics in real time, contrary to the initial intuition, plays an auxiliary role here; the main effect is due to the subtle mechanics of choosing between generic and custom plans.
The results already provide a practical justification for reviewing scheduler settings in productive systems, and the proposed dual verification methodology — first macroanalysis, then immersion in execution plans — can serve as a template for further research in the field of predictable PostgreSQL optimization.
Методология анализа отчётов pgpro_pwr с помощью PG_EXPECTO и DeepSeek: планы выполнения PostgreSQL 17 при использовании online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1.
Эмпирическая реконструкция причинно-следственного механизма: почему отказ от преждевременной фиксации общих планов оказался решающим фактором снижения дисковой нагрузки, а актуализация статистики в реальном времени — лишь сопутствующим условием.
Часть-1: Профиль нагрузки PostgreSQL 17 при online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1(1/2)
Часть-2: Профиль нагрузки PostgreSQL 17 при online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1 (2/2)
Аннотация.
Сравнительный анализ двух эксплуатационных периодов кластера PostgreSQL 17 с кардинально различающимися настройками планировщика и автоанализа выявил сложную картину. Изменение параметров autoprepare_threshold (Подробнее) , generic_plan_fuzz_factor (Подробнее) и online_analyze (Подробнее) привело к снижению общего времени выполнения запросов на 21 %, времени ввода-вывода на 31 % и полному исчезновению сбросов разделяемого кэша при одновременном росте числа выполняемых операций на 14 %.
Детальное сопоставление планов выполнения позволило уточнить механизм: ключевым драйвером улучшения стал отказ от преждевременной фиксации общих планов, а не актуализация статистики сама по себе. Статья резюмирует ход исследования и формулирует итоговую, эмпирически обоснованную гипотезу, снабжённую уровнями достоверности.
1. Введение
Точная настройка планировщика запросов — вечная тема для администраторов PostgreSQL. Расширение online_analyze, порог автоматической подготовки общих планов autoprepare_threshold и фактор нечёткости generic_plan_fuzz_factor по отдельности хорошо документированы, однако их совместное действие в живой многопользовательской среде до сих пор оставалось малоизученным.
Настоящее исследование восполняет этот пробел, опираясь на дифференциальный отчёт pgpro_pwr, снятый в двух часовых интервалах на продуктивном кластере PostgreSQL 17.
Рис.1 - График изменения метрики "Disk utilization" для диска, используемого файловой системой PGDATA
Первый интервал (снимки 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.
Никакие другие параметры сервера не менялись.
2. Что показала макростатистика
Основная рабочая база (условное имя DB‑4) аккумулировала более 99 % всей нагрузки кластера, поэтому именно её метрики стали центральным объектом анализа. Переход к новым настройкам сопровождался статистически значимыми и однонаправленными сдвигами:
- Общее время выполнения (Total time) снизилось на 21,3 % (с 9939 до 7824 с), тогда как количество выполненных запросов выросло на 14,0 % (с 6,36 до 7,25 млн).
- Время ввода-вывода (I/O time) упало ещё заметнее — на 30,8 %.
- Чтение разделяемых блоков с диска (Shared blocks read) сократилось на 27,5 %, а запись временных/локальных блоков — на 27,5 %.
- Сбросы разделяемого кэша (Cache resets), зафиксированные четыре раза в первом периоде, во втором отсутствовали полностью.
- Среднее время одного запроса уменьшилось с 1,56 до 1,08 мс.
На второстепенной базе DB‑5 при практически неизменном количестве пользовательских запросов генерация журналов предзаписи (WAL) выросла на 16 % — эффект, который логично связать с фоновой активностью online_analyze, порождающей дополнительные записи при автоанализе модифицируемых таблиц.
Уже на этом этапе можно было утверждать, что совокупность изменений привела к качественному улучшению эффективности работы с диском, однако причинно-следственные связи требовали более глубокой проверки.
3. Первичная гипотеза: актуальная статистика и стабилизация планов
На основе документации была сформулирована следующая гипотеза.
Параметр autoprepare_threshold = 0 заставляет планировщик немедленно переходить к использованию общего (generic) плана для подготовленных запросов, а generic_plan_fuzz_factor = 1 максимально либерально допускает такой план даже при несколько более высокой оценочной стоимости. Одновременно online_analyze = on обеспечивает постоянную актуальность статистики таблиц сразу после операций DML. Логичным следствием, как предполагалось, становится стабилизация планов выполнения и более точный выбор оптимальных методов доступа — в частности, преобладание индексных сканирований вместо последовательных, что и объясняет снижение физических чтений и рост коэффициента попадания в кэш (Hit ratio вырос с 92,2 % до 93,9 %).
Эта гипотеза имела право на жизнь, но обладала серьёзным изъяном: она не была подкреплена прямым анализом самих планов, а опиралась лишь на макропоказатели распределения нагрузки.
Для перехода от предположения к доказательству потребовался второй этап исследования.
4. Анализ планов выполнения
Сравнение отчётов «Top SQL by execution time» и «Top SQL by I/O wait time» за оба периода позволило непосредственно увидеть, как изменилось поведение конкретных запросов.
Картина оказалась иной, чем предполагалось изначально.
Наиболее показательным стал параметризованный запрос 12e2db113ff929b0, извлекающий данные из таблицы _InfoRg12488 с сортировкой:
- В первом интервале он выполнялся с тремя разными планами: последовательное сканирование (Seq Scan) — 48 вызовов, сканирование по битовой карте (Bitmap Heap Scan) — 649 вызовов и индексное сканирование (Index Scan) — 30 245 вызовов.
- Во втором интервале план Seq Scan исчез полностью, время ввода-вывода Bitmap Heap Scan сократилось на 47 %, а Index Scan — на 69 %. Суммарное I/O этого запроса уменьшилось в 3,6 раза.
Другой запрос, 22fb79dbb23e1e4, при двукратном росте числа вызовов продемонстрировал падение времени физического чтения на 89 % и сокращение читаемых с диска блоков на 20 % при значительном увеличении общего объёма логических обращений (fetched). Такое поведение характерно для перехода от преимущественно дисковых чтений к эффективной работе через буферный кэш, что прямо указывает на смену метода доступа.
«Контрольным» примером послужил не параметризованный INSERT INTO BinaryData: его план и удельные затраты ввода-вывода остались неизменными, а небольшие колебания были пропорциональны изменению числа вызовов. Этот факт стал сильным свидетельством того, что наблюдаемый эффект избирателен и связан именно с запросами, чувствительными к выбору между обобщим(generic) и специализированным(custom) планами.
5. Уточнённый механизм: отказ от ранней фиксации generic-плана
Совместный анализ планов заставил пересмотреть первоначальное объяснение. Оказалось, что ключевую роль сыграл не переход к общим планам, а, напротив, предотвращение их преждевременной фиксации.
Реконструированная цепочка событий.
При старых настройках (autoprepare_threshold = 2, generic_plan_fuzz_factor = 0.9) планировщик после двух выполнений создавал общий план на основе усреднённых значений параметров и затем использовал его, даже если в отдельных случаях его стоимость была выше, чем у специализированного(custom) плана. Если по какой-то причине на этапе формирования generic-план использовал последовательное сканирование (например, из-за неудачных параметров в первых вызовах), этот неоптимальный план закреплялся и воспроизводился многократно.
Новые параметры (autoprepare_threshold = 0, generic_plan_fuzz_factor = 1) изменили динамику: порог срабатывания немедленный, но одновременно условие допустимости общего плана стало жёстче — он принимается только если его стоимость не превышает среднюю стоимость специализированных(custom) планов. В результате во многих случаях система продолжает использовать custom-план для каждого вызова, адаптируя метод доступа к конкретным значениям параметров. Для рассматриваемых запросов это означало стабильный выбор индексного сканирования там, где раньше мог «проскакивать» Seq Scan.
Таким образом, снижение физических чтений и временных записей, зафиксированное в макростатистике, стало прямым следствием исчезновения неоптимальных планов.
Вклад online_analyze, напротив, оказался вторичным: основные проблемные запросы не работают с временными таблицами, а статистика постоянных таблиц поддерживается и штатным автоанализом.
Рост WAL в DB‑5, однако, логично объясняется именно активностью online_analyze, генерирующей дополнительные записи при частом анализе модифицируемых данных.
6. Уровни достоверности и необходимая верификация
Следуя методологии, принятой в исследовании, каждое утверждение было снабжено уровнем обоснованности.
- Подтверждено (уровень 1): фактические значения метрик из отчётов pgpro_pwr, наличие конкретных планов выполнения, их исчезновение или изменение.
- Вероятно (уровень 2): причинная связь между изменением параметров autoprepare_threshold/generic_plan_fuzz_factor и сменой планов выполнения, а также интерпретация этой смены как главной причины улучшения макропоказателей.
- Предположение (уровень 3): точное разделение вклада каждого из трёх параметров в отсутствие контролируемого эксперимента и данных о состоянии статистики таблиц в реальном времени.
Для перевода гипотезы в статус «Подтверждено» автор рекомендует проведение изолированного A/B-теста на стенде с воспроизведением идентичных экземпляров запросов, детальный анализ pg_stat_statements по идентификаторам планов и логирование через auto_explain. Дополнительно необходима проверка моментов последних анализов таблиц (pg_stat_user_tables.last_analyze) и значений work_mem/effective_cache_size в обоих интервалах, чтобы исключить альтернативные объяснения.
7. Заключение
Проведённое исследование наглядно демонстрирует, что в средах с высокой вариативностью данных и интенсивным использованием подготовленных запросов параметры управления обобщим(generic) планами способны радикально изменить профиль ввода-вывода. Второй период показал не просто снижение нагрузки на дисковую подсистему, а качественный переход к более эффективному кэшированию и использованию индексов. При этом актуализация статистики в реальном времени, вопреки первоначальной интуиции, играет здесь вспомогательную роль; главный эффект обусловлен тонкой механикой выбора между generic- и custom-планами.
Результаты уже сейчас дают практическое обоснование для пересмотра настроек планировщика в продуктивных системах, а предложенная методология двойной верификации — сначала макроанализ, затем погружение в планы выполнения — может служить шаблоном для дальнейших исследований в области предсказуемой оптимизации PostgreSQL.
Share
