Re: Repeating Append operation

On Sun, Mar 21, 2010 at 4:29 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Mar 19, 2010 at 2:09 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
> wrote:
> > Is there a way to avoid this double evaluation?
>
> Maybe with a CTE?
>
> WITH x AS (...) SELECT ...
>
> It does look like surprising behavior.
>

It was discussed on the IRC that same day, and RhodiumToad (Andrew) pointed
out that this behaviour is because of subquery un-nesting. Putting an OFFSET
0 clause (hint) in the inline view prevents it from being merged with the
outer query:

explain
select v from (
select array(
select 1
union all
select 2) as v
from (select 1) offset 0) as s
where v is not null;
QUERY PLAN
----------------------------------------------------------------------------------
Subquery Scan s (cost=0.04..0.07 rows=1 width=32)
Filter: (v IS NOT NULL)
-> Limit (cost=0.04..0.06 rows=1 width=0)
InitPlan
-> Append (cost=0.00..0.04 rows=2 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan __unnamed_subquery_0 (cost=0.00..0.02 rows=1
width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
(9 rows)

This raises the point that we do subquery un-nesting purely on
heuristics, and not on cost basis. I guess we should be be doing a cost
comparison too. I think that this un-nesting happens quite before we start
generating alternative plans for cost comparisons, and that we might not
have costs to compare at this stage, but IMHO we should somehow incorporate
cost comparisons too.

Regards,

--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device