Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
I'm wondering if this might expose a weakness in the optimizer having to
do with left joins.
Before 8.2 the optimizer has no ability to rearrange the order of outer
joins. Do you have time to try your test case against CVS HEAD?
OK, I figured it out--grabbed the latest snapshot (hope that is what you
need).
My results are similar:
select l.* from ledg_v1 l, proj p where l.proj = p.proj_id and 5 =
p.par; (24 msec)
Nested Loop (cost=0.00..1991.93 rows=480 width=23)
-> Nested Loop (cost=0.00..4.68 rows=6 width=8)
-> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4)
Filter: ((code)::text = 'ap'::text)
-> Index Scan using i_proj_par on proj p (cost=0.00..3.49
rows=6 width=4)
Index Cond: (5 = par)
-> Index Scan using i_ledg_proj on ledg l (cost=0.00..330.17
rows=83 width=19)
Index Cond: (l.proj = "outer".proj_id)
select l.* from ledg_v2 l, proj p where l.proj = p.proj_id and 5 =
p.par; (1.25 sec)
Hash Join (cost=4.63..16768.43 rows=480 width=23)
Hash Cond: ("outer".proj = "inner".proj_id)
-> Nested Loop Left Join (cost=1.13..14760.13 rows=400000 width=23)
-> Seq Scan on ledg l (cost=0.00..6759.00 rows=400000 width=19)
-> Materialize (cost=1.13..1.14 rows=1 width=4)
-> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4)
Filter: ((code)::text = 'ap'::text)
-> Hash (cost=3.49..3.49 rows=6 width=4)
-> Index Scan using i_proj_par on proj p (cost=0.00..3.49
rows=6 width=4)
Index Cond: (5 = par)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
