Re: Another planner oddity
От | Philip Warner |
---|---|
Тема | Re: Another planner oddity |
Дата | |
Msg-id | 3.0.5.32.20011104101910.02a75390@mail.rhyme.com.au обсуждение исходный текст |
Ответ на | Re: Another planner oddity (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Another planner oddity
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
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 |/
В списке pgsql-hackers по дате отправления: