Speed depending of Join Order. - Mailing list pgsql-sql

I will explain my question usin an example. I have two tables as
follows:
       Table "public.image_mode"  Column    |     Type      | Modifiers 
-------------+---------------+-----------mis_id      | character(5)  | not nullins_id      | character(5)  | not
nullimg_id     | character(25) | not nullmod_mis_id  | character(5)  | not nullmod_ins_id  | character(5)  | not
nullmod_id     | character(5)  | not nullmod_valueid | character(5)  | not null
 
Indexes: pk_imgmode primary key btree (mis_id, ins_id, img_id,
mod_mis_id, mod_ins_id, mod_id, mod_valueid),        image_mode_fk_image btree (mis_id, ins_id, img_id),
image_mode_fk_modebtree (mod_mis_id, mod_ins_id, mod_id,
 
mod_valueid)

                Table "public.mode"    Column      |          Type          | Modifiers 
-----------------+------------------------+-----------mis_id          | character(5)           | not nullins_id
| character(5)           | not nullmod_id          | character(5)           | not nullmod_valueid     | character(5)
      | not nullmod_name        | character varying(50)  | not nullmod_value       | character varying(25)  | not
nullvmod_valuenr   | double precision       | vmod_valueunits | character varying(25)  | vmod_obs        | character
varying(255)| 
 
Indexes: pk_mode primary key btree (mis_id, ins_id, mod_id, mod_valueid)


Ten I perform the same search in two different ways:

SELECT mod.mod_id, mod.mod_value      FROM image_mode imod, mode mod      WHERE imod.mod_mis_id = mod.mis_id      AND
imod.mod_ins_id= mod.ins_id      AND   imod.mod_id     = mod.mod_id      AND   imod.mod_valueid= mod.mod_valueid
AND  imod.mis_id='XXX'      AND   imod.ins_id='YYY'      AND   imod.img_id='ZZZ';
 

SELECT mod.mod_id, mod.mod_value      FROM image_mode imod, mode mod      WHERE mod.mis_id = imod.mod_mis_id      AND
mod.ins_id= imod.mod_ins_id      AND   mod.mod_id = imod.mod_id      AND   mod.mod_valueid= imod.mod_valueid      AND
imod.mis_id='XXX'     AND   imod.ins_id='YYY'      AND   imod.img_id='ZZZ';
 

Note that the only difference is the order of the join elements. Using
version 7.2.2, which I have been using untill now, the time expended in
both of them was the same, using the right indexes. However, using
version 7.3.1 which I have instaled recently, the results of the explain
are the following:

-------- Case 1: ------------
Merge Join  (cost=1.79..1.92 rows=1 width=79) (actual
time=404.29..4109.78 rows=2 loops=1)  Merge Cond: (("outer".mod_mis_id = "inner".mis_id) AND
("outer".mod_ins_id = "inner".ins_id) AND ("outer".mod_id =
"inner".mod_id) AND ("outer".mod_valueid = "inner".mod_valueid))  ->  Index Scan using image_mode_fk_mode on image_mode
imod
 
(cost=0.00..606979.14 rows=1 width=36) (actual time=403.42..4108.67
rows=2 loops=1)        Filter: ((mis_id = 'IUE'::bpchar) AND (ins_id = 'LWP'::bpchar)
AND (img_id = 'HL28915'::bpchar))  ->  Sort  (cost=1.79..1.85 rows=24 width=43) (actual time=0.81..0.81
rows=5 loops=1)        Sort Key: mod.mis_id, mod.ins_id, mod.mod_id, mod.mod_valueid        ->  Seq Scan on "mode" mod
(cost=0.00..1.24rows=24 width=43)
 
(actual time=0.10..0.19 rows=24 loops=1)Total runtime: 4109.96 msec



--------  Case 2:  ---------
Merge Join  (cost=5.69..5.71 rows=1 width=79) (actual time=1.12..1.30
rows=2 loops=1)  Merge Cond: (("outer".mis_id = "inner".mod_mis_id) AND
("outer".ins_id = "inner".mod_ins_id) AND ("outer".mod_id =
"inner".mod_id) AND ("outer".mod_valueid = "inner".mod_valueid))  ->  Index Scan using pk_mode on "mode" mod
(cost=0.00..6.08rows=24
 
width=43) (actual time=0.27..0.30 rows=5 loops=1)  ->  Sort  (cost=5.69..5.70 rows=1 width=36) (actual time=0.81..0.81
rows=2 loops=1)        Sort Key: imod.mod_mis_id, imod.mod_ins_id, imod.mod_id,
imod.mod_valueid        ->  Index Scan using image_mode_fk_image on image_mode imod 
(cost=0.00..5.68 rows=1 width=36) (actual time=0.58..0.61 rows=2
loops=1)              Index Cond: ((mis_id = 'IUE'::bpchar) AND (ins_id =
'LWP'::bpchar) AND (img_id = 'HL28915'::bpchar))Total runtime: 1.45 msec



As you can see, there is a great differece in the time it takes to
execute each of them since a sequential scan is performed in Case 1
instead an Index scan. I have run vacuum analyze so I am sure this is
not the problem.

Thank you very much in advance,
Raul Gutierrez