Skip site navigation (1) Skip section navigation (2)

Re: Common Table Expressions applied; some issues remain

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <stark(at)enterprisedb(dot)com>, Yoshiyuki Asaba <y-asaba(at)sraoss(dot)co(dot)jp>, Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Common Table Expressions applied; some issues remain
Date: 2009-05-27 03:29:58
Message-ID: e08cc0400905262029x2e08051fvc622500939a95255@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-05-27 03:36:34
Subject: Re: Common Table Expressions applied; some issues remain
Previous:From: Robert HaasDate: 2009-05-27 03:21:06
Subject: Re: commitfest management webapp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group