Re: Common Table Expressions applied; some issues remain

2009/5/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.
>

And 4. only if the sub-selects use index scan? Or in other cases would
it be effective?

Regards,

--
Hitoshi Harada