Hello,
I have a query, which is quite big and there is a huge difference between execution time in MySQL and PostgreSQL. I think that I have made all possible steps to increase the speed of the query, but unfortunately it is still about 100 times slower. I'm out of ideas what to do next, so maybe you will point me what shall I do. In the attachment I send you the result of "explain analyze".


I will be appreciated for any help. Thanks in advance.

ML
                                                                                       
                                            QUERY PLAN                                 
                                                                                       
           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=10224.78..10899.69 rows=337 width=1070) (actual 
time=2133.000..2193.000 rows=3618 loops=1)
   Merge Cond: (("outer".equipment_id = "inner".equipment_id) AND ("outer".date_id = 
"inner".date_id))
   ->  Index Scan using mda_mak_mod_equ_nam__pkey on mda_mak_mod_equ_nam _pen_lang  
(cost=0.00..603.16 rows=12833 width=44) (actual time=0.000..50.000 rows=12805 loops=1)
         Filter: (language_id = 1)
   ->  Sort  (cost=10224.78..10225.62 rows=337 width=1038) (actual 
time=2032.000..2042.000 rows=3618 loops=1)
         Sort Key: _peq.equipment_id, _dat.date_id
         ->  Merge Right Join  (cost=9540.77..10210.63 rows=337 width=1038) (actual 
time=1872.000..1932.000 rows=3618 loops=1)
               Merge Cond: (("outer".equipment_id = "inner".equipment_id) AND 
("outer".date_id = "inner".date_id))
               ->  Index Scan using mda_mak_mod_equ_nam__pkey on mda_mak_mod_equ_nam 
_pen  (cost=0.00..603.16 rows=12833 width=44) (actual time=0.000..50.000 rows=12805 
loops=1)
                     Filter: (language_id = 1)
               ->  Sort  (cost=9540.77..9541.61 rows=337 width=1002) (actual 
time=1772.000..1782.000 rows=3618 loops=1)
                     Sort Key: _peq.equipment_id, _dat.date_id
                     ->  Merge Right Join  (cost=8856.76..9526.62 rows=337 width=1002) 
(actual time=1532.000..1652.000 rows=3618 loops=1)
                           Merge Cond: (("outer".equipment_id = "inner".equipment_id) 
AND ("outer".date_id = "inner".date_id))
                           ->  Index Scan using mda_mak_mod_equ_nam__pkey on 
mda_mak_mod_equ_nam _nam  (cost=0.00..603.16 rows=12833 width=44) (actual 
time=0.000..50.000 rows=12805 loops=1)
                                 Filter: (language_id = 1)
                           ->  Sort  (cost=8856.76..8857.60 rows=337 width=966) 
(actual time=1522.000..1522.000 rows=3618 loops=1)
                                 Sort Key: _equ.equipment_id, _dat.date_id
                                 ->  Merge Right Join  (cost=8172.75..8842.61 rows=337 
width=966) (actual time=1261.000..1382.000 rows=3618 loops=1)
                                       Merge Cond: (("outer".equipment_id = 
"inner".equipment_id) AND ("outer".date_id = "inner".date_id))
                                       ->  Index Scan using mda_mak_mod_equ_nam__pkey 
on mda_mak_mod_equ_nam _nam_lang  (cost=0.00..603.16 rows=12833 width=44) (actual 
time=0.000..61.000 rows=12805 loops=1)
                                             Filter: (language_id = 1)
                                       ->  Sort  (cost=8172.75..8173.59 rows=337 
width=930) (actual time=1251.000..1261.000 rows=3618 loops=1)
                                             Sort Key: _equ.equipment_id, _dat.date_id
                                             ->  Nested Loop Left Join  
(cost=1.14..8158.60 rows=337 width=930) (actual time=80.000..1141.000 rows=3618 
loops=1)
                                                   ->  Nested Loop Left Join  
(cost=1.14..6189.48 rows=337 width=860) (actual time=80.000..1041.000 rows=1215 
loops=1)
                                                         Join Filter: ("inner".date_id 
= "outer".date_id)
                                                         ->  Merge Left Join  
(cost=1.14..5135.43 rows=337 width=839) (actual time=80.000..1021.000 rows=1215 
loops=1)
                                                               Merge Cond: 
("outer".equipment_parent_id = "inner".equipment_id)
                                                               ->  Nested Loop Left 
Join  (cost=1.14..68283.40 rows=337 width=576) (actual time=80.000..1001.000 rows=1215 
loops=1)
                                                                     Join Filter: 
("inner".group_id = CASE WHEN ("outer".group_id > 0) THEN "outer".group_id ELSE 
"outer".group_id END)
                                                                     ->  Nested Loop 
Left Join  (cost=0.00..68164.31 rows=337 width=576) (actual time=80.000..891.000 
rows=1215 loops=1)
                                                                           ->  Nested 
Loop Left Join  (cost=0.00..67137.87 rows=337 width=572) (actual time=80.000..861.000 
rows=1215 loops=1)
                                                                                 ->  
Nested Loop  (cost=0.00..65476.93 rows=337 width=560) (actual time=80.000..821.000 
rows=403 loops=1)
                                                                                       
->  Nested Loop  (cost=0.00..64457.17 rows=337 width=552) (actual time=80.000..821.000 
rows=403 loops=1)
                                                                                       
      ->  Index Scan using mda_mak_mod_equ__equipment_parent_id__idx on 
mda_mak_mod_equ _equ  (cost=0.00..3505.21 rows=19472 width=271) (actual 
time=10.000..250.000 rows=19185 loops=1)
                                                                                       
            Filter: (("type" <> 'ICO'::bpchar) AND ("type" <> 'OPT'::bpchar))
                                                                                       
      ->  Index Scan using mda_mak_mod_equ_dat__equipment_id__idx on 
mda_mak_mod_equ_dat _dat  (cost=0.00..3.12 rows=1 width=285) (actual time=0.023..0.023 
rows=0 loops=19185)
                                                                                       
            Index Cond: (_dat.equipment_id = "outer".equipment_id)
                                                                                       
            Filter: ((date_id = 95) OR (date_id = 339) OR (date_id = 241) OR (date_id 
= 51))
                                                                                       
->  Index Scan using mda_mak_mod_equ_dts__pkey on mda_mak_mod_equ_dts _arc  
(cost=0.00..3.01 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=403)
                                                                                       
      Index Cond: (_arc.date_id = "outer".date_id)
                                                                                 ->  
Index Scan using mda_mak_mod_equ_spe__equipment_id_date_id_idx on mda_mak_mod_equ_spe 
_spe  (cost=0.00..4.91 rows=1 width=20) (actual time=0.025..0.074 rows=3 loops=403)
                                                                                       
Index Cond: ((_spe.equipment_id = "outer".equipment_id) AND (_spe.date_id = 
"outer".date_id))
                                                                           ->  Index 
Scan using mda_equ_ele__pkey on mda_equ_ele _ele  (cost=0.00..3.03 rows=1 width=8) 
(actual time=0.025..0.025 rows=1 loops=1215)
                                                                                 Index 
Cond: (_ele.element_id = "outer".element_id)
                                                                     ->  Materialize  
(cost=1.14..1.28 rows=14 width=8) (actual time=0.000..0.016 rows=14 loops=1215)
                                                                           ->  Seq 
Scan on mda_equ_gro _gro  (cost=0.00..1.14 rows=14 width=8) (actual time=0.000..0.000 
rows=14 loops=1)
                                                               ->  Index Scan using 
mda_mak_mod_equ__pkey on mda_mak_mod_equ _peq  (cost=0.00..2235.90 rows=24627 
width=263) (actual time=0.000..0.000 rows=1 loops=1)
                                                         ->  Index Scan using 
mda_mak_mod_equ_dat__equipment_id__idx on mda_mak_mod_equ_dat _ped  (cost=0.00..3.10 
rows=2 width=29) (actual time=0.000..0.000 rows=0 loops=1215)
                                                               Index Cond: 
(_ped.equipment_id = "outer".equipment_id)
                                                   ->  Index Scan using 
mda_mak_mod_equ_ava__equipment_id_date_id__idx on mda_mak_mod_equ_ava _ava  
(cost=0.00..5.83 rows=1 width=74) (actual time=0.033..0.049 rows=3 loops=1215)
                                                         Index Cond: 
((_ava.equipment_id = "outer".equipment_id) AND (_ava.date_id = "outer".date_id))
 Total runtime: 2203.000 ms
(54 rows)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to