СTE, подзапрос или представление?

[Akina]

CTE могут материализоваться в памяти в ходе выполнения запроса

Существует две принципиально разные стратегии выполнения CTE - инлайн и материализация.

Какую стратегию реализует PostgreSQL? или, если реализованы обе, как выполняется установка стратегии для текущего запроса либо соединения? Ваши слова неоднозначны, "могут материализоваться" - не означает "безусловно материализуются", материализация точно реализована, но реализован ли инлайн? доступен ли он вообще, и на каком уровне?

Аналогичный вопрос - по нематериализованным представлениям.

Синтаксис, показанный вами для CTE, весьма неаккуратен, и скорее должен быть отнесён к bad practice. Имена выходных полей крайне желательно указывать в заголовке CTE, а не полагаться на трансляцию имён из подзапроса.

Синтаксис подзапросов неполон, пропущен наиболее частый вариант - подзапрос в секции FROM.

И не упомянут весьма существенный минус временных таблиц - их утрата при работе в условиях автопереключения при использовании пула соединений, а также при наличии автовосстановления соединения при его разрыве.

[KainoRhine]

В зависимости от плана выполнения. Например в запросе

explain  
with cte as (
	select "Name"  from Example tc 
)
select * from cte

cte используется только 1 раз. То есть повторного выполнения нет, а значит нет необходимости материализовывать результат. Теперь смотрим план выполнения

with cte as (
	select "Name"  from Example tc 
)
select * from cte

И видим что шаг Materialize отсутствует

Теперь давайте напишем запрос который будет повторно использовать результаты cte

with cte as (
	select "Name"  from Example tc 
)
select * from cte
left join cte as c on  c."Name" = cte."Name"

здесь мы добавили простой join с результатами cte

Теперь давайте посмотрим на план выполнения добавив перед запросом explain

explain  
with cte as (
	select "Name"  from Example tc 
)
select * from cte
left join cte as c on  c."Name" = cte."Name"

И в плане выполнения видим:

Появился шаг Materialize.

Таким образом, когда мы повторно используем CTE, результаты будут материализованы

Я ответил на ваш вопрос?

C подзапросами в секции FROM расскажу отдельно и про временные таблицы тоже (тут очень много подводных камней с которыми я сталкивался когда то )

[Akina]

Нет. Вы его, похоже, даже не поняли.

WITH
cte1 (value1) AS (SELECT random()),
cte2 (value2) AS (SELECT value1 FROM cte1)
SELECT value1, value2
FROM cte1 CROSS JOIN cte2;

Materialized strategy вернёт равные значения полей, inline strategy - разные.

На практике иногда нужна одна стратегия, иногда другая. Реализованы ли обе стратегии, и если да, то как указать нужную?

[shurutov]

Ключевое свойство запросов WITH состоит в том, что они обычно вычисляются один раз для всего основного запроса, даже если в основном запросе содержатся несколько ссылок на них. В частности, гарантируется, что операторы, изменяющие данные, будут выполняться ровно один раз, вне зависимости от того, будет ли их результат прочитан основным запросом и в каком объёме.

Однако от этой гарантии можно отказаться, добавив для запроса WITH пометку NOT MATERIALIZED. В этом случае запрос WITH может быть свёрнут в основной запрос, как если бы это был простой SELECT внутри предложения FROM основного запроса.

Документация по ПГ: https://postgrespro.ru/docs/postgresql/12/sql-select#SQL-WITH

[Akina]

Вот опять...

.. запрос WITH может быть свёрнут ..

"Может" - это не "будет". Нередки случаи, когда инлайн подзапроса производит гораздо более вменяемый план выполнения и разницу по времени выполнения на порядок. А тут - ты укажи NOT MATERIALIZED, и молись...

Документация по ПГ

Постгресс и ПостгрессПро - это совсем не одно и то же.

[shurutov]

Я привёл ссылку на перевод официальной документации. У каждого ПгПро (стандарт/энтерпрайз) своя документация.

[KainoRhine]

в Greenplum( у меня PostgreSQL 9.4.26 ) я нашел решение либо через временные таблицы либо через материализованные представления, так как инструкции MATERIALIZED и NOT MATERIALIZED для CTE появились в 12+ версии.

Так же они есть в arenadata https://docs.arenadata.io/en/ADPG/current/how-to/queries/common-table-expressions.html

[KainoRhine]

Спасибо за комментарий, с такими задачами не сталкивался, но было интересно пощупать планы и разные стратегии выполнения CTE. Обычно сталкивался с задачами где они должны возвращать одинаковые значения.

[erogov]

А планы, внезапно, от Greenplum-а.

[0xMihalich]

ну в целом GreenPlum и есть Postgres, только очень очень старый, но зато иногда еще и умеющий быть колоночной бд