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 null
ins_id | character(5) | not null
img_id | character(25) | not null
mod_mis_id | character(5) | not null
mod_ins_id | character(5) | not null
mod_id | character(5) | not null
mod_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_mode btree (mod_mis_id, mod_ins_id, mod_id,
mod_valueid)
Table "public.mode"
Column | Type | Modifiers
-----------------+------------------------+-----------
mis_id | character(5) | not null
ins_id | character(5) | not null
mod_id | character(5) | not null
mod_valueid | character(5) | not null
mod_name | character varying(50) | not null
mod_value | character varying(25) | not null
vmod_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.24 rows=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.08 rows=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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
