Tom Lane wrote:
>Kyle Bateman <kyle@actarg.com> 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) ->
NestedLoop Left Join (cost=1.13..14760.13 rows=400000 width=23) -> Seq Scan on ledg l (cost=0.00..6759.00
rows=400000width=19) -> Materialize (cost=1.13..1.14 rows=1 width=4) -> Seq Scan on acct a
(cost=0.00..1.12rows=1 width=4) Filter: ((code)::text = 'ap'::text) -> Hash (cost=3.49..3.49
rows=6width=4) -> Index Scan using i_proj_par on proj p (cost=0.00..3.49
rows=6 width=4) Index Cond: (5 = par)
