Открыть список
Как стать автором
Обновить
145.33
Рейтинг
Postgres Professional
Разработчик СУБД Postgres Pro

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

Блог компании Postgres ProfessionalPostgreSQL


Эта статья — продолжение рассказа о новом в 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.
Теги:postgresqlpostgresdbmsсубдбазы данных
Хабы: Блог компании Postgres Professional PostgreSQL
Всего голосов 29: ↑29 и ↓0 +29
Просмотры12.6K

Похожие публикации

Лучшие публикации за сутки

Информация

Дата основания
Местоположение
Россия
Сайт
www.postgrespro.ru
Численность
51–100 человек
Дата регистрации
Представитель
Иван Панченко

Блог на Хабре