Message-ID: <3E2D3467.BEA05FE9@laeff.esa.es>
Date: Tue, 21 Jan 2003 12:52:07 +0100
From: =?iso-8859-1?Q?Ra=FAl=20Guti=E9rrez=20S=E1nchez?= <raul@laeff.esa.es>
MIME-Version: 1.0
To: PgSql-SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Speed depending of Join Order.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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