At 10:53 3/11/01 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.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 selectt.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)
Fromtv
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.49rows=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) ->
MergeJoin (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 |/
