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=12833width=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=3618loops=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.16rows=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=3618loops=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.000rows=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.16rows=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.000rows=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.000rows=3618 loops=1)
-> Nested Loop Left Join (cost=1.14..6189.48 rows=337 width=860)
(actualtime=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)
(actualtime=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=337width=576) (actual time=80.000..891.000 rows=1215 loops=1)
-> Nested Loop Left Join
(cost=0.00..67137.87rows=337 width=572) (actual time=80.000..861.000 rows=1215 loops=1)
-> Nested Loop (cost=0.00..65476.93
rows=337width=560) (actual time=80.000..821.000 rows=403 loops=1)
-> Nested Loop
(cost=0.00..64457.17rows=337 width=552) (actual time=80.000..821.000 rows=403 loops=1)
-> Index Scan using
mda_mak_mod_equ__equipment_parent_id__idxon mda_mak_mod_equ _equ (cost=0.00..3505.21 rows=19472 width=271) (actual
time=10.000..250.000rows=19185 loops=1)
Filter: (("type" <>
'ICO'::bpchar)AND ("type" <> 'OPT'::bpchar))
-> Index Scan using
mda_mak_mod_equ_dat__equipment_id__idxon mda_mak_mod_equ_dat _dat (cost=0.00..3.12 rows=1 width=285) (actual
time=0.023..0.023rows=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__pkeyon mda_mak_mod_equ_dts _arc (cost=0.00..3.01 rows=1 width=12) (actual time=0.000..0.000
rows=1loops=403)
Index Cond: (_arc.date_id
="outer".date_id)
-> Index Scan using
mda_mak_mod_equ_spe__equipment_id_date_id_idxon mda_mak_mod_equ_spe _spe (cost=0.00..4.91 rows=1 width=20) (actual
time=0.025..0.074rows=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)
(actualtime=0.000..0.016 rows=14 loops=1215)
-> Seq Scan on mda_equ_gro _gro
(cost=0.00..1.14rows=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
onmda_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)