Игра в прятки с оптимизатором. Гейм овер, это 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 NOT 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)

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

    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.
    Postgres Professional
    136,85
    Разработчик СУБД Postgres Pro
    Поделиться публикацией

    Комментарии 11

      0
      Запрещаем материализацию, убираем барьер:
      EXPLAIN ANALYZE WITH yy AS MATERIALIZED

      NOT пропущен?
        0
        да, конечно! спасибо, поправил.
        0
        Не нравится, что императивщина прорвалась в SQL (декларативный язык). Всё же решение о материализации должен принимать оптимизатор, а не разработчик. Иначе получается тот же самый хинт, только с приятным синтаксисом
          +3
          С неприятным синтаксисом. Хинты должны быть (я не перестаю удивляться PG-разработчикам; даже в IBM давно передумали, хотя первоначально они занимали подобную позицию), причём они должны быть оформлены как комментарии. А эта штука буквально вынудит почти везде писать NOT MATERIALIZED, что и лишняя писанина, и неудобства при миграции (и с, и на).
            +1
            Гм… непонятно, «пишущие CTE исполняются всегда» относится к конкретному CTE, или ко всем CTE, когда их несколько? Иными словами, нормально работающий в 11-м
            WITH cte1 AS (сложный запрос на основе нескольких копий table),
                 cte2 AS (DELETE FROM table)
            INSERT INTO table SELECT * FROM cte1

            не приведёт ли к проблемам, если cte1 заявить как NOT MATERIALIZED?
              +1
              Во всяком случае у
              CREATE TABLE akina_table AS SELECT x, x AS y FROM generate_series(1,10) AS x;
              EXPLAIN ANALYZE WITH
                   cte1 AS (NOT MATERIALIZED) (SELECT * FROM akina_table WHERE x<2),
                   cte2 AS (DELETE FROM akina_table WHERE y>9)
              INSERT INTO akina_table SELECT * FROM cte1;
              план одинаковый с и без NOT MATERIALIZED. Я ответил?
                0
                план одинаковый с и без NOT MATERIALIZED. Я ответил?

                Вопрос был как бы не о плане. Если мы заявляем CTE1 как нематериализуемый (т.е. пусть будет кэшируемый), но при этом объём выборки великоват, чтобы поместиться в имеющуюся оперативку, то в случае без материализации его результата CTE2 почистит таблицу, и на момент выполнения основного запроса в исходной таблице просто не будет существовать данных для воспроизведения результата CTE1. Т.е. для корректного выполнения всего запроса сервер просто обязан будет начхать на наш хинт и материализовать результат CTE1. Или это будет игра «раз снапшот, два снапшот»?
                0
                Идея хорошая, но нет — работает так же, как на 11.
                  0

                  А как он заранее узнает, запрос пишущий или нет? Ведь запись может буть глубоко во вложенных функциях, которые вызываются через обычный SELECT в CTE.

                  0
                  Igor_Le Простите, пожалуйста
                  1) Для
                  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);

                  верным будет что:
                  движок дважды обращается к индексу xytable_x_y_idx, но второе обращение заметно быстрее из-за попадания в кэш?
                  2) Здесь противопоставлялась явная материалиция cte и «встраивание» запроса cte в каждое использование «по месту», если я правильно понимаю.
                  А в чем проблема с выделением множества пересекающихся условий из выборок по cte, и переноса данных условий на этап материализации cte?
                    +1
                    Проблема именно в «барьере» для оптимизатора. Дело не в том, попало что-то в кэш или нет: это все равно быстро, так как по индексу. В случае, когда материализация НЕ запрещена, поиск секскан, оптимизатор не может воспользоваться индексом. Он НЕ МОЖЕТ объединить условия, так как одно «внутри», а другое «снаружи». Он (по существующему дизайну) обязан сначала исполнить (точнее, исполняет, конечно, экзекьютер) внутренне, подлежащее материализации, а о внешнем он «узнает» только после этого.

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое