Prev: [HACKERS] Postgres officially accepted in to 2010 Google Summer of Code program
Next: [HACKERS] Call for translations
From: Gurjeet Singh on 19 Mar 2010 14:09
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
 | 
Pages: 1
Prev: [HACKERS] Postgres officially accepted in to 2010 Google Summer of Code program
Next: [HACKERS] Call for translations