On Tue, Mar 23, 2010 at 2:09 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Sun, Mar 21, 2010 at 4:29 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>
>> 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.
I don't think this is right. Flattening the subquery doesn't prevent
the join from being implemented a nested loop, which is essentially
what happens when it's treated as an initplan. It just allows other
options also.
...Robert