Re: Common Table Expressions applied; some issues remain

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <stark(at)enterprisedb(dot)com>
Cc: 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-26 23:47:45
Message-ID: 21843.1243381665@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2009-05-27 00:30:04 Re: generic options for explain
Previous Message Caleb Welton 2009-05-26 23:07:33 [PATCH] plpythonu datatype conversion improvements