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

[kxl]

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

NOT пропущен?

[Igor_Le]

да, конечно! спасибо, поправил.

[vovik0134]

Не нравится, что императивщина прорвалась в SQL (декларативный язык). Всё же решение о материализации должен принимать оптимизатор, а не разработчик. Иначе получается тот же самый хинт, только с приятным синтаксисом

[Akina]

Гм… непонятно, «пишущие CTE исполняются всегда» относится к конкретному CTE, или ко всем CTE, когда их несколько? Иными словами, нормально работающий в 11-м

WITH cte1 AS (сложный запрос на основе нескольких копий table),
     cte2 AS (DELETE FROM table)
INSERT INTO table SELECT * FROM cte1

не приведёт ли к проблемам, если cte1 заявить как NOT MATERIALIZED?

[Igor_Le]

Во всяком случае у

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. Я ответил?

[Akina]

план одинаковый с и без NOT MATERIALIZED. Я ответил?

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

[x-wao]

Идея хорошая, но нет — работает так же, как на 11.

[DmitryKoterov]

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

[nightwolf_du]

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?

[Igor_Le]

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

[Yellow_ki]

Подскажите, актуальны ли особенности, описанные в статье, для Greenplum?