Побывав на PGConf.DE’2025 и обсуждая там практику применения Postgres на больших базах данных, я к своему удивлению регулярно слышал мнение, что проблемой является время планирования запроса. Как разработчику, мне было странно узнать, что этот фактор может, например, тормозить принятие решения о переходе на партиционирование, что казалось бы естественный шаг, когда количество записей в таблице переваливает за сотню миллионов. Что ж, давайте разбираться.

Очевидный выход в такой ситуации - использовать PREPARED statements, изначально предназначенные для переиспользования таких трудоемких в построении элементов, как parse tree и query plan. Для большей конкретики посмотрим простое сканирование таблицы с большим количеством партиций (см. скрипт инициализации):

EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF)
SELECT * FROM test WHERE y = 127;

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = 127)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3787kB  allocated=4104kB
 Planning Time: 61.272 ms
 Execution Time: 4.929 ms
*/

Для случая выборки из таблицы, содержащей 256 партиций время планирования у меня составило порядка 60 мс, при этом выполнение - 5 мс. Также, в ходе построения плана выделено 4 МБ RAM и потрогано 1500 страниц БД. Мне бы тоже не понравились такие накладные расходы на проде!

Здесь мы имеем так называемый custom план, который создаётся заново при каждом выполнении запроса и при оптимизации учитываются пришедшие значения параметров запроса. Параметризуем этот запрос и поместим его в "Кэш планов" бэкенда, выполнив PREPARE:

PREPARE tst (integer) AS SELECT * FROM test WHERE y = $1;
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3772kB  allocated=4120kB
 Planning Time: 59.525 ms
 Execution Time: 5.184 ms
*/

Трудоёмкость планирования осталась на том же уровне, поскольку используется custom план. Заставим бэкенд сгенерировать и использовать обобщённый “generic” план:

SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
  ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Memory: used=4kB  allocated=24kB
 Planning Time: 0.272 ms
 Execution Time: 2.810 ms
*/

При первом выполнении строится т.н. generic план, требующий тех же ресурсов на построение, что и custom план. Однако при последующем выполнении запроса план просто извлекается из кэша. Можно заметить, что время затраченное на подготовку плана запроса теперь составляет 0.2 мс, количество аллоцированной памяти - 24 кб, обращений к БД не было вообще. Явный профит!

Однако предложение использовать команду PREPARE обычно вызывает отторжение и скепсис, поскольку (как оказалось) в эксплуатации часто возникают проблемы с generic планами, их актуализацией (перепланированием) и переключением на custom планы (link). Чтобы лучше понять как устроен механизм generic планов и в чем суть проблемы, я решил заглянуть в историю этого проекта, а заодно опробовать новый формат публикации - обзор списков рассылки на заданную тему.

Концепция plancache, или "Кэш планов" появилась в 2007 году с коммитом b9527e9. В тот момент предполагалось, что каждый PREPARED-запрос в Postgres должен выполняться исключительно generic планом и не тратить время на перестроение плана вовсе. При этом, в отличие от Oracle, SQL Server и других собратьев по цеху, в Postgres с самого начала generic план строится без использования какого-нибудь конкретного “референсного” значения параметра (см., например, link, link, link, link). То есть, в приведённом выше примере константа "127" будет отброшена на время создания generic-плана.

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

EXPLAIN
SELECT * FROM test_2
WHERE
  start_date > '2025-06-30'::timestamp - '7 days'::interval;
/*
 Index Scan using test_2_start_date_idx on test_2  (rows=739 width=34)
   Index Cond: (start_date > '2025-06-23 00:00:00'::timestamp)
*/

PREPARE tst3(timestamp) AS SELECT * FROM test_2
  WHERE start_date > $1 - '7 days'::interval;

EXPLAIN EXECUTE tst3('2025-06-30'::timestamp);
/*
 Seq Scan on test_2  (rows=333333 width=34)
   Filter: (start_date > ($1 - '7 days'::interval))
*/

Навскидку, здесь можно отметить следующие основные причины: отсутствие константы в операторе неравенства приводит к эстимации фильтра в 33%; для  range-фильтров дефолтное значение составляет 0.5% от количества строк в таблице, а в операторе равенства не получится использовать MCV-статистику и придётся ориентироваться ориентироваться только на ndistinct. Также, например, в ряде случаев невозможно использовать частичные индексы.

Отсутствие альтернатив приводило к заметной деградации производительности, редкому использованию вообще-то полезной конструкции PREPARE/EXECUTE statement и в 2011 году стартовала дискуссия, которая привела к коммиту e6faf91, позволившему переключаться между custom и generic вариантами плана по некоторой несложной автоматической методике.

Дискуссия эта началась с обсуждения назревшей проблемы, вызванной тем, что PREPARED statements выполнялись исключительно generic-планами (Mark Mielke, link). Такой план хоть и перестраивался каждый раз по сигналу инвалидации - например, после команды ANALYZE или ALTER TABLE, однако имел заметно худшее качество планирования.

Для решения проблемы высказывались различные идеи:

• Периодически перепланировать generic-план (Jeroen Vermeulen, link).

• Ввести threshold по соотношению “время планирования/ выполнения“ - Если величина критерия больше 100, то использовать только generic план, если меньше 0.01 - то только custom-план. (Bart Samwel link. Yeb Havinga оппонирует (link) этой идее - объективный критерий не должен содержать в себе параметр “время“). Однако, была поддержана Jeroen Vermeulen и Greg Stark (link) c оговоркой, что разница между временем планирования и выполнения должна быть существенной и составлять порядки величины.

• Отслеживать значение stddev различных параметров выполнения конкретного плана запроса, что даст возможность оценивать вероятность того, как долго будет планироваться/выполняться запрос в следующий раз (Greg Stark, link).

• Строить некоторое количество кастомных и  generic план, делать выбор исходя из соотношения костов (Tom Lane, link).

• Отказаться от generic планов вовсе, одновременно удешевив перепланирование путём сохранения “кэша” оптимизатора PlannerInfo и перепланированием только той части jointree / подзапроса, где параметры реально используются (Yeb Havinga, link).

• Использовать generic  планы, но ввести критерий перепланирования - попадает значение параметра в MCV или нет (Robert Haas (link, link), поддержано Jeff Davis).

Интересно, что уже тогда высказывалась идея re-optimisation (Richard Huxton, link). В то время она предлагалась скорее в форме мечты, но уже к 2020 году инфраструктура созрела достаточно, что нам в короткие сроки удалось реализовать аналогичную идею (см. replan). Здесь также в явном виде описана концепция  обнаружения, обобщения и кэширования часто приходящих через simple-протокол стэйтментов, реализованная нами в sr_plan (Robert Haas, link и идея Yeb Havinga сделать это через подобие тогда ещё не существовавшего queryId link).

Также в 2011 году возникла идея one-shot plan (Simon Riggs, link). Основной смысл этого типа планов - сигнализировать СУБД, что план запроса будет построен, немедленно запущен на выполнение и уничтожен по окончании. Это позволит использовать дополнительные оптимизации, неактуальные в том случае, когда нет связки момента планирования и исполнения.

В поддержку идеи Simon привёл один пример - вычисление стабильных функций, что позволит эффективнее выполнять partition pruning. В поддержку, Bruce Momjan озвучил идею дополнительной оптимизации, которая может быть реализована в one-shot plan - заглянуть в буферный кэш чтобы оценить эффективность использования того или иного индекса.

В это же время похожую фичу разрабатывал Tom Lane (link). И закоммитил он её в результате жалоб на регрессию в dynamic SQL запросах (link, link). Однако, его концепция принципиально отличалась от изначальной идеи Simon Riggs и в оригинале состояла в том, чтобы просто унифицировать механизмы SPI, PREPARE и extended protocol через использование plancache. В результате, оригинальная идея не получила активного продолжения, хотя и дискутировалась позднее (link, link).

Идея отслеживания времени планирования и выполнения запросов не прижилась из-за возражения Tom Lane (link), который высказался против использования характеристики времени, поскольку она по определению непредсказуема и будет нестабильно себя вести на разных системах.

В 2017 году Pavel Stehule поднял тему явного управления типом плана, который будет выбираться при обращении к plancache (link). Это привело к  появлению параметра plan_cache_mode и двух опций - force_generic_plan и force_custom_plan для безальтернативного использования  generic и custom типа плана соответственно.

Что особенно любопытно мне как разработчику, в ходе всех этих дискуссий были озвучены следующие ключевые концепции ядра  Postgres:

• Tom Lane указал, что в отсутствии общего решения, нужно разрабатывать эвристики (link), а отдавать такие решения на откуп пользователям предлагая очередной GUC - плохая идея и результат компромисса.

• Предсказуемость выполнения важнее скорости (Greg Stark, Pavel Stehule link).

• Переключение между типами плана запроса имеет ценность, если это можно регулировать на уровне каждого отдельного запроса (Tom Lane link).

Итак, анализируя историю создания фичи, мнения, высказанные в сообществе и текущий опыт использования generic планов я прихожу к выводу, что большинство текущих проблем вызвано следующими причинами:

• generic план может демонстрировать нестабильную производительность на разных наборах значений входных параметров. Это очевидно требует переключения на custom  тип плана. Однако Postgres не может заметить этого и совершить переключение в автоматическом режиме, поскольку он не ведёт в том или ином виде статистики трудоёмкости выполнения запроса. Текущее состояние кода ядра позволяет достаточно просто имплементировать отслеживание различных параметров выполнения, контроль средней величины и стандартного отклонения. Однако до внедрения такого изменения придется ответить на принципиальный вопрос - должна ли в ядре Postgres появиться система обратной связи от executor’a к optimiser’у?

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

• Неадекватные косты планов. Постоянно встречающаяся проблема, когда ошибочные эстимации делают косты плана запроса нерелевантным реальной нагрузке. Это приводит к тому, что выбор между custom и generic планами становится делом случая.

И что же делать? Как обычно, есть два решения - ядерное, для коммьюнити и расширяемое, включая патч в ядро для форков постгреса.

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

Второе предложение чуть более спорно - добавить режим создания generic плана с использованием референсных значений констант. Не имея принципиальных преимуществ это даст пользователям привычное поведение или знакомый инструмент влияния на план запроса при миграции с, например, SQL Server.

Влиять на переключение планов можно ещё более гибко, если выйти за пределы только ванильного решения. Первое - можно вести статистику времени планирования и выполнения для каждого из планов, сравнивать со значениями костов и принимать решение о перепланировании или даже форсировании определенного типа плана. Ещё лучше - ориентироваться на количество страниц, прочитанных в ходе выполнения запроса. Для наглядности можно посмотреть в проект, который является наброском для системы управления типами планов в автоматическом режиме и другая ветка там же - набросок для переключения между форсированными режимами.

А вы сталкивались с проблемами при использовании generic планов? Есть ли смысл создавать целую систему для переключения планов или достаточно просто сделать расширение, которое позволит для каждого конкретного prepared statement отслеживать его состояние влиять на его состояние вручную, средствами SQL задействуя, например, pg_stat_statements?

Ссылки

Hackers' mailing lists threads:

1. Avoiding bad prepared-statement plans. , 2010-02

2. Restructuring plancache.c API , 2010-11

3. One-Shot Plans , 2011-06

4. Transient plans versus the SPI API , 2011-08

5. why do we need two snapshots per query? , 2011-11

6. dynamic SQL - possible performance regression in 9.2 , 2012-12

7. PoC plpgsql - possibility to force custom or generic plan , 2017-01

8. The logic behind comparing generic vs. custom plan costs , 2025-03

9. inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) ,2025-05

Main commits:

• b9527e9 - видимо первый заход на фичу, 2007-03

• e6faf91 появилась возможность создавать кастомные планы, 2011-09

• 94afbd5 - появление one-shot entries, 2013-01

• 2aac339 - уточнение модели оценки стоимости планирования, 2013-09

• f7cb284 - появление plan_cache_mode setting, 2018-07

THE END.
Испания, Мадрид, 29 июня 2025 года.