How to read query plan

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