Re: Early WIP/PoC for inlining CTEs

On 22/01/2019 02:40, Andreas Karlsson wrote:
> 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
>
+1

For putting the 'AS' earlier, 2nd option,  I think it reads better.

Cheers,
Gavin