Re: Repeating Append operation

On Tue, Mar 23, 2010 at 2:09 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> 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.

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