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.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
