Hi all,
I am new to PostgreSQL and query optimizations. We have recently moved our project from MySQL to PostgreSQL and we are having performance problem with one of our most often used queries. On MySQL the speed was sufficient but PostgreSQL chooses time expensive query plan. I would like to optimize it somehow but the query plan from EXPLAIN ANALYZE is little bit cryptic to me.
So the first thing I would like is to understand the query plan. I have read "performance tips" and FAQ but it didn't move me too much further.
I would appreciate if someone could help me to understand the query plan and what are the possible general options I can test. I think at this moment the most expensive part is the "Sort". Am I right? If so, how could I generally avoid it (turning something on or off, using parentheses for JOINs etc.) to force some more efficient query plan?
Thank you for any suggestions.
QUERY PLAN
Merge Right Join (cost=9868.84..9997.74 rows=6364 width=815) (actual time=9982.022..10801.216 rows=6364 loops=1)
Merge Cond: ("outer".idpk = "inner".cadastralunitidfk)-> Index Scan using cadastralunits_pkey on cadastralunits (cost=0.00..314.72 rows=13027 width=31) (actual time=0.457..0.552 rows=63 loops=1)
-> Sort (cost=9868.84..9884.75 rows=6364 width=788) (actual time=9981.405..10013.708 rows=6364 loops=1)
Sort Key: addevicessites.cadastralunitidfk
-> Hash Left Join (cost=5615.03..7816.51 rows=6364 width=788) (actual time=3898.603..9884.248 rows=6364 loops=1)
Hash Cond: ("outer".addevicessitepartnerstickeridfk = "inner".idpk)-> Hash Left Join (cost=5612.27..7718.29 rows=6364 width=762) (actual time=3898.243..9104.791 rows=6364 loops=1)
Hash Cond: ("outer".addevicessitepartnermaintaineridfk =
"inner".idpk)-> Hash Left Join (cost=5609.51..7620.06 rows=6364 width=736) (actual time=3897.996..8341.965 rows=6364 loops=1)
Hash Cond: ("outer".addevicessitepartnerelectricitysupplieridfk
= "inner".idpk)-> Hash Left Join (cost=5606.74..7521.84 rows=6364 width=710) (actual time=3897.736..7572.182 rows=6364 loops=1)
Hash Cond:
("outer".addevicessitepartneridentificationoperatoridfk = "inner".idpk)-> Nested Loop Left Join (cost=5603.98..7423.62 rows=6364 width=684) (actual time=3897.436..6821.713 rows=6364 loops=1)
Join Filter: ("outer".addevicessitestatustypeidfk =
"inner".idpk)-> Nested Loop Left Join (cost=5602.93..6706.61 rows=6364 width=657) (actual time=3897.294..6038.976 rows=6364 loops=1)
Join Filter:
("outer".addevicessitepositionidfk = "inner".idpk)-> Nested Loop Left Join (cost=5601.89..6276.01 rows=6364 width=634) (actual time=3897.158..5303.575 rows=6364 loops=1)
Join Filter:
("outer".addevicessitevisibilityidfk = "inner".idpk)-> Merge Right Join (cost=5600.85..5702.21 rows=6364 width=602) (actual time=3896.963..4583.749 rows=6364 loops=1)
Merge Cond: ("outer".idpk =
"inner".addevicessitesizeidfk)-> Index Scan using addevicessitesizes_pkey on addevicessitesizes (cost=0.00..5.62 rows=110 width=14) (actual time=0.059..0.492 rows=110 loops=1)
-> Sort (cost=5600.85..5616.76 rows=6364 width=592) (actual time=3896.754..3915.022 rows=6364 loops=1)
Sort Key: addevicessites.addevicessitesizeidfk
-> Hash Left Join (cost=2546.59..4066.81 rows=6364 width=592) (actual time=646.162..3792.310 rows=6364 loops=1)
Hash Cond:
("outer".addevicessitedistrictidfk = "inner".idpk)-> Hash Left Join (cost=2539.29..3964.05 rows=6364 width=579) (actual time=645.296..3142.128 rows=6364 loops=1)
Hash Cond:
("outer".addevicessitestreetdescriptionidfk = "inner".idpk)-> Hash Left Join (cost=2389.98..2724.64 rows=6364 width=544) (actual time=632.806..2466.030 rows=6364 loops=1)
Hash Cond:
("outer".addevicessitestreetidfk = "inner".idpk)-> Hash Left Join (cost=2324.25..2515.72 rows=6364 width=518) (actual time=626.081..1822.137 rows=6364 loops=1)
Hash Cond:
("outer".addevicessitecityidfk = "inner".idpk)-> Merge Right Join (cost=2321.70..2417.71 rows=6364 width=505) (actual time=625.598..1220.967 rows=6364 loops=1)
Merge
Cond: ("outer".idpk = "inner".addevicessitecountyidfk)
-> Sort (cost=5.83..6.10 rows=110 width=17) (actual time=0.348..0.391
rows=110 loops=1)
Sort Key: addevicessitecounties.idpk
-> Seq Scan on addevicessitecounties (cost=0.00..2.10 rows=110
width=17) (actual time=0.007..0.145 rows=110 loops=1)
-> Sort (cost=2315.87..2331.78 rows=6364 width=492) (actual
time=625.108..640.325 rows=6364 loops=1)
Sort Key: addevicessites.addevicessitecountyidfk
-> Merge Right Join (cost=0.00..1006.90 rows=6364 width=492)
(actual time=0.145..543.043 rows=6364 loops=1)
Merge Cond: ("outer".idpk = "inner".addevicessiteregionidfk)
-> Index Scan using addevicessiteregions_pkey on
addevicessiteregions (cost=0.00..3.17 rows=15 width=23) (actual time=0.011..0.031
rows=15 loops=1)
-> Index Scan using addevicessites_addevicessiteregionidfk
on addevicessites (cost=0.00..924.14 rows=6364 width=473) (actual
time=0.010..9.825 rows=6364 loops=1)-> Hash (cost=2.24..2.24 rows=124 width=17) (actual time=0.238..0.238 rows=0 loops=1)
-> Seq Scan on addevicessitecities (cost=0.00..2.24 rows=124
width=17) (actual time=0.009..0.145 rows=124 loops=1)-> Hash (cost=58.58..58.58 rows=2858 width=34) (actual time=6.532..6.532 rows=0 loops=1)
-> Seq Scan on addevicessitestreets (cost=0.00..58.58 rows=2858 width=34) (actual time=0.040..4.129 rows=2858 loops=1)
-> Hash (cost=96.85..96.85 rows=4585 width=43) (actual time=11.786..11.786 rows=0 loops=1)
-> Seq Scan on addevicessitestreetdescriptions (cost=0.00..96.85 rows=4585 width=43) (actual time=0.036..7.290 rows=4585 loops=1)
-> Hash (cost=6.44..6.44 rows=344 width=21) (actual time=0.730..0.730 rows=0 loops=1)
-> Seq Scan on addevicessitedistricts (cost=0.00..6.44 rows=344 width=21) (actual time=0.027..0.478 rows=344 loops=1)
-> Materialize (cost=1.04..1.08 rows=4 width=36) (actual time=0.000..0.002 rows=4 loops=6364)
-> Seq Scan on addevicessitevisibilities (cost=0.00..1.04 rows=4 width=36) (actual time=0.036..0.050 rows=4 loops=1)
-> Materialize (cost=1.03..1.06 rows=3 width=27) (actual time=0.001..0.002 rows=3 loops=6364)
-> Seq Scan on addevicessitepositions (cost=0.00..1.03 rows=3 width=27) (actual time=0.013..0.017 rows=3 loops=1)
-> Materialize (cost=1.05..1.10 rows=5 width=31) (actual time=0.000..0.002 rows=5 loops=6364)
-> Seq Scan on addevicessitestatustypes (cost=0.00..1.05 rows=5 width=31) (actual time=0.012..0.019 rows=5 loops=1)
-> Hash (cost=2.61..2.61 rows=61 width=34) (actual time=0.171..0.171 rows=0 loops=1)
-> Seq Scan on partneridentifications partneridentificationsoperator (cost=0.00..2.61 rows=61 width=34) (actual time=0.027..0.126 rows=61 loops=1)
-> Hash (cost=2.61..2.61 rows=61 width=34) (actual time=0.130..0.130 rows=0 loops=1)
-> Seq Scan on partners partnerselectricitysupplier (cost=0.00..2.61 rows=61 width=34) (actual time=0.003..0.076 rows=61 loops=1)
-> Hash (cost=2.61..2.61 rows=61 width=34) (actual time=0.118..0.118 rows=0 loops=1)
-> Seq Scan on partners partnersmaintainer (cost=0.00..2.61 rows=61 width=34) (actual time=0.003..0.075 rows=61 loops=1)
-> Hash (cost=2.61..2.61 rows=61 width=34) (actual time=0.171..0.171 rows=0 loops=1)
-> Seq Scan on partners partnerssticker (cost=0.00..2.61 rows=61 width=34) (actual time=0.029..0.120 rows=61 loops=1)
Total runtime: 10811.567 ms
-- Miroslav Šulc
begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
