Common Table Expressions applied; some issues remain

I've applied the latest version of the CTE patch. Congratulations on
making that happen!

There are still some loose ends that need to be considered, though.

1. As committed, the patch takes an extremely hard line about WITH
queries being evaluated independently of the main query and only once
per main query execution. This could be seen as a good thing --- it
provides much more determinism for execution of volatile functions
within complex queries than was really available in the past. It could
also be seen as a bad thing --- in particular, we won't push any
limiting qualifications from the main query into the WITH queries.
So for instance

WITH q AS ( SELECT * FROM foo )
SELECT * FROM q WHERE key = 42;

is going to be executed quite inefficiently; it won't use an index on
foo.key. I think we don't have much choice about this in the case of
recursive WITH queries: it would be pretty difficult to determine
whether pushing a restriction into a recursive WITH would change the
results incorrectly. However, for plain non-recursive WITHs it's all
a matter of definition. I gather from
http://www.oracle-developer.net/display.php?id=212
that Oracle chooses to treat WITH-queries as if they were plain
sub-selects if they're non-recursive and only referenced once.
That is, Oracle would rewrite the above into

SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;

and then flatten the sub-select and optimize normally. It would
not be hard to make Postgres do the same, but then we would lose
some guarantees about predictable execution of volatile functions.

I'm inclined to think that there is no reason to provide two
different syntaxes to do the same thing, and so having the WITH
syntax behave like this is okay. But it could well result in
performance surprises for people who are used to Oracle.

Any thoughts on what to do? One possibility is to flatten only
if the subquery doesn't contain any volatile functions.

2. The patch didn't touch the implicit-RTE code, which means that

WITH q AS ( SELECT ... )
SELECT q.*

will fail even if you've got add_missing_from enabled. I'm inclined
to think that this violates the principle of least surprise. On
the other hand, add_missing_from is certainly a legacy thing and maybe
we shouldn't bother expending any extra code to make it work with
new features. Thoughts?

3. ruleutils.c's get_name_for_var_field() hasn't implemented the
RTE_CTE case, which means that it doesn't work to reverse-list
examples like this:

explain verbose with qq as (select x from (values(1,2),(3,4)) as x(c1,c2))
select * from (select (x).c2 from qq offset 0) ss;

The reason I let this go is that while poking into it I found out that
get_name_for_var_field is pretty broken already; this fails in HEAD:

explain verbose select (x).c2 from
(select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss ;

and this fails even in the back branches:

explain select * from
(select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss
where (x).c2 > 0;

It seems we need some redesign in and around EXPLAIN to make that work
nicely, so I figured it would be reasonable to tackle that stuff as a
separate patch.

regards, tom lane

On Sat, Oct 4, 2008 at 5:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> that Oracle chooses to treat WITH-queries as if they were plain
> sub-selects if they're non-recursive and only referenced once.
> That is, Oracle would rewrite the above into
>
> SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>
> and then flatten the sub-select and optimize normally. It would
> not be hard to make Postgres do the same, but then we would lose
> some guarantees about predictable execution of volatile functions.
>
[...]
>
> Any thoughts on what to do? One possibility is to flatten only
> if the subquery doesn't contain any volatile functions.
>

maybe i'm missing something but AFAIR postgres will not try to
optimize (push down/pull up) if it see any volatile function.

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

> I've applied the latest version of the CTE patch. Congratulations on
> making that happen!

Great! and thanks, Tom. Without your great help, we cannot make it
reality. I also would like to thank to everyone who helped this
project!

> There are still some loose ends that need to be considered, though.

I think in addition to them, we need to update ecpg.

> 1. As committed, the patch takes an extremely hard line about WITH
> queries being evaluated independently of the main query and only once
> per main query execution. This could be seen as a good thing --- it
> provides much more determinism for execution of volatile functions
> within complex queries than was really available in the past. It could
> also be seen as a bad thing --- in particular, we won't push any
> limiting qualifications from the main query into the WITH queries.
> So for instance
>
> WITH q AS ( SELECT * FROM foo )
> SELECT * FROM q WHERE key = 42;
>
> is going to be executed quite inefficiently; it won't use an index on
> foo.key. I think we don't have much choice about this in the case of
> recursive WITH queries: it would be pretty difficult to determine
> whether pushing a restriction into a recursive WITH would change the
> results incorrectly. However, for plain non-recursive WITHs it's all
> a matter of definition. I gather from
> http://www.oracle-developer.net/display.php?id=212
> that Oracle chooses to treat WITH-queries as if they were plain
> sub-selects if they're non-recursive and only referenced once.
> That is, Oracle would rewrite the above into
>
> SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>
> and then flatten the sub-select and optimize normally. It would
> not be hard to make Postgres do the same, but then we would lose
> some guarantees about predictable execution of volatile functions.
>
> I'm inclined to think that there is no reason to provide two
> different syntaxes to do the same thing, and so having the WITH
> syntax behave like this is okay. But it could well result in
> performance surprises for people who are used to Oracle.
>
> Any thoughts on what to do? One possibility is to flatten only
> if the subquery doesn't contain any volatile functions.
>
>
> 2. The patch didn't touch the implicit-RTE code, which means that
>
> WITH q AS ( SELECT ... )
> SELECT q.*
>
> will fail even if you've got add_missing_from enabled. I'm inclined
> to think that this violates the principle of least surprise. On
> the other hand, add_missing_from is certainly a legacy thing and maybe
> we shouldn't bother expending any extra code to make it work with
> new features. Thoughts?
>
>
> 3. ruleutils.c's get_name_for_var_field() hasn't implemented the
> RTE_CTE case, which means that it doesn't work to reverse-list
> examples like this:
>
> explain verbose with qq as (select x from (values(1,2),(3,4)) as x(c1,c2))
> select * from (select (x).c2 from qq offset 0) ss;
>
> The reason I let this go is that while poking into it I found out that
> get_name_for_var_field is pretty broken already; this fails in HEAD:
>
> explain verbose select (x).c2 from
> (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss ;
>
> and this fails even in the back branches:
>
> explain select * from
> (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss
> where (x).c2 > 0;
>
> It seems we need some redesign in and around EXPLAIN to make that work
> nicely, so I figured it would be reasonable to tackle that stuff as a
> separate patch.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tom Lane wrote:
> I've applied the latest version of the CTE patch. Congratulations on
> making that happen!

This is great news. A big thanks to all the people involved in making
this happen.

I've had several people come up to me during the conference that I've
been at that just ended asking if I thought this would make it into 8.4.
It's certainly a much asked-for feature!

//Magnus

Tom Lane wrote:
> that Oracle chooses to treat WITH-queries as if they were plain
> sub-selects if they're non-recursive and only referenced once.
> That is, Oracle would rewrite the above into
>
> SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>
> and then flatten the sub-select and optimize normally. It would
> not be hard to make Postgres do the same, but then we would lose
> some guarantees about predictable execution of volatile functions.
>
> I'm inclined to think that there is no reason to provide two
> different syntaxes to do the same thing, and so having the WITH
> syntax behave like this is okay. But it could well result in
> performance surprises for people who are used to Oracle.
>
> Any thoughts on what to do? One possibility is to flatten only
> if the subquery doesn't contain any volatile functions.

I don't think we should overload syntax choices with optimization hints.
We don't really know why or how people will be using this syntax, and
labeling it from the start as "will have unusual performance behavior"
isn't a good sell.

As a precedent, consider the JOIN syntax, which is obviously redundant
and in its first implementation contained an implicit optimization hint
with regard to join order that later had to be done away with because it
confused users (I think). The CTE case is quite similar, and maybe the
GUC answer of old could apply here as well. But I think by default we
should abide by SQL's declarative approach of "Tell me what you want and
I'll execute it any way I like."

Also, why is predictability about volatile function executation a
requirement? Is there some typical use case that involves sequences
functions here or something?

> 2. The patch didn't touch the implicit-RTE code, which means that
>
> WITH q AS ( SELECT ... )
> SELECT q.*
>
> will fail even if you've got add_missing_from enabled. I'm inclined
> to think that this violates the principle of least surprise. On
> the other hand, add_missing_from is certainly a legacy thing and maybe
> we shouldn't bother expending any extra code to make it work with
> new features. Thoughts?

Yes, it's legacy. I wouldn't bother.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:

> Tom Lane wrote:
>
>> 2. The patch didn't touch the implicit-RTE code, which means that
>>
>> WITH q AS ( SELECT ... )
>> SELECT q.*
>>
>> will fail even if you've got add_missing_from enabled. I'm inclined
>> to think that this violates the principle of least surprise. On
>> the other hand, add_missing_from is certainly a legacy thing and maybe
>> we shouldn't bother expending any extra code to make it work with
>> new features. Thoughts?
>
> Yes, it's legacy. I wouldn't bother.

The results would be even more suprising if there *is* a table named "q"
though...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

> Tom Lane wrote:
>> that Oracle chooses to treat WITH-queries as if they were plain
>> sub-selects if they're non-recursive and only referenced once.
>> That is, Oracle would rewrite the above into
>>
>> SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>>
>> and then flatten the sub-select and optimize normally. It would
>> not be hard to make Postgres do the same, but then we would lose
>> some guarantees about predictable execution of volatile functions.
>>
>> I'm inclined to think that there is no reason to provide two
>> different syntaxes to do the same thing, and so having the WITH
>> syntax behave like this is okay. But it could well result in
>> performance surprises for people who are used to Oracle.
>>
>> Any thoughts on what to do? One possibility is to flatten only
>> if the subquery doesn't contain any volatile functions.

I think we should always inline the view if there's a single call site. If
people want to control the subsequent flattening they can do it the same way
they can do today for inline views using OFFSET 0.

The question in my mind is if we can do better for CTEs with multiple call
sites. If we have no volatile function calls in them then we should be free to
inline some or all call sites. I'm not sure we have enough information early
enough to make the decision though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Tom Lane wrote:
>>> 2. The patch didn't touch the implicit-RTE code, which means that
>>>
>>> WITH q AS ( SELECT ... )
>>> SELECT q.*
>>>
>>> will fail even if you've got add_missing_from enabled.
>>
>> Yes, it's legacy. I wouldn't bother.

> The results would be even more suprising if there *is* a table named "q"
> though...

Yeah, the real problem is not so much that it might fail as that it
might silently do something quite different from what you would expect.

CVS HEAD documentation states

(In fact, the WITH query hides any real table of the same name for the
purposes of the primary query. If necessary, you can refer to a real
table of the same name by schema-qualifying the table's name.)

If we don't fix this, I think we'd have to add some disclaimer about
how WITH clauses *don't* hide real tables in the case of implicit RTE
additions. That seems much uglier than fixing it.

(Hmm, memo to self: I'll bet ruleutils.c's decision about whether it
needs to schema-qualify a reverse-listed table name doesn't take this
into account.)

regards, tom lane

Hi all,

While i'm testing the HEAD version of CVS with this new feature, i
found a possible bug and like that more persons could try it in you
own box.

The attached file is a log of my test and I'm using a unprivileged
user to do it.

Thanks.
--
[]s
Dickson S. Guedes
-------------------------------------
Projeto Colmeia - Florianopolis, SC
(48) 3322-1185 ramal: 27
http://makeall.wordpress.com/
http://pgcon.postgresql.org.br/
http://planeta.postgresql.org.br/

"Dickson S. Guedes" <guediz(at)gmail(dot)com> writes:
> While i'm testing the HEAD version of CVS with this new feature, i
> found a possible bug and like that more persons could try it in you
> own box.

Yeah, that's a bug (two different ones in fact). Fixed --- thanks for
the report!

regards, tom lane

On Oct 5, 2008, at 1:11 AM, Peter Eisentraut wrote:
> I don't think we should overload syntax choices with optimization
> hints. We don't really know why or how people will be using this
> syntax, and labeling it from the start as "will have unusual
> performance behavior" isn't a good sell.
>
> As a precedent, consider the JOIN syntax, which is obviously
> redundant and in its first implementation contained an implicit
> optimization hint with regard to join order that later had to be
> done away with because it confused users (I think). The CTE case
> is quite similar, and maybe the GUC answer of old could apply here
> as well. But I think by default we should abide by SQL's
> declarative approach of "Tell me what you want and I'll execute it
> any way I like."

Agreed. It's already horrible that we suggest people use OFFSET 0,
only because we don't want to define formal optimizer hints (and
that's *exactly* what OFFSET 0 is).
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

> Agreed. It's already horrible that we suggest people use OFFSET 0, only
> because we don't want to define formal optimizer hints (and that's *exactly*
> what OFFSET 0 is).

Yes, especially since TFM says:

"OFFSET 0 is the same as omitting the OFFSET clause."

Unless I'm looking at the wrong part of the manual?

...Robert

[ back to the when-to-inline-WITHs discussion ]

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> Tom Lane wrote:
>>> Any thoughts on what to do? One possibility is to flatten only
>>> if the subquery doesn't contain any volatile functions.

> I think we should always inline the view if there's a single call site. If
> people want to control the subsequent flattening they can do it the same way
> they can do today for inline views using OFFSET 0.

That's certainly a defensible choice, and in fact was what I had
intended to do at one point (that's why CommonTableExpr.cterefcount
is in there). However, the extent to which you can prevent duplicate
evaluation in an inline view is actually pretty limited. As an example
consider

select ... from table1,
(select expensive_function(...) from table2 offset 0) ss
where table1.key = table2.key;

If the planner chooses to do this as a nestloop with table2 on the
inside, then expensive_function() can get evaluated multiple times on
the same row of table2. We really don't make very many guarantees about
what will happen with functions inside inlined views, even with "offset
0" as an optimization fence. So I was thinking that taking a strong
reading of the spec's wording about single evaluation of WITH clauses
might provide useful leverage for people who need to control evaluation
of expensive or volatile functions better than they can now.

Another possibility that we could think about is: if a CTE is only
referenced once, then push down any restriction clauses that are
available at the single call site, but still execute it using the
CteScan materialization logic. The evaluation guarantee would then
look like "no row of the CTE's result is evaluated twice, but some rows
might not be evaluated at all". What we'd pay for this is that the CTE
could not be the inside of a nestloop with inner indexscan using a join
condition, since we don't have any way to keep track of which rows were
already fetched in that case.

regards, tom lane

> If the planner chooses to do this as a nestloop with table2 on the
> inside, then expensive_function() can get evaluated multiple times on
> the same row of table2. We really don't make very many guarantees about
> what will happen with functions inside inlined views, even with "offset
> 0" as an optimization fence. So I was thinking that taking a strong
> reading of the spec's wording about single evaluation of WITH clauses
> might provide useful leverage for people who need to control evaluation
> of expensive or volatile functions better than they can now.

+1 for a strong reading. I think the ability to prevent multiple
evaluations of expensive functions is key here.

> Another possibility that we could think about is: if a CTE is only
> referenced once, then push down any restriction clauses that are
> available at the single call site, but still execute it using the
> CteScan materialization logic. The evaluation guarantee would then
> look like "no row of the CTE's result is evaluated twice, but some rows
> might not be evaluated at all".

Assuming a perfectly intelligent optimizer, the only advantage of the
=1 guarantee over the <=1 guarantee is that you can evaluate the
entire CTE for side-effects and then fetch back only a subset of the
data to return to the user. This seems likely to be a pretty rare use
case, though, and the rest of the time you'd presumably prefer for
performance reasons to have as little of the CTE as possible
executed.... so +1 for <=1.

> What we'd pay for this is that the CTE
> could not be the inside of a nestloop with inner indexscan using a join
> condition, since we don't have any way to keep track of which rows were
> already fetched in that case.

Is it not possible to consider both plans? That is, compare the cost
of evaluating every row and then doing a nestloop with inner indexscan
versus using some other plan and evaluating only the rows meeting the
quals?

As a side note, in theory, I think you could generalize this to CTEs
with multiple call sites by taking the logical OR of the available
quals. This might not be worth it, though unless the quals are highly
selective.

...Robert

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> [ back to the when-to-inline-WITHs discussion ]
>
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> >> Tom Lane wrote:
> >>> Any thoughts on what to do? One possibility is to flatten only
> >>> if the subquery doesn't contain any volatile functions.
>
> > I think we should always inline the view if there's a single call site. If
> > people want to control the subsequent flattening they can do it the same way
> > they can do today for inline views using OFFSET 0.
>
> That's certainly a defensible choice, and in fact was what I had
> intended to do at one point (that's why CommonTableExpr.cterefcount
> is in there). However, the extent to which you can prevent duplicate
> evaluation in an inline view is actually pretty limited. As an example
> consider
>
> select ... from table1,
> (select expensive_function(...) from table2 offset 0) ss
> where table1.key = table2.key;
>
> If the planner chooses to do this as a nestloop with table2 on the
> inside, then expensive_function() can get evaluated multiple times on
> the same row of table2. We really don't make very many guarantees about
> what will happen with functions inside inlined views, even with "offset
> 0" as an optimization fence. So I was thinking that taking a strong
> reading of the spec's wording about single evaluation of WITH clauses
> might provide useful leverage for people who need to control evaluation
> of expensive or volatile functions better than they can now.
>
> Another possibility that we could think about is: if a CTE is only
> referenced once, then push down any restriction clauses that are
> available at the single call site, but still execute it using the
> CteScan materialization logic. The evaluation guarantee would then
> look like "no row of the CTE's result is evaluated twice, but some rows
> might not be evaluated at all". What we'd pay for this is that the CTE
> could not be the inside of a nestloop with inner indexscan using a join
> condition, since we don't have any way to keep track of which rows were
> already fetched in that case.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Is this a TODO?

I'm inclined to leave it as-is, at least till we get some field
feedback about how people want it to behave.

regards, tom lane

(quoting more than usual to provide context because this is such an old thread)

On Sat, Oct 4, 2008 at 11:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I've applied the latest version of the CTE patch.  Congratulations on
> making that happen!
>
> There are still some loose ends that need to be considered, though.
>
> 1. As committed, the patch takes an extremely hard line about WITH
> queries being evaluated independently of the main query and only once
> per main query execution.  This could be seen as a good thing --- it
> provides much more determinism for execution of volatile functions
> within complex queries than was really available in the past.  It could
> also be seen as a bad thing --- in particular, we won't push any
> limiting qualifications from the main query into the WITH queries.
> So for instance
>
>        WITH q AS ( SELECT * FROM foo )
>        SELECT * FROM q WHERE key = 42;
>
> is going to be executed quite inefficiently; it won't use an index on
> foo.key.  I think we don't have much choice about this in the case of
> recursive WITH queries: it would be pretty difficult to determine
> whether pushing a restriction into a recursive WITH would change the
> results incorrectly.  However, for plain non-recursive WITHs it's all
> a matter of definition.  I gather from
> http://www.oracle-developer.net/display.php?id=212
> that Oracle chooses to treat WITH-queries as if they were plain
> sub-selects if they're non-recursive and only referenced once.
> That is, Oracle would rewrite the above into
>
>        SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
>
> and then flatten the sub-select and optimize normally.  It would
> not be hard to make Postgres do the same, but then we would lose
> some guarantees about predictable execution of volatile functions.
>
> I'm inclined to think that there is no reason to provide two
> different syntaxes to do the same thing, and so having the WITH
> syntax behave like this is okay.  But it could well result in
> performance surprises for people who are used to Oracle.
>
> Any thoughts on what to do?  One possibility is to flatten only
> if the subquery doesn't contain any volatile functions.
>

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 sure we will work out with the order of in which the various
phases of analysis are done on the outer query compared to the
subquery.

--
greg

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

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

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
> 2009/5/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> 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?

Uh ... you've got the causality backwards, and I don't see the point of
such a restriction anyway.

regards, tom lane

On Wed, May 27, 2009 at 12:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm not too thrilled about that solution because it still eliminates
> predictability of execution of volatile functions.

How so? It means the volatile function might only be executed for the
matching rows but the rows will still have the same value for the same
rows for all references to the CTE which seems like the key property
to me.

--
greg

Greg Stark wrote:
> On Wed, May 27, 2009 at 12:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm not too thrilled about that solution because it still eliminates
>> predictability of execution of volatile functions.
>
> How so? It means the volatile function might only be executed for the
> matching rows but the rows will still have the same value for the same
> rows for all references to the CTE which seems like the key property
> to me.

A volatile function could have side-effects, ie. insert rows to another
table. I would not recommend a design that relies on such behavior, but
it should be predictable how often the volatile function is run if you
do that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Greg Stark <stark(at)enterprisedb(dot)com> writes:
> On Wed, May 27, 2009 at 12:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm not too thrilled about that solution because it still eliminates
>> predictability of execution of volatile functions.

> How so? It means the volatile function might only be executed for the
> matching rows

Exactly. If the point of the CTE is to ensure that nextval() is
executed N times, and it actually gets executed less than that,
then we've broken the semantics in a visible way.

regards, tom lane