Re: Early WIP/PoC for inlining CTEs

On 1/18/19 9:34 PM, Robert Haas wrote:
> On Thu, Jan 17, 2019 at 10:48 AM Andreas Karlsson <andreas(at)proxel(dot)se> wrote:
>> On 1/11/19 8:10 PM, Robert Haas wrote:
>>> WITH cte_name [[NOT] MATERIALIZED] AS (query) main_query...
>>
>> Hm, when would one want "NOT MATERIALIZED"? I am not sure I see the
>> usefulness of forcing inlining other than if we by default do not inline
>> when a CTE is referenced multiple times.
>
> When the planner materializes it, but the performance of the resulting
> plan therefore sucks, I suppose.
>
> I don't feel super-strongly about this, and Tom is right that there
> may be cases where materialization is just not practical due to
> implementation restrictions. But it's not crazy to imagine that
> inlining a multiply-referenced CTE might create opportunities for
> optimization at each of those places, perhaps not the same ones in
> each case, whereas materializing it results in doing extra work.

I see.

I have a minor biksheddish question about the syntax.

You proposed:

WITH cte_name [[NOT] MATERIALIZED] AS (query) main_query

While Andrew proposed:

WITH cte_name AS [[NOT] MATERIALIZED] (query) main_query

Do people have any preference between these two?

Andreas