[PostgreSQL-Hackers] Another planner oddity

Another mild planning oddity; this time, the query does not seem to rem,ove
an unreferenced column from the plan. No big deal, but for larger queries
it can significantly increase the cost.

create table g(n text, rn text);
create table r(n text, p int);
create table t(p int, x int);

-- Basically LOJ t->r->g, and return 'n' from g if found.
create view tv as select
t.p,
g.n as gn,
x
from
t left outer join r on (r.p=t.p)
left outer join g on (g.rn = r.n)
;

explain select
(select r.n from r where r.p=tv.p), -- no reference to gn!
sum(x)
From
tv
Group by 1
;

Aggregate (cost=3378.54..3503.54 rows=2500 width=76)
-> Group (cost=3378.54..3441.04 rows=25000 width=76)
-> Sort (cost=3378.54..3378.54 rows=25000 width=76)
-> Merge Join (cost=584.18..911.68 rows=25000 width=76)
-> Sort (cost=514.35..514.35 rows=5000 width=44)
-> Merge Join (cost=139.66..207.16 rows=5000
width=44)
-> Sort (cost=69.83..69.83 rows=1000
width=8)
-> Seq Scan on t (cost=0.00..20.00
rows=1000 width=8)
-> Sort (cost=69.83..69.83 rows=1000
width=36)
-> Seq Scan on r (cost=0.00..20.00
rows=1000 width=36)
-> Sort (cost=69.83..69.83 rows=1000 width=32)
!!!!!! -> Seq Scan on g (cost=0.00..20.00 rows=1000
width=32)
SubPlan
!? -> Seq Scan on r (cost=0.00..22.50 rows=5 width=32)


----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/