Re: Repeating Append operation

 

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,