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