Repeating Append operation - Mailing list pgsql-hackers

Hi all,

explain
select v from (
select array(
        select 1
        union all
        select 2) as v
from (select 1) ) as s
where v is not null;

The plan looks like:

                       QUERY PLAN
--------------------------------------------------------
 Result  (cost=0.08..0.10 rows=1 width=0)
   One-Time Filter: ($1 IS NOT NULL)
   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)
     ->  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)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(10 rows)

    It seems that that the UNION ALL part of the query will be executed twice. If I remove the WHERE clause the I see only one Append operation. I had a suspicion that its just the display of the plan that showed the same plan twice, but then I noticed that the overall cost of the query also drops making me think that this UNION ALL will actually be executed twice.

    The plan without the WHERE clause is:
                              QUERY PLAN
----------------------------------------------------------------------
 Subquery Scan __unnamed_subquery_0  (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)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(6 rows)

    I had seen this with a bigger query on actual tables, and this is just a reproducible test case. In the original query I see 'SubPlan' instead of the 'InitPlan' seen here.

Head of plan with WHERE  clause:
  Seq Scan on "Person" p  (cost=0.00..280486580881.10 rows=1373415 width=4)

Head of plan without WHERE  clause:
  Seq Scan on "Person" p  (cost=0.00..140594841889.03 rows=1380317 width=4)

    Is there a way to avoid this double evaluation?

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

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device