About a year ago I was briefly in discussion/collaboration with Adam Sah
regarding the topic of inlining CTEs into the query rather than treating
them as optimization barriers. We didn't take it very far (he sent me
some stuff, I wrote some stuff and sent it back, things kind of got
dropped at that point); but there's been some recent discussion of this
and some people have expressed an interest in seeing the code.
So I'm posting the parts that I wrote for the benefit of anyone wanting
to pick up the issue again. The assumption of this code is that some
form of syntax would exist to mark materialized CTEs and set the
"ctematerialized" flag.
I haven't rebased this or tested it since last year; this patch is
against b81eba6a65.
Posted for discussion, further development, criticism, whatever; feel
free to include this (with credit) in any relevant patch. Consider this
released under the PG license.
--
Andrew (irc:RhodiumToad)
In our environment we often want this to be a fence. For example it can be used to only have smaller numbers of joins in each cte and not hit the join collapse limit, or when we really know more about the subquery than the optimizer and have something really specific there . So in general I would not want the default functionality to change all of the queries we have already written with this in mind. I do however like the idea of this feature being an option, but I would question whether it perhaps worked the other way around where you have to mark a CTE as not being a fence.
Curious what other RDBMSs do here?
Thanks,
Jeremy
