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

Reply via email to