Методология фиксации планов выполнения в PostgreSQL и Oracle: сравнительный анализ

Материал полностью подготовлен нейросетью.

PostgreSQL: свобода планирования в каждом соединении; Oracle: управляемая верификация для всех

Сокращенный вариант:

Глоссарий

План выполнения (execution plan) — последовательность операций, которую сервер базы данных использует для получения результата SQL-запроса. Включает методы доступа к данным (последовательное сканирование, индексное сканирование), способы соединений и сортировок.

Фиксация плана (plan stability) — способность СУБД гарантировать, что однажды выбранный план выполнения будет повторно использоваться при последующих запусках того же запроса, предотвращая случайные изменения, способные вызвать падение производительности.

Подготовленный оператор (prepared statement) — SQL-запрос, разобранный и оптимизированный заранее, после чего он может многократно выполняться с разными значениями параметров без повторения стадий парсинга и планирования.

Generic-план (обобщённый план) — план выполнения, не учитывающий конкретные значения параметров запроса. Оценивается по усреднённой статистике и может быть использован для любого набора значений. Минимизирует накладные расходы на планирование, но рискует оказаться неоптимальным при сильном разбросе данных.

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

autoprepare_threshold — параметр PostgreSQL (Postgres Pro), определяющий, после какого количества выполнений параметризованного запроса сервер автоматически перейдёт к использованию подготовленного оператора. Значение 0 отключает автоподготовку; значение N запускает её после N повторений.

generic_plan_fuzz_factor — параметр PostgreSQL, задающий порог, на который стоимость generic-плана может превышать стоимость custom-плана, чтобы оптимизатор всё равно выбрал generic-план. При значении 1 (максимум) generic-план выбирается, только если его стоимость не выше custom-плана.

online_analyze — расширение PostgreSQL (Postgres Pro), автоматически запускающее ANALYZE для таблиц после операций INSERT, UPDATE, DELETE, поддерживая актуальность статистики без ожидания планового autovacuum.

SQL Plan Baseline (базовая линия планов) — основной механизм Oracle для управления стабильностью планов. Представляет собой совокупность принятых (accepted) планов выполнения, которыми оптимизатор может пользоваться; непроверенные планы находятся в статусе non-accepted и не применяются до верификации.

SQL Plan Management (SPM) — подсистема Oracle, реализующая эволюцию и контроль планов на основе baseline. Включает автоматический захват, хранение истории, проверку новых планов и возможность их принятия или отклонения.

Stored Outlines (хранимые контуры) — устаревшая технология Oracle, фиксирующая план через набор подсказок (hints). Жёстко привязана к тексту запроса и лишена механизмов адаптации.

Bind Variable Peeking (подсмотр переменных связывания) — техника Oracle, при которой оптимизатор анализирует фактические значения переменных связывания при первой компиляции запроса, чтобы выбрать оптимальный план. В последующих выполнениях план может переиспользоваться с риском неоптимальности для других значений.

Регрессия плана (plan regression) — ситуация, когда новый план выполнения, появившийся вследствие изменения статистики, конфигурации или версии СУБД, оказывается хуже предыдущего и вызывает деградацию производительности.

Введение

Одним из центральных вызовов при эксплуатации высоконагруженных систем управления базами данных является обеспечение стабильной и предсказуемой производительности обработки SQL-запросов. Оптимизаторы современных реляционных СУБД генерируют планы выполнения, опираясь на статистику распределения данных и ресурсные ограничения. Однако по мере изменения самой статистики, состава данных или конфигурационных параметров оптимизатор может принять решение о смене метода доступа, что иногда приводит не к улучшению, а к резкому падению производительности — так называемой регрессии плана.

Для предотвращения подобных инцидентов производители СУБД разрабатывают механизмы фиксации (стабилизации) планов выполнения. Их цель — гарантировать, что при неизменном тексте запроса будет использоваться либо заранее проверенный план, либо ограниченный набор планов, прошедших верификацию.

В настоящей статье мы сравниваем методологию фиксации планов в двух ведущих реляционных системах — PostgreSQL и Oracle, акцентируя внимание на фундаментальных архитектурных различиях, определяющих подходы к управлению планами.

1. Механизмы фиксации планов в PostgreSQL

1.1. Встроенные возможности стандартного PostgreSQL

Ядро PostgreSQL предоставляет базовые средства кеширования планов через механизм подготовленных операторов (PREPARE/EXECUTE). Клиентское приложение явно отправляет команду PREPARE, создавая параметризованный запрос, после чего последующие вызовы EXECUTE с конкретными значениями параметров пропускают стадии разбора и планирования. По умолчанию PostgreSQL после пяти выполнений заменяет custom-план на обобщённый (generic), если последний оказывается не дороже. Эта логика регулируется параметром plan_cache_mode, введённым в версии 12, который может принимать значения auto (поведение по умолчанию), force_generic_plan (всегда использовать generic) или force_custom_plan (всегда перепланировать).

Важным архитектурным ограничением является то, что кеш планов локален для каждого обслуживающего процесса (backend). Подготовленный оператор существует только в рамках одной сессии и не разделяется между другими подключениями. С одной стороны, это устраняет конкуренцию за глобальный кеш, с другой — означает, что в разных сессиях один и тот же запрос может выполняться с разными планами, и никакого централизованного контроля над их единообразием не предусмотрено.

1.2. Расширенные возможности Postgres Pro

В линейке Postgres Pro Enterprise механизмы управления планами получили существенное развитие. Ключевые дополнения включают:

• Автоматическую подготовку (autoprepare). Параметр autoprepare_threshold позволяет серверу самостоятельно выявлять часто повторяющиеся запросы и кешировать их generic-планы, не требуя от приложения команд PREPARE. Если значение установлено в N, после N-го выполнения запроса в рамках одного backend-процесса сервер автоматически создаёт подготовленный оператор и использует его при последующих вызовах. Значение по умолчанию 0 отключает эту возможность.
• Управление выбором между generic и custom планами. Параметр generic_plan_fuzz_factor ограничивает допустимое превышение стоимости generic-плана над custom-планом. По умолчанию значение равно 0.9, что позволяет выбрать generic-план, даже если его стоимость на 10 % выше. Установка в 1 ужесточает критерий: generic-план будет выбран, только если его стоимость не превышает стоимость custom-плана.
• Расширение pg_plan_advice (начиная с версии 19). Позволяет зафиксировать конкретный «совет» планировщика для воспроизведения заданного плана при последующих выполнениях запроса. Это первый шаг к централизованному управлению планами, приближающий PostgreSQL к функциональности Oracle SPM, хотя пока требующий ручного вмешательства.

1.3. Отсутствие механизма предотвращения регрессий

При использовании как стандартного PREPARE, так и autoprepare, PostgreSQL лишён встроенной защиты от регрессий. Если при изменении параметров или статистики оптимизатор начнёт генерировать более медленный custom-план, он будет применяться немедленно. В случае же использования generic-планов возникает обратная проблема: план, зафиксированный при запуске или после порогового числа вызовов, может стать неоптимальным по мере изменения данных, но не будет пересмотрен до явного перезапуска сессии или выполнения DEALLOCATE. Этот эффект особенно ярок при неудачном сочетании autoprepare_threshold и generic_plan_fuzz_factor, что будет продемонстрировано в разделе 4.

2. Механизмы фиксации планов в Oracle

Oracle Database прошла длительный путь эволюции средств стабилизации планов, и на текущий момент предлагает многоуровневую архитектуру, центральным элементом которой является SQL Plan Management (SPM).

2.1. Stored Outlines (хранимые контуры) — устаревший подход

Stored Outlines появились в Oracle 8i и представляли собой набор подсказок оптимизатора (hints), сохраняемых для конкретного оператора SQL и принудительно воспроизводящих заданный план. Основные недостатки:

• Жёсткая привязка к точному тексту запроса (включая регистр и пробелы), из-за чего даже минимальные модификации SQL ломали соответствие.
• Отсутствие какой-либо автоматической адаптации — план «замораживался» и не мог быть улучшен даже при появлении более эффективных индексов или изменении распределения данных.
• Управление требовало ручного труда администратора для каждого проблемного запроса.

Stored Outlines признаны устаревшими, но до сих пор поддерживаются для обратной совместимости.

2.2. SQL Plan Management и SQL Plan Baselines

Начиная с Oracle 11g, основным механизмом управления планами стал SQL Plan Management (SPM). Его ядро — SQL Plan Baselines (базовые линии планов). Логическое хранилище SMB (SQL Management Base), расположенное в табличном пространстве SYSAUX, содержит историю всех планов выполнения, а также три ключевых класса:

• Accepted plans — планы, одобренные к использованию. Только они могут быть выбраны оптимизатором для выполнения запроса.
• Fixed plans — подмножество принятых планов с наивысшим приоритетом. Если существует хотя бы один fixed-план, оптимизатор будет использовать его, игнорируя остальные accepted-планы.
• Non-accepted plans — планы, зафиксированные в истории, но не прошедшие верификацию. Они не участвуют в выполнении запроса, пока администратор или автоматический процесс не одобрит их.

Жизненный цикл плана в рамках SPM выглядит следующим образом:

1. Захват (Capture). При первом выполнении запроса план автоматически сохраняется в истории и, если захват включён (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE), сразу становится accepted-планом, образуя исходную baseline.
2. Обнаружение нового плана. Изменение статистики, появление новых индексов или обновление версии СУБД могут привести к тому, что оптимизатор сгенерирует план, отличный от имеющихся в baseline. Такой план помещается в историю со статусом non-accepted и не используется для выполнения.
3. Эволюция (Evolution). Специальное задание SPM Evolve Advisor (или ручной вызов DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE) проверяет, действительно ли новый план улучшает производительность. Для этого план тестируется — сравниваются метрики выполнения с эталонным accepted-планом.
4. Принятие или отклонение. Если новый план показал лучшие результаты, он переводится в статус accepted и включается в baseline. Если производительность ухудшилась — остаётся non-accepted и оптимизатором не рассматривается.

Таким образом, SPM реализует принцип «доверяй, но проверяй»: оптимизатор может искать потенциально лучшие планы, но применять их только после верификации.

2.3. SQL Profiles (профили SQL)

SQL Profiles представляют собой дополнительный механизм, воздействующий не на выбор конкретного плана, а на оценки оптимизатора. Профиль, создаваемый SQL Tuning Advisor, корректирует статистику кардинальности (предполагаемое число строк, возвращаемых операцией), позволяя планировщику самостоятельно построить более точный план без жёсткой фиксации. Такой подход даёт большую гибкость: план может адаптироваться к изменениям данных, но с уточнёнными исходными оценками.

3. Сравнение методологий фиксации планов

3.1. Архитектурный фундамент: локальный против глобального кеша

Коренное различие между PostgreSQL и Oracle в контексте управления планами кроется в модели кеширования.

• Oracle хранит планы выполнения в разделяемом пуле (SGA) — глобальной области памяти, доступной всем серверным процессам. Это даёт возможность централизованно управлять планами: baseline, зафиксированная для запроса, действует для всех сессий одновременно. Отсюда вытекает необходимость в механизме предотвращения регрессий: если один и тот же план применяется везде, его случайное ухудшение немедленно затронет всю систему.
• PostgreSQL работает по process-per-connection модели, и каждый backend хранит собственный локальный кеш планов. Prepared-оператор и его generic-план существуют только в рамках одного соединения. Следовательно, в одной сессии запрос может выполняться по Index Scan, а в другой — по Seq Scan, и никакой централизованной точки контроля нет. Это архитектурное решение снимает проблему конкуренции за глобальный кеш, но лишает администратора возможности единообразно управлять планами.

3.2. Подход к предотвращению регрессий

Oracle: Полноценная система предотвращения регрессий. Любой новый план попадает в карантин (статус non-accepted) и не применяется, пока не будет доказана его эффективность. Процесс эволюции автоматизирован (SPM Evolve Advisor) или управляется вручную.

PostgreSQL: Встроенный механизм отсутствует. Параметр plan_cache_mode=force_generic_plan позволяет зафиксировать обобщённый план, но не гарантирует его оптимальности при изменчивости данных. generic_plan_fuzz_factor и autoprepare_threshold могут либо создавать риск преждевременной фиксации неоптимального generic-плана (при низком пороге и малом fuzz_factor), либо полностью отказываться от кеширования планов. Механизма верификации новых планов до их применения нет.

3.3. Эволюция планов

Oracle: Поддерживается эволюция baseline — автоматический или ручной процесс, при котором новый план сравнивается с существующим accepted-планом и при улучшении метрик включается в baseline. Это позволяет системе со временем адаптироваться к изменению данных и схемы, не опасаясь внезапных регрессов.

PostgreSQL: Эволюция планов не предусмотрена. Однажды зафиксированный (через pg_plan_advice или force_generic_plan) план остаётся неизменным до явного вмешательства. Если план перестал быть эффективным, администратор должен вручную пересмотреть настройки.

3.4. Переносимость планов

Oracle: SQL Plan Baselines можно экспортировать и импортировать между базами данных с помощью DBMS_SPM или Data Pump. Это позволяет переносить проверенные планы из тестовой среды в продуктивную, сокращая риск регрессий при миграции.

PostgreSQL: Перенос планов между экземплярами не поддерживается. Поскольку планы локальны для процессов, не существует формата их сериализации и механизма импорта.

3.5. Интеграция с подсказками оптимизатору

Oracle: Подсказки (hints) встроены в ядро и могут быть внедрены в SQL-запросы через комментарии /*+ ... */. Они позволяют администратору принудительно влиять на выбор метода доступа или порядка соединений, а также используются в Stored Outlines и SPM для сохранения конкретных планов.

PostgreSQL: Прямые подсказки отсутствуют. Косвенно повлиять на планировщик можно через параметры enable_seqscan, enable_indexscan и т.п., но эти настройки глобальны. Стороннее расширение pg_hint_plan добавляет hint-подобную функциональность, однако оно не является частью стандартной поставки.

3.6. Сводное сопоставление ключевых характеристик

• Основной механизм: PostgreSQL — PREPARE/autoprepare; Oracle — SQL Plan Baselines.
• Активация: PostgreSQL — явная (PREPARE) или автоматическая (autoprepare_threshold); Oracle — автоматический захват (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES) или ручной через DBMS_SPM.
• Уровень кеширования: PostgreSQL — локальный (backend-процесс); Oracle — глобальный (SGA).
• Срок жизни плана: PostgreSQL — до завершения сессии или сброса кеша; Oracle — постоянно в SMB до явного удаления.
• Предотвращение регрессий: PostgreSQL — отсутствует; Oracle — полное (non-accepted → эволюция → accepted).
• Эволюция планов: PostgreSQL — отсутствует; Oracle — автоматическая и ручная.
• Перенос планов: PostgreSQL — невозможен; Oracle — поддерживается экспорт/импорт.
• Подсказки оптимизатору: PostgreSQL — отсутствуют в ядре (стороннее pg_hint_plan); Oracle — встроены.
• Зрелость: PostgreSQL — базовый уровень; Oracle — высокозрелый, развивается с 2008 г.

4. Практическая иллюстрация: влияние autoprepare_threshold на профиль нагрузки PostgreSQL

Чтобы продемонстрировать, как настройки управления планами могут влиять на производительность в реальной системе, рассмотрим результаты эксперимента, проведённого на продуктивном кластере PostgreSQL 17.

4.1. Условия эксперимента

Сравнивались два часовых периода:

• Период 1: online_analyze.enable=off, autoprepare_threshold=2, generic_plan_fuzz_factor=0.9.
• Период 2: online_analyze.enable=on, autoprepare_threshold=0, generic_plan_fuzz_factor=1.

Основная нагрузка (>99%) приходилась на базу данных DB-4, обслуживающую параметризованные запросы к таблицам, чувствительным к распределению значений.

4.2. Наблюдаемые изменения метрик

• Общее время выполнения запросов (Total time) снизилось с 9939 с до 7824 с (–21,3%).
• Время ввода-вывода (I/O time) сократилось с 3642 с до 2521 с (–30,8%).
• Количество выполненных запросов (Executed count) выросло с 6,36 млн до 7,25 млн (+14,0%).
• Число прочитанных с диска разделяемых блоков (Shared blocks read) уменьшилось с 80,0 млн до 58,0 млн (–27,5%).
• Объём записанных временных и локальных блоков сократился на 27,5%.
• Коэффициент попадания в кеш (Hit ratio) вырос с 92,2% до 93,9%.
• Сбросы разделяемого кеша полностью прекратились (с 4 случаев до 0).

4.3. Интерпретация на основе планов выполнения

Детальный анализ Top SQL показал, что для ключевого параметризованного запроса 12e2db113ff929b0 произошли радикальные изменения планов выполнения:

• План с последовательным сканированием (Seq Scan), использовавшийся в Период 1 (48 вызовов, I/O 25,75 с), полностью исчез во втором периоде — 0 вызовов.
• Bitmap Heap Scan сократил время I/O на 47% (с 24,26 с до 12,82 с).
• Index Scan снизил время I/O на 69% (с 32,0 с до 9,91 с).

Суммарное время I/O для данного запроса уменьшилось в 3,6 раза.

Причина: в Период 1 параметр autoprepare_threshold=2 приводил к тому, что после двух выполнений запроса в рамках одной сессии кешировался generic-план, который из-за усреднённых оценок параметров выбирал Seq Scan. Значение generic_plan_fuzz_factor=0.9 допускало такой выбор, даже если стоимость generic-плана на 10% превышала стоимость custom-плана. В Период 2 autoprepare_threshold=0 отключил автоподготовку, а fuzz_factor=1 разрешил выбор generic-плана лишь при его строго равной или меньшей стоимости. В результате планировщик каждый раз генерировал custom-план с учётом реальных значений параметров, что практически всегда приводило к оптимальному Index Scan.

Этот кейс наглядно иллюстрирует, как отсутствие в PostgreSQL механизма верификации планов (подобного SPM Evolve) может приводить к фиксации неоптимального решения при неправильно подобранных настройках, и как точечное изменение параметров способно высвободить скрытый потенциал производительности.

Заключение

Методология фиксации планов выполнения в PostgreSQL и Oracle базируется на принципиально разных архитектурных предпосылках.

Oracle, используя глобальный разделяемый пул, вынужден был разработать многоуровневую систему SQL Plan Management, предотвращающую регрессии через карантин новых планов и их эволюцию.

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

Механизмы autoprepare и pg_plan_advice в Postgres Pro существенно расширяют возможности управления планами, однако ещё не достигают уровня зрелости Oracle SPM в части автоматического предотвращения регрессий и эволюции планов. Практический эксперимент с autoprepare_threshold продемонстрировал, что грамотная настройка параметров может дать значительный прирост производительности (снижение Total time на 21%, I/O на 31%), но при этом сохраняется риск фиксации неоптимального плана, который в Oracle был бы своевременно выявлен и заблокирован.

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

Развитие pg_plan_advice и возможное появление аналогов SPM в будущих версиях PostgreSQL обещают сократить существующий разрыв, приближая открытую СУБД к лучшим практикам управления планами, выработанным в индустрии.