Re: Another planner oddity

At 10:53 3/11/01 -0500, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> explain select
>> (select r.n from r where r.p=tv.p), -- no reference to gn!
>> sum(x)
>> From
>> tv
>
>What's your point? We can't omit the join to g, as that would change
>the set of returned rows. (In general, anyway; in this case the
>dependency is that multiple matches in g would change sum(x) for
>any given r.n.)

Oops. Left out too much. Make each of the ref'd tables unique (so only one
match for given t.p):

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

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=308.49..313.49 rows=100 width=76)
-> Group (cost=308.49..310.99 rows=1000 width=76)
-> Sort (cost=308.49..308.49 rows=1000 width=76)
-> Merge Join (cost=189.16..258.66 rows=1000 width=76)
-> Index Scan using g_rn_key on g (cost=0.00..52.00
rows=1000 width=32)
-> Sort (cost=189.16..189.16 rows=1000 width=44)
-> Merge Join (cost=69.83..139.33 rows=1000
width=44)
-> Index Scan using r_pkey on r
(cost=0.00..52.00 rows=1000 width=36)
-> Sort (cost=69.83..69.83 rows=1000
width=8)
-> Seq Scan on t (cost=0.00..20.00
rows=1000 width=8)
SubPlan
-> Index Scan using r_pkey on r (cost=0.00..4.82
rows=1 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 |/