Re: Using bitmap index scans-more efficient

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)