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])
