Игра в прятки с оптимизатором. Гейм овер, это 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)
Опять никакой передышки: считается моментально.
Остались нюансы. Но важные нюансы.
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.
