On Fri, Mar 19, 2010 at 2:09 PM, Gurjeet Singh <singh.gurjeet@gmail.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.
