query faster using LEFT OUTER join?

I have a planner question about a multi-join query, where if I  
rewrite the query excluding the table that is left joined to the  
other tables my query speed increases by 75%.

I've tested this query on both 8.0.9 and 8.2b3, w/ similar results  
(except 8.2b3 is 50% faster!)

I'm wondering why the first query is planned a faster way and if  
there is anything I could do to my 2nd query faster (which should be  
faster because it doesn't have to scan the first table).


Here's my initial query that runs in 22.961ms:
SELECT    train_family_id, object_type,  COUNT(*) AS frequency
FROM    translation_pair tp    LEFT OUTER JOIN instance i USING(translation_pair_id)    JOIN context c
USING(context_id)   JOIN loc_submission ls USING(loc_submission_id)    JOIN loc_submission_train_info lsti
USING(loc_submission_id)
WHERE    translation_pair_id IN (640352, 6144, 1023028, 18155, 240244,  
50157)
GROUP BY    train_family_id,    object_type;


If I just remove the translation_pair table and join directly against  
the instance table, the query speed drops 50% to 36.6ms
SELECT    train_family_id, object_type, COUNT(*) AS frequency
FROM    instance i    JOIN context c USING(context_id)    JOIN loc_submission ls USING(loc_submission_id)    JOIN
loc_submission_train_infolsti USING(loc_submission_id)
 
WHERE    translation_pair_id IN (640352, 6144, 1023028, 18155, 240244,  
50157)
GROUP BY    train_family_id,    object_type;


Looking at the query plans for the 2 of them, it looks like the inner  
"Bitmap Index Scan on instance_translation_pair_id" is much slower  
when using "IN (x,y,z)" instead of comparing against table  
(tp.translation_pair_id = i.translation_pair_id.

Is there anything else I should notice? Can I force the planner to  
use the translation_pair table first?


Here's the plan for the first query (using 8.2b3)
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
-----------------------------------
HashAggregate  (cost=3223.17..3223.41 rows=19 width=18) (actual  
time=34.913..34.978 rows=122 loops=1)   ->  Nested Loop Left Join  (cost=29.53..3223.03 rows=19 width=18)  
(actual time=0.606..33.994 rows=770 loops=1)         ->  Nested Loop Left Join  (cost=29.53..3108.80 rows=19  
width=22) (actual time=0.552..26.918 rows=770 loops=1)               ->  Nested Loop Left Join  (cost=29.53..2995.26  
rows=19 width=18) (actual time=0.497..19.924 rows=768 loops=1)                     ->  Nested Loop Left Join
(cost=29.53..2879.59 
 
rows=19 width=8) (actual time=0.431..6.395 rows=768 loops=1)                           ->  Bitmap Heap Scan on
translation_pair 
 
tp  (cost=24.27..48.10 rows=6 width=4) (actual time=0.304..0.376  
rows=6 loops=1)                                 Recheck Cond: (translation_pair_id  
= ANY ('{640352,6144,1023028,18155,240244,50157}'::oid[]))                                 ->  Bitmap Index Scan on  
translation_pair_pkey  (cost=0.00..24.27 rows=6 width=0) (actual  
time=0.254..0.254 rows=6 loops=1)                                       Index Cond:  
(translation_pair_id = ANY  
('{640352,6144,1023028,18155,240244,50157}'::oid[]))                           ->  Bitmap Heap Scan on instance i   
(cost=5.26..470.38 rows=123 width=12) (actual time=0.119..0.922  
rows=128 loops=6)                                 Recheck Cond:  
(tp.translation_pair_id = i.translation_pair_id)                                 ->  Bitmap Index Scan on  
instance_translation_pair_id  (cost=0.00..5.26 rows=123 width=0)  
(actual time=0.098..0.098 rows=128 loops=6)                                       Index Cond:  
(tp.translation_pair_id = i.translation_pair_id)                     ->  Index Scan using context_pkey on context c   
(cost=0.00..6.07 rows=1 width=18) (actual time=0.015..0.016 rows=1  
loops=768)                           Index Cond: (i.context_id = c.context_id)               ->  Index Scan using
loc_submission_train_info_pkey 
 
on loc_submission_train_info lsti  (cost=0.00..5.96 rows=1 width=8)  
(actual time=0.007..0.008 rows=1 loops=768)                     Index Cond: (i.loc_submission_id =  
lsti.loc_submission_id)         ->  Index Scan using loc_submission_pkey on loc_submission  
ls  (cost=0.00..6.00 rows=1 width=4) (actual time=0.007..0.008 rows=1  
loops=770)               Index Cond: (i.loc_submission_id = ls.loc_submission_id)


And here's the plan for the second query without the first LEFT OUTER  
join (using 8.2b3)
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
------------------------
HashAggregate  (cost=8927.87..8936.89 rows=721 width=18) (actual  
time=39.484..39.551 rows=122 loops=1)   ->  Nested Loop Left Join  (cost=3706.71..8922.47 rows=721  
width=18) (actual time=25.246..38.654 rows=770 loops=1)         ->  Hash Left Join  (cost=3706.71..4000.93 rows=721  
width=8) (actual time=25.215..31.169 rows=770 loops=1)               Hash Cond: (i.loc_submission_id =
ls.loc_submission_id)              ->  Merge Left Join  (cost=3420.14..3481.22 rows=721  
 
width=12) (actual time=15.088..20.018 rows=770 loops=1)                     Merge Cond: (i.loc_submission_id =  
lsti.loc_submission_id)                     ->  Sort  (cost=2576.82..2578.62 rows=720  
width=8) (actual time=2.287..2.438 rows=768 loops=1)                           Sort Key: i.loc_submission_id
              ->  Bitmap Heap Scan on instance i   
 
(cost=25.24..2542.65 rows=720 width=8) (actual time=0.381..1.623  
rows=768 loops=1)                                 Recheck Cond: (translation_pair_id  
= ANY ('{640352,6144,1023028,18155,240244,50157}'::oid[]))                                 ->  Bitmap Index Scan on  
instance_translation_pair_id  (cost=0.00..25.24 rows=720 width=0)  
(actual time=0.293..0.293 rows=768 loops=1)                                       Index Cond:  
(translation_pair_id = ANY  
('{640352,6144,1023028,18155,240244,50157}'::oid[]))                     ->  Sort  (cost=843.32..868.47 rows=10059  
width=8) (actual time=12.782..14.312 rows=10530 loops=1)                           Sort Key: lsti.loc_submission_id
                     ->  Seq Scan on loc_submission_train_info  
 
lsti  (cost=0.00..174.59 rows=10059 width=8) (actual  
time=0.012..3.708 rows=10059 loops=1)               ->  Hash  (cost=261.46..261.46 rows=10046 width=4)  
(actual time=10.061..10.061 rows=10046 loops=1)                     ->  Seq Scan on loc_submission ls   
(cost=0.00..261.46 rows=10046 width=4) (actual time=0.013..5.235  
rows=10046 loops=1)         ->  Index Scan using context_pkey on context c   
(cost=0.00..6.81 rows=1 width=18) (actual time=0.008..0.009 rows=1  
loops=770)               Index Cond: (i.context_id = c.context_id)


Thanks for your help,

Drew