Привет, Хабр! Меня зовут Алёна Рыбакина, уже четыре года я разработчик компании Postgres Professional и контрибьютор в ванильный PostgreSQL. В статье расскажу о расширении pgpro_planner, которое помогает стандартному оптимизатору находить лучшие планы там, где он обычно «спотыкается».

Я работаю с кодом оптимизатора и часто вижу запросы, время выполнения которых увеличивается с долей миллисекунды до нескольких минут или даже часов. В докладе они прячутся под «маленькими, но мощными оптимизациями», на самом же деле за каждым из них — реальная боль.

Расширение pgpro_planner выросло именно из таких историй. В Postgres Professional мы используем pgpro_planner как экспериментальную площадку для улучшений планировщика: обкатываем идеи на Postgres Pro Enterprise, а самые удачные решения отправляем в ванильный PostgreSQL. Так уже произошло, например, с оптимизацией IN (VALUES …) в PostgreSQL 18, о которой я расскажу позже.

Что делает оптимизатор и почему ему больно?

Любой запрос к PostgreSQL начинается с вполне человеческого текста вроде SELECT * FROM users;, но внутри ядра он очень быстро превращается в дерево объектов query tree. В нём уже нет «звёздочек» и «селектов», зато есть узлы с конкретными таблицами, колонками, условиями и операциями соединения.

Дальше за дело принимается планировщик, который среди всех возможных планов пытается выбрать оптимальный (в его терминологии — самый дешёвый). Оценщик кардинальности должен понять, сколько строк ожидается на каждом шаге, от простого сканирования таблицы до join двух отношений. От этого зависит, насколько дорогой будет операция. 

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

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

Стоимость (условная метрика трудозатрат операции) состоит из двух частей: старт и выполнение. Например, в случае Nested Loop не нужны подго��овительные операции, это просто цикл в цикле. Для каждого тупла из одного массива значений мы ищем совпадающие туплы из другого, то есть мы считаем total cost. Это может быть трудоёмко для больших массивов значений, но небольшие обрабатываются мгновенно. 

С Hash Join другая ситуация: поскольку он сохраняет уникальные значения одного массива в виде хеш-таблицы, то нужно просто найти такое же значение по ключу в ней. Здесь придётся потратиться (startup cost) на создание хеш-таблицы, так как она требует и места в памяти для хранения значений, и времени на построение. Процесс стоит того, если массивы данных растягиваются на миллион значений. А total cost — это стоимость выполнения основной части алгоритма проверки, какие значения из массива присутствуют в хеш-таблице.

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

В оптимизаторе аналогично: startup cost отвечает за подготовку структуры данных, например хеш‑таблицы для Hash Join, а total cost суммирует подготовку и непосредственное выполнение.

Если недооценить кардинальность и стоимость, оптимизатор выбирает слишком «дешёвый» с его точки зрения план. В реальности это превращается в Nested Loop там, где выгоднее Hash Join для огромного массива данных или Seq Scan вместо Index Scan для маленького объёма. 

А если дерево запроса организовано неудачно, некоторые потенциально эффективные алгоритмы вообще не рассматриваются, потому что нужные условия «спрятаны» в форме, которую оптимизатор не умеет обрабатывать эффективно.

Именно такие случаи я и пытаюсь «закрыть» в pgpro_planner: расширение переписывает неудобные для оптимизатора куски дерева запроса в более дружелюбный вид ещё до того, как основной планировщик выберет план.

IN (VALUES …) ломает план

Поводом для первой функции pgpro_planner стал запрос из мира 1С, в котором очень любят выражения IN (VALUES ...). В реальном кейсе это был запрос к таблице с десятками миллионов строк и несколькими фильтрами по полям, оформленными через IN (VALUES (...), (...)) для типов bytea и numeric.

explain (analyze, buffers)
SELECT T1._Fld89914,
       T1._Fld89915,
       T1._Fld89916
FROM _InfoRg89913X1 T1
WHERE (T1._Fld89915 IN (VALUES (CAST(6 AS NUMERIC)) ...)) AND
  (T1._Fld91907RRef IN (VALUES ('\202 ...'::bytea) ...));

До обновления статистики запрос выполнялся быстрее миллисекунды. После обновления — около 7,5 с. Если посмотреть на план через EXPLAIN (ANALYZE, BUFFERS), сразу видно, что львиная доля времени уходит в Nested Loop Semi Join с материализацией маленьких временных таблиц, построенных из VALUES.

Nested Loop Semi Join  (actual time=7463.781..7463.782 rows=0)
  Join Filter: (t1._fld89915 = "*VALUES*".column1)
  -> Nested Loop Semi Join  (actual time=7463.780..7463.781 rows=0)
        Join Filter: (t1._fld91907rref = "*VALUES*_1".column1)
        -> Index Scan using _inforg89913_1x1 on t1  
              (actual time=10.031..4963.070 rows=13015820 loops=1)
        -> Materialize  (actual time=0.000..0.000 rows=2 loops=13015820)
              -> Values Scan on "*VALUES*_1"
                    (actual time=0.008..0.010 rows=2)
  -> Materialize  (cost=0.00..0.14 rows=8 width=32)
        (never executed)
        -> Values Scan on "*VALUES*"
              (never executed)

Planning Time: 1.046 ms
Execution Time: 7463.840 ms

Проблема в том, что PostgreSQL относится к T1._Fld89915 IN (VALUES (CAST(6 AS NUMERIC)), …) как к условию, требующему построения временной таблицы и выполнения Semi Join между основной таблицей и этой таблицей из VALUES. Для каждого списка значений создаётся отдельный Values Scan, который потом материализуется. Индекс по колонке T1._Fld89915 почти не даёт эффекта: сравнение происходит внутри join‑фильтра, а не в индексном условии.

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

Решение, которое мы нашли вместе с Андреем Лепиховым для pgpro_planner, оказалось очень простым: собрать все константные значения VALUES в массив и переписать предикат в виде x = ANY(array).

Запрос:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    T1._Fld89914,
    T1._Fld89915,
    T1._Fld89916
FROM _InfoRg89913X1 AS T1
WHERE
    T1._Fld89915 IN (
        VALUES (CAST(6 AS NUMERIC))  -- и ещё значения ...
        -- , (CAST(7 AS NUMERIC))
        -- , ...
    ) AND
    T1._Fld91907RRef IN (
        VALUES ('\\x5c\\202...'::bytea)  -- и ещё значения ...
        -- , ('\\x5c....'::bytea)
        -- , ...
    );

превратился в:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    T1._Fld89914,
    T1._Fld89915,
    T1._Fld89916
FROM _InfoRg89913X1 AS T1
WHERE
    T1._Fld89915 = ANY ('{6,7,8,9,10,11,12,15}'::numeric[]) AND 
    T1._Fld91907RRef = ANY (
        '{"\\x5c..." , "\\x5c..."}'::bytea[]
    );

После такой трансформации оптимизатор уже видит привычное ему выражение = ANY(array) и может строить чистый Index Scan или Bitmap Index Scan по соответствующему полю. В плане исчезают Nested Loop Semi Join и временные таблицы с VALUES, индекс попадает в Index Cond, а время выполнения падает с 7 секунд до долей миллисекунды.

Эту функциональность в итоге закоммитили в PostgreSQL 18.

Ограничения для IN (VALUES …) → = ANY(array)

Как и любая трансформация дерева запроса, IN (VALUES …) превращается в = ANY(array) не всегда.

Трансформация не применяется, если внутри VALUES:

1. Есть волатильные функции.

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

   SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), (unique2));

3. Встречаются сложные типы, для которых нелегко корректно построить массив.

   SELECT * FROM onek
   WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))

4. Нет подходящего оператора равенства между типом колонки и типом массива.

5. Среди констант в списке значений встречается NULL.

   SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));

6. В выражении, где фигурирует IN (VALUES …), встречается LIMIT, OFFSET или ORDER BY. Такие конструкции могут влиять на порядок и количество строк, а преобразование в ANY(array) меняет модель выполнения. В этих ситуациях оптимизатору лучше работать с исходным деревом.

Простейшее x + 0 ломает индекс

Вторая функция pgpro_planner касается выражений вида x + 0, x * 1, x / 1 и x - 0. Очевидно, что все эти выражения равны x, но оптимизатор PostgreSQL так считать не обязан, а значит, может не использовать индекс.

Представим запрос:

EXPLAIN ANALYZE SELECT * FROM t WHERE x + 0 > 900;

Без дополнительной логики планировщик видит условие x + 0 > 900 и не понимает, что за ним скрывается простой диапазон по x. В результате строится Seq Scan по таблице t с фильтром на уровне строк, а индекс не используется. В плане это выглядит как последовательное сканирование с удалением сотен строк фильтром.

Seq Scan on t (rows=333 width=4) (rows=100.00 loops=1)
  Filter: ((x + 0) > 900)
  Rows Removed by Filter: 900

Если же переформулировать условие как x > 900, то можно построить Index Only Scan, который будет считывать только нужные данные из индекса без чтения самой таблицы.

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

Index Only Scan using idx_t_x on t (rows=100 width=4) (rows=100.00 loops=1)
  Index Cond: (x > 900)

Моя коллега Влада Поголежская добавила в pgpro_planner шаг упрощения тривиальных арифметических выражений ещё до того, как основной оптимизатор выберет план. Иногда это просто «распаковка» условий: если понять, что x + 0 эквивалентно x, можно переписать фильтр и позволить планировщику использовать индекс, как будто исходное выражение изначально было написано без «шумной» арифметики.

Ограничения

У этой оптимизации тоже есть ограничения:

1. Наличие volatile-функций, где повторное вычисление может дать разные значения.

2. Наличие сложных конструкций для возвращения 0 вроде x + 100 - 100, хотя человеку ясно, что результат — снова x: код такого упрощения получился бы непропорционально сложным по сравнению с выигрышем.

select id from data where a + 100 - 100 = b;

-----------------------------------
Seq Scan on data
  Filter: (((a + 100) - 100) = b)

Коррелированные подзапросы и Memoize: уходим от рутины

Третья функция pgpro_planner связана с коррелированными подзапросами и оператором Memoize. Сначала разберёмся, что такое коррелированные подзапросы.

Пример:

SELECT
    (
        SELECT SUM(s.x)
        FROM sub_tbl AS s
        WHERE s.x = m.x
    ) AS res
FROM main_tbl AS m
WHERE m.thousand BETWEEN 175 AND 177;

В плане это выглядит так: сначала выполняется Bitmap Heap Scan по main_tbl по диапазону m.thousand, потом для каждой строки из main_tbl запускается SubPlan, который делает Seq Scan по sub_tbl и агрегирует sum(s.x) по условию s.x = m.x.

Если значения m.x часто повторяются, подзапрос с одним и тем же параметром выполняется снова и снова. PostgreSQL без дополнительных оптимизаций честно каждый раз сканирует sub_tbl, даже если вчера и минуту назад уже вычислял sum(s.x) для такого же значения. Наблюдать это в планах больно.

По сути, наш подзапрос (SELECT sum(s.x) FROM sub_tbl s WHERE s.x=m.x — этот подзапрос) логически зависит от строки внешнего запроса (m.x — параметр), но фактически для разных строк получаются одни и те же параметры (s.x=m.x — условие, s.x уникально).

Memoize решает эту проблему через кеширование. В плане появляется узел Memoize с ключом m.x: для каждого нового значения m.x его результат сохраняется в хеш‑таблице, а при повторных обращениях с тем же m.x просто берётся результат подзапроса из кеша без его выполнения.

Ноду Memoize добавляем в pgpro_planner только там, где это действительно выгодно. Для этого проверяем несколько условий: 

• подзапрос должен быть коррелированным: если нет никаких зависимостей от основного запроса, нет смысла в кешировании;

• внутри не должно быть агрегатов min или max;

• кеш должен поддерживаться через подходящий hash‑оператор;

• стоимость плана с Memoize должна быть ниже стоимости варианта без кеширования.

В последнем условии мы оцениваем, сколько раз подзапрос будет вызван с разными параметрами и сколько стоит один такой вызов, а затем сравниваем это с затратами на создание и использование хеш‑таблицы. Если startup_cost + calls * per_call_cost без Memoize оказывается выше, чем стоимость с кэшированием, добавляем Memoize. Иначе подзапрос остаётся как есть.

Над этой функциональностью вместе со мной работали Андрей Лепихов и Пётр Петров.

Пример оптимизации кореллированных подзапросов в Join Order Benchmark

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

Запрос пытается найти фильмы, у которых даты в разных колонках (info и note) не совпадают. Мы пытаемся найти фильмы, для которых год, извлечённый из поля info, равен максимальному году из поля note таблицы movie_companies для того же фильма:

EXPLAIN ANALYZE
  SELECT t.title
    FROM movie_info mi
    JOIN title t ON 
      t.id = mi.movie_id
    WHERE substring(mi.info from '\d{4}')::int = (
      SELECT max(substring(mc.note from '\d{4}')::int)
      FROM movie_companies mc
      WHERE mc.movie_id = mi.movie_id);

Ключевой момент — выражение:

WHERE substring(mi.info from '\d{4}')::int = (
    SELECT max(substring(mc.note from '\d{4}')::int)
    FROM movie_companies mc
    WHERE mc.movie_id = mi.movie_id
)

Исходный план запроса (без pgpro_planner):

Nested Loop (actual time=9.859..496524.556 rows=685213 loops=1)
  ->  Seq Scan on movie_info mi
        (actual time=0.00..316888153.47? rows=685213 loops=1)
        Filter: ( (substring(info, '\d{4}'::text))::integer = (SubPlan 1) )
        Rows Removed by Filter: 14150507
        SubPlan 1
        ->  Aggregate
              (actual time=0.028..0.028 rows=1 loops=14835720)
            ->  Index Scan on movie_companies mc
                  (actual time=0.010..0.014 rows=5 loops=14835720)
                  Index Cond: (movie_id = mi.movie_id)
  ->  Index Scan using title_pkey on title t
        (actual time=0.011..0.011 rows=1 loops=685213)
        Index Cond: (id = mi.movie_id)

Planning Time: 1.693 ms
Execution Time: 496690.239 ms

mi.movie_id очень часто повторяется. Мы заново ищем одно и то же значение, то есть избыточно сканируем по индексу. Это видно по ключевому показателю: один и тот же подзапрос выполняется 14 835 720 раз. И это не баг, так работает обычный планировщик.

Nested Loop идёт по movie_info и для каждой строки «дёргает» индекс movie_companies, в итоге 14 миллионов повторов спустя получаем Execution Time: 496690 ms, а это полкилосекунды избыточной работы, почти 0.5 с только из-за того, что повторяющиеся вычисления не кешируются.

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

pgpro_planner вводит узел Memoize, который устраняет фундаментальную проблему повторных вычислений. Memoize — маленькая, но мощная оптимизация Postgres, которая сохраняет результаты выполнения подзапроса для каждого уникального параметра и возвращает их из кеша при повторении параметра. План запроса с pgpro_planner:

Nested Loop  (actual time=1.155..115353.599 rows=685213 loops=1)
  ->  Seq Scan on movie_info mi
        (actual time=1.086..106624.266 rows=685213 loops=1)
        Filter: ( (substring(info, '\d{4}'::text))::integer = (SubPlan 1) )
        SubPlan 1
        ->  Memoize
              (actual time=0.003..0.003 rows=1 loops=14835720)
              Cache Key: mi.movie_id  Cache Mode: binary
              Hits: 12366894  Misses: 2468826  Evictions: 2385405  Overflows: 0  Memory Usage: 8193kB
            ->  Aggregate
                  (actual time=0.014..0.014 rows=1 loops=2468826)
                ->  Index Scan on movie_companies mc
                      (actual time=0.009..0.011 rows=1 loops=2468826)
                      Index Cond: (movie_id = mi.movie_id)
  ->  Index Scan using title_pkey on title t
        (actual time=0.011..0.011 rows=1 loops=685213)
        Index Cond: (id = mi.movie_id)

Planning Time: 1.886 ms
Execution Time: 115528.841 ms

Memoize стоит перед подзапросом: ключ — mi.movie_id, значение — результат подзапроса (агрегат max(...)). Кеш хранится в небольшой хеш-таблице (8 MB в примере). При первом обращении к конкретному movie_id Memoize запускает SubPlan и сохраняет результат в кеше. При последующих обращениях с тем же ключом результат возвращается моментально, без повторного Index Scan.

Что мы получаем:

• Hits: 12 366 894 раз подзапрос не выполнялся.

• Misses: 2 468 826 раз система обращалась к индексу.

• Evictions: 2 385 405 значений вытеснились из-за ограничения размера кеша, но это не повлияло на итоговую корректность.

Благодаря Memoize итоговое время сократилось в четыре с лишним раза: с 496 690 ms до 115 528 ms.

Как включить pgpro_planner и не сломать прод

С точки зрения пользователя, pgpro_planner — обычное расширение для оптимизатора PostgreSQL. Поддерживается Postgres Pro Enterprise начиная с версии 16 и ванильным PostgreSQL тех же версий. 

Загрузить pgpro_planner можно двумя способами:

• Ввести команду LOAD pgpro_planner внутри сессии с 16-й версии и выше и включить расширение pgpro_planner.enable = true. В этом случае перезагрузка сервера не требуется: расширение подключается как планировочный хук и начинает перехватывать запросы в текущем соединении.

• Добавить pgpro_planner в shared_preload_libraries с перезагрузкой сервера и включить расширение pgpro_planner.enable = true.

А ещё расширение можно настроить гуками — включать и отключать функции внутри расширения: 

• enable_values_transformation — включает /отключает преобразование VALUES в ANY; 

• enable_simplify_trivials — упрощает тривиальные арифметические выражения;

• memoize_subplan — управляет кешированием подзапросов.

Такая настройка даёт гибкость: в продакшене вы можете держать включённой только ту часть функциональности, которая действительно нужна. Например, оставить Memoize для тяжёлых отчётных запросов, отключив преобразование VALUES, если автогенерация запросов их почти не использует. Или наоборот отключить упрощение тривиальных выражений, если вы специально вставляете их, чтобы принудительно получить Seq Scan при отладке.

Где pgpro_planner помогает, а где нет

Все три функции: преобразование IN (VALUES …), упрощение тривиальных выражений и Memoize — решают конкретные классы проблем, с которыми наша команда столкнулась в реальных запросах:

• В запросах из 1С‑подобных систем pgpro_planner убирает избыточные Nested Loop Semi Join и позволяет планировщику использовать индексы по знакомому виду = ANY(array). Это напрямую сокращает время выполнения тяжёлых отчётных запросов, особенно на таблицах с десятками миллионов строк.

• В автосгенерированных запросах с тривиальной арифметикой преобразование x + 0 и др. в x избавляет от визуального шума, возвращает возможность строить индексные планы и улучшает оценку кардинальности.

• Memoize даёт выигрыш там, где коррелированные подзапросы обращаются к одним и тем же значениям параметров. Вместо десятков одинаковых Seq Scan по подтаблице выполняется одна выборка и множество дешёвых обращений к кешу. pgpro_planner принимает решение на основе стоимости: если подзапрос вызывается один раз, смысла в Memoize нет.

При этом у всех трёх оптимизаций есть ограничения: волатильные функции, сложные типы, отсутствие подходящи�� операторов равенства, LIMIT и ORDER BY на пути к преобразуемым выражениям.

Если в ваших проектах есть запросы, которые «буксуют» на ровном месте, напишите о них в комментариях, и, возможно, наш планировщик разгадает секрет их неторопливости.

Статья собрана по мотивам доклада на конференции PGConf.СПб 2025.