Хабрахабр

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

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

CTE

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

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

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

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

Запускаем тот же запрос на базе новой версии. Итак, до версии 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)

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

<!--
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.

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть