Re: Common Table Expressions applied; some issues remain

Greg Stark <stark(at)enterprisedb(dot)com> writes:
> [ point 1 here remains unresolved:
> http://archives.postgresql.org/message-id/9623.1223158943@sss.pgh.pa.us ]

> One possibility would be to not flatten the query but find these quals
> and copy them onto the cte when planning the cte. So we would still
> materialize the result and avoid duplicate execution but only fetch
> the records which we know a caller will need. We could even do that
> for multiple callers if we join their quals with an OR -- that still
> might allow a bitmap index scan.

I'm not too thrilled about that solution because it still eliminates
predictability of execution of volatile functions. It's really just a
partial form of subquery pullup, so we're paying all the disadvantages
for only a subset of the advantages.

I could still see doing what I mentioned in the prior message, which is
to flatten CTEs as if they are plain sub-selects when

1. they are non-recursive,
2. they are referenced only once, and
3. they contain no volatile functions.

Restriction #3 is what we need to ensure we aren't causing visible
semantics changes. You could argue #2 either way, I guess, but my
feeling is that if someone is using a doubly referenced CTE then he's
probably doing something more complex than we are currently prepared
to optimize well. I think we should let that case go until we
understand typical usage and possible optimizations better.

regards, tom lane