On 1/18/19 9:34 PM, Robert Haas wrote:
> On Thu, Jan 17, 2019 at 10:48 AM Andreas Karlsson <andreas@proxel.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
