The following bug has been logged on the website:
Bug reference: 11279
Logged by: Mason
Email address: qiao5034@163.com
PostgreSQL version: 9.2.4
Operating system: linux suse 2.6.32.12-0.7-default
Description:
The following query generates two subplan which is unnecessary.
scripts:
create table a(a1 int, a2 int);
create table b(b1 int, b2 int);
explain
select * from (
select a1, a2,(select SUM(b2) sum_b2 from b where a1 = b1)
from a ) v
where a2 = sum_b2;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on a (cost=0.00..327949668.86 rows=44331 width=8)
Filter: (a2 = (SubPlan 2))
SubPlan 1
-> Aggregate (cost=36.78..36.79 rows=1 width=4)
-> Seq Scan on b (cost=0.00..36.75 rows=11 width=4)
Filter: (a.a1 = b1)
SubPlan 2
-> Aggregate (cost=36.78..36.79 rows=1 width=4)
-> Seq Scan on b b_1 (cost=0.00..36.75 rows=11 width=4)
Filter: (a.a1 = b1)
(10 rows)