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 <andr...@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
+1
For putting the 'AS' earlier, 2nd option, I think it reads better.
Cheers,
Gavin
