How to read query plan - Mailing list pgsql-performance

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.552rows=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=6364loops=1) 

                          Hash Cond: ("outer".addevicessitepartnerelectricitysupplieridfk = "inner".idpk)

                          ->  Hash Left Join  (cost=5606.74..7521.84 rows=6364 width=710) (actual
time=3897.736..7572.182rows=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.713rows=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.976rows=6364 loops=1) 

                                            Join Filter: ("outer".addevicessitepositionidfk = "inner".idpk)

                                            ->  Nested Loop Left Join  (cost=5601.89..6276.01 rows=6364 width=634)
(actualtime=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)
(actualtime=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.022rows=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=6364width=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=6364width=544) (actual time=632.806..2466.030 rows=6364 loops=1) 

                                                                                Hash Cond:
("outer".addevicessitestreetidfk= "inner".idpk) 

                                                                                ->  Hash Left Join
(cost=2324.25..2515.72rows=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.71rows=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=110width=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.78rows=6364 width=492) (actual time=625.108..640.325 rows=6364 loops=1) 

                                                                                                  Sort Key:
addevicessites.addevicessitecountyidfk

                                                                                                  ->  Merge Right Join
(cost=0.00..1006.90rows=6364 width=492) (actual time=0.145..543.043 rows=6364 loops=1) 

                                                                                                        Merge Cond:
("outer".idpk= "inner".addevicessiteregionidfk) 

                                                                                                        ->  Index Scan
usingaddevicessiteregions_pkey on addevicessiteregions  (cost=0.00..3.17 rows=15 width=23) (actual time=0.011..0.031
rows=15loops=1) 

                                                                                                        ->  Index Scan
usingaddevicessites_addevicessiteregionidfk on addevicessites  (cost=0.00..924.14 rows=6364 width=473) (actual
time=0.010..9.825rows=6364 loops=1) 

                                                                                      ->  Hash  (cost=2.24..2.24
rows=124width=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)
(actualtime=0.730..0.730 rows=0 loops=1) 

                                                                          ->  Seq Scan on addevicessitedistricts
(cost=0.00..6.44rows=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.002rows=4 loops=6364) 

                                                        ->  Seq Scan on addevicessitevisibilities  (cost=0.00..1.04
rows=4width=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.002rows=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=5loops=6364) 

                                            ->  Seq Scan on addevicessitestatustypes  (cost=0.00..1.05 rows=5 width=31)
(actualtime=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.61rows=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.075rows=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.120rows=61 loops=1) 

Total runtime: 10811.567 ms


--
Miroslav Šulc