Игра в прятки с оптимизатором. Гейм овер, это CTE PostgreSQL 12

Эта статья — продолжение рассказа о новом в PostgreSQL 12. Мы уже разобрали SQL/JSON (патч JSONPath) в статье «Что заморозили на feature freeze 2019. Часть I. JSONPath», теперь очередь CTE.

CTE

CTE это Common Table Expression — общие табличные выражения, их еще называют конструкциями с WITH. Фактически это создание временных таблиц, но существующих только для одного запроса, а не для сессии. К ним можно обращаться внутри этого запроса. Такой запрос хорошо читается, он понятен, его легко видоизменять, если потребуется. Это очень востребованная вещь, и она в PostgreSQL давно.

Но удобства могут обойтись дорого. Проблемы связаны с материализацией выражения после AS внутри конструкции WITH… AS (). Его еще называют внутренним выражением и вычисляют перед тем, как начать вычисление остального, его нельзя встроить в запрос верхнего уровня (no inlining). Планирование этого выражения происходит без учета остальной части запроса. Такое поведение называют барьером для оптимизации, или fencing. Кроме того, сама материализация требует под себя work_mem. И если выборка большая, то начинаются проблемы (об этом, например, есть в докладе Ивана Фролкова на PGConf 2019).

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

Будем наблюдать работу CTE на такой табличке:

CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x;
CREATE INDEX ON xytable(x,y);

Table "public.xytable"
Column | Type      | Collation | Nullable | Default
-----------+-----------+-------------+-----------+---------
x             | integer |                  |                |
y             | integer |                  |                |

Indexes:
"xytable_x_y_idx" btree (x, y)

Начнем с простенького запроса:

SELECT * FROM xytable WHERE x=2 AND y>1;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using xytable_x_y_idx on xytable  (cost=0.43..8.46 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)
      Index Cond: ((x = 2) AND (y > 1))
      Heap Fetches: 1
 Planning Time: 0.075 ms
 Execution Time: 0.035 ms
(5 rows)

Всё считается моментально, используется только индекс.

Запрос с подзапросом, вычисляющий то же самое, но с синтаксисом чуть посложней:

SELECT * FROM
    (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using xytable_x_y_idx on xytable  (cost=0.43..8.46 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)
   Index Cond: ((x = 2) AND (y > 1))
   Heap Fetches: 1
 Planning Time: 0.062 ms
 Execution Time: 0.029 ms
(5 rows)

Всё в порядке, очень быстрое вычисление по индексу.

А теперь еще один логически эквивалентный запрос, но уже с CTE:

WITH yy AS (
     SELECT * FROM xytable WHERE y>1)
SELECT * FROM yy WHERE x=2;
QUERY PLAN
------------------------------------------
CTE Scan on yy (actual time=0.099..3672.842 rows=1 loops=1)
   Filter: (x = 2)
   Rows Removed by Filter: 9999998
   CTE yy
      -> Seq Scan on cte (actual time=0.097..1355.367 rows=9999999 loops=1)
          Filter: (y > 1)
          Rows Removed by Filter: 1
 Planning Time: 0.088 ms
 Execution Time: 3735.986 ms
(9 rows)

Такая задержка видна уже и невооруженным глазом. Кофе не попьешь, но в почту заглянуть времени хватит (когда у нас 11-я версия или более ранняя).

А произошло вот что: в случае с подзапросами оптимизатор сразу сообразил, что условия x=2 и y>1 можно объединить в один фильтр и искать по индексу. В случае CTE у оптимизатора нет выбора: он должен сначала разобраться с условием внутри конструкции WITH… AS, материализовать результат и только после этого работать дальше.

И здесь дело не в том, что материализация потребует ресурсов: если условие будет y<3, то материализовать придется не миллионы записей а всего 2. Чудовищное для несложного запроса время тратится на последовательный поиск, оптимизатор не может использовать поиск по индексу из-за того, что составной индекс строится по x, а уже потом по y, а о запросе с условием x=2 он ничего не узнает, пока не выполнит внутренне условие CTE. Оно — за барьером.

Итак, до версии PostgreSQL 12 по умолчанию была материализация, теперь — ее отсутствие. Запускаем тот же запрос на базе новой версии. Барьера как не бывало, оптимизатор сразу видит весь запрос:

WITH yy AS (
     SELECT * FROM xytable WHERE y>1)
SELECT * FROM yy
WHERE x=2;

QUERY PLAN
------------------------------------------
Index Only Scan using xytable_x_y_idx1 on xytable  (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
       Index Cond: ((x = 2) AND (y > 1))
       Heap Fetches: 1
 Planning Time: 0.067 ms
 Execution Time: 0.029 ms
(5 rows)

Оптимизатор моментально научился объединять условия в оптимальном порядке — как это было с подзапросами.

Но умолчания умолчаниями, а для полного владения ситуацией теперь, в версии 12 имеется контролируемая, управляемая материализация CTE:

WITH cte_name
AS [NOT] MATERIALIZED

Заставим материализоваться:

EXPLAIN ANALYZE  WITH yy AS MATERIALIZED (
     SELECT * FROM xytable WHERE y>1)
SELECT * FROM yy WHERE x=2;

QUERY PLAN
---------------------------
 CTE Scan on yy  (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1)
   Filter: (x = 2)
   Rows Removed by Filter: 9999998
   CTE yy
     ->  Bitmap Heap Scan on cte  (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1)
           Recheck Cond: (y > 1)
           Heap Blocks: exact=44248
           ->  Bitmap Index Scan on xytable_x_y_idx1  (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1)
                 Index Cond: (y > 1)
 Planning Time: 0.086 ms
 Execution Time: 3612.840 ms
(11 rows)

Всё как в 11 и до нее, можно глянуть почту в режиме ожидания результатов запроса. Запрещаем материализацию, убираем барьер:

EXPLAIN ANALYZE  WITH yy AS MATERIALIZED (
     SELECT * FROM xytable WHERE y>1)
SELECT * FROM yy WHERE x=2;QUERY PLAN
---------------------------
 Index Only Scan using xytable_x_y_idx1 on xytable  (cost=0.43..8.46 rows=1 width=8) (actual time=0.070..0.072 rows=1 loops=1)
   Index Cond: ((x = 2) AND (y > 1))
   Heap Fetches: 1
 Planning Time: 0.182 ms
 Execution Time: 0.108 ms
(5 rows)

Опять никакой передышки: считается моментально.

<!--
WITH yy AS (
SELECT * FROM cte
WHERE y=2
)
SELECT * FROM yy
WHERE x=2;

explain analyze SELECT * FROM
    (SELECT * FROM cte WHERE y>1) AS t WHERE x=2;

 Index Only Scan using cte_x_y_idx1 on cte  (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
   Index Cond: ((x = 2) AND (y > 1))
   Heap Fetches: 1
 Planning Time: 0.065 ms
 Execution Time: 0.029 ms
(5 rows)

explain analyze WITH yy AS (SELECT * FROM cte WHERE y>1)SELECT * FROM yy WHERE x=2;

 Index Only Scan using cte_x_y_idx1 on cte  (cost=0.43..8.46 rows=1 width=8) (actual time=0.019..0.021 rows=1 loops=1)
   Index Cond: ((x = 2) AND (y > 1))
   Heap Fetches: 1
 Planning Time: 0.094 ms
 Execution Time: 0.038 ms
(5 rows)

 Index Only Scan using cte_x_y_idx1 on cte  (cost=0.43..8.46 rows=1 width=8) (actual time=0.044..0.046 rows=1 loops=1)
   Index Cond: ((x = 2) AND (y = 2))
   Heap Fetches: 1
 Planning Time: 0.223 ms
 Execution Time: 0.089 ms
(5 rows)

explain analyze WITH yy AS MATERIALIZED (
     SELECT * FROM cte WHERE y=2)
SELECT * FROM yy WHERE x=2;

 CTE Scan on yy  (cost=97331.43..97331.46 rows=1 width=8) (actual time=0.140..1106.887 rows=1 loops=1)
   Filter: (x = 2)
   CTE yy
     ->  Gather  (cost=1000.00..97331.43 rows=1 width=8) (actual time=0.138..1107.260 rows=1 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           ->  Parallel Seq Scan on cte  (cost=0.00..96331.33 rows=1 width=8) (actual time=735.870..1104.785 rows=0 loops=3)
                 Filter: (y = 2)
                 Rows Removed by Filter: 3333333
 Planning Time: 0.068 ms
 Execution Time: 1107.282 ms
(11 rows)
= = = = -->

Остались нюансы. Но важные нюансы.

CTE материализуется по умолчанию, если к ней обращаются более одного раза.

На первый взгляд материализация в таких случаях разумное решение: зачем вычислять одно и то же дважды. На практике это часто приводит к тому, что мы наблюдали выше. Чтобы заставить отказаться от материализации, надо явно приказать оптимизатору: NOT MATERIALIZED.

Исполняем без NOT MATERIALIZED запрос с двойным WHERE:

WITH yy AS ( 
     SELECT * FROM xytable WHERE y > 1)
SELECT (
     SELECT count(*) FROM yy WHERE x=2), (
     SELECT count(*) FROM yy WHERE x=2);

QUERY PLAN
---------------------------------------------------------------------------
Result (actual time=3922.274..3922.275 rows=1 loops=1)
   CTE yy
      -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1)
          Filter: (y > 1)
          Rows Removed by Filter: 1
   InitPlan 2 (returns $1)
      -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1)
          -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1)
              Filter: (x = 2)
              Rows Removed by Filter: 9999998
   InitPlan 3 (returns $2)
      -> Aggregate (actual time=812.580..812.580 rows=1 loops=1)
          -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1)
               Filter: (x = 2)
               Rows Removed by Filter: 9999998
   Planning Time: 0.136 ms
   Execution Time: 3939.848 ms
(17 rows)

А теперь явно пропишем запрет на материализацию:

WITH yy AS NOT MATERIALIZED (
     SELECT * FROM xytable WHERE y > 1)
SELECT (
     SELECT count(*) FROM yy WHERE x=2), (
     SELECT count(*) FROM yy WHERE x=2);

QUERY PLAN
---------------------------------------------------------------------------
Result (actual time=0.035..0.035 rows=1 loops=1)
   InitPlan 1 (returns $0)
     -> Aggregate (actual time=0.024..0.024 rows=1 loops=1)
         -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1)
             Index Cond: ((x = 2) AND (y > 1))
             Heap Fetches: 1
   InitPlan 2 (returns $1)
     -> Aggregate (actual time=0.006..0.006 rows=1 loops=1)
        -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1)
            Index Cond: ((x = 2) AND (y > 1))
            Heap Fetches: 1
   Planning Time: 0.253 ms
   Execution Time: 0.075 ms
(13 rows)

пишущие CTE исполняются всегда, а CTE, на которые нет ссылок — никогда.

Это видно из плана: not_executed в нем нет. Это верно и для предыдущих версий, но об этом стоит помнить, и к исполняемому выражению в версии 12 применима конструкция (NOT) MATERIALIZED.

EXPLAIN (COSTS OFF) WITH yy AS (
     SELECT * FROM xytable WHERE y > 1),
not_executed AS (
     SELECT * FROM xytable),
always_executed AS (
     INSERT INTO xytable VALUES(2,2) RETURNING *)
SELECT FROM yy WHERE x=2;

QUERY PLAN
-----------------------------
CTE Scan on yy
   Filter: (x = 2)
   CTE yy
      -> Seq Scan on cte
            Filter: (y > 1)
   CTE always_executed
      -> Insert on cte cte_1
            -> Result
(5 rows)

И еще одно правило:

рекурсивные запросы с WITH материализуются всегда.

Именно всегда, а не по умолчанию. Если мы прикажем оптимизатору: NOT MATERIALIZED, ошибки не будет, а материализация все равно будет. Это сознательное, обсуждавшееся сообществом решение.

Будем считать иллюстрирующий пример домашним заданием. На этом на сегодня всё.

В этой части обзора, посвященной новому в CTE, используются примеры и фрагменты из доклада «Postgres 12 в этюдах», который прочитал Олег Бартунов на Saint Highload++ в СПБ 9 апреля сего года.

В следующей серии — KNN.