>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I also thought about that. But what I thought about it on reflection
>> was: if the user explicitly wrote NOT MATERIALIZED, then we should
>> assume they mean it.
Tom> Ah, but the example I gave also had MATERIALIZED on the inner WITH.
Tom> Why should the user not also mean that?
The inner WITH does get materialized, it just gets materialized twice.
If the user doesn't want that, then they can avoid using NOT MATERIALIZED
on the outer CTE; but if we force it to materialize the outer query,
then that leaves the user without recourse.
Consider a case like:
create view foo as
with s as materialized (select something)
select * from large l
where l.foo in (select * from s) or l.bar in (select * from s);
with
bar as not materialized (select * from foo)
select * from bar b1, bar b2 where b1.col='x' and b2.col='y';
In a case like this, materializing "s" twice may be far less expensive
than materializing the result of "select * from large..." without
benefit of pushed-down quals.
--
Andrew (irc:RhodiumToad)