bad query performance - Mailing list pgsql-sql

Hi everybody,<br /><br /> I'm having some trouble optimizing the performance of a query on my web log system. The
tableis 1,6 millions records ( secu.logs_et table).<br /><br /> Here is the query :<br /><font face="Arial, Helvetica"
size="1">------------------------------------------------------------------------------------<br/></font><font
color="#0000FF"face="Arial, Helvetica" size="1">SELECT</font><font face="Arial, Helvetica" size="1">
profiles_et.username,profiles_et.name, profiles_et.firstname, profiles_et.email, company_et.name </font><font
color="#008000"face="Arial, Helvetica" size="1">AS</font><font face="Arial, Helvetica" size="1"> company,
count(logs_et.dt)</font><font color="#008000" face="Arial, Helvetica" size="1">AS</font><font face="Arial, Helvetica"
size="1">cnt, pro_invoice_addr_at.id <br /><br /><br /></font><font color="#008000" face="Arial, Helvetica"
size="1">FROM</font><fontface="Arial, Helvetica" size="1"><br /> (<br />      (<br />                  (<br />
                          secu.company_et  JOIN secu.pro_invoice_addr_at </font><font color="#008000" face="Arial,
Helvetica"size="1">ON</font><font face="Arial, Helvetica" size="1"> (pro_invoice_addr_at.company = company_et.id)<br />
                ) <br />                <br />                JOIN secu.profiles_et </font><font color="#008000"
face="Arial,Helvetica" size="1">ON</font><font face="Arial, Helvetica" size="1"> (pro_invoice_addr_at.profile =
profiles_et.id)<br/>      ) <br />     JOIN  secu.logs_et </font><font color="#008000" face="Arial, Helvetica"
size="1">ON</font><fontface="Arial, Helvetica" size="1"> (logs_et.invaddr = pro_invoice_addr_at.id)<br /> )<br /><br
/><br/>  </font><font color="#FF0000" face="Arial, Helvetica" size="1">GROUP</font><font face="Arial, Helvetica"
size="1"></font><font color="#008000" face="Arial, Helvetica" size="1">BY</font><font face="Arial, Helvetica" size="1">
profiles_et.username,profiles_et.name, profiles_et.firstname, profiles_et.email, company_et.name,
pro_invoice_addr_at.id<br /><br /></font><font color="#008000" face="Arial, Helvetica" size="1">ORDER</font><font
face="Arial,Helvetica" size="1"> </font><font color="#008000" face="Arial, Helvetica" size="1">BY</font><font
face="Arial,Helvetica" size="1"> count(logs_et.dt) DESC;<br />
------------------------------------------------------------------------------------<br/><br /><br /><br /></font>The
secu_company,secu_prov_invoice_addr_at and secu_profiles contains only informations about the users.<br /><br /> The
queryis executing in <b>8,6 minutes</b>.... Indexes are defined on all the field used in the different joins.<br /><br
/>Here is the query plan :<br /><br />
------------------------------------------------------------------------------------<br/><font face="Arial, Helvetica"
size="1">Sort (cost=895649.54..896073.23 rows=169474 width=145)<br />   Sort Key: count(logs_et.dt)<br />   -> 
</font><fontcolor="#FF0000" face="Arial, Helvetica" size="1">Aggregate</font><font face="Arial, Helvetica" size="1"> 
(cost=831240.24..865135.10rows=169474 width=145)<br />         ->  </font><font color="#FF0000" face="Arial,
Helvetica"size="1">Group</font><font face="Arial, Helvetica" size="1">  (cost=831240.24..860898.24 rows=1694743
width=145)<br/>               ->  Sort  (cost=831240.24..835477.10 rows=1694743 width=145)<br />                    
SortKey: profiles_et.username, profiles_et.name, profiles_et.firstname, profiles_et.email, company_et.name,
pro_invoice_addr_at.id<br/>                     ->  Merge Join  (cost=274406.73..304066.75 rows=1694743
width=145)<br/>                           Merge Cond: ("outer".id = "inner".invaddr)<br />                          
-> Sort  (cost=143.81..144.92 rows=446 width=126)<br />                                 Sort Key:
pro_invoice_addr_at.id<br/>                                 ->  Merge Join  (cost=90.27..124.18 rows=446
width=126)<br/>                                       Merge Cond: ("outer".id = "inner".profile)<br />
                                     ->  </font><font color="#FF0000" face="Arial, Helvetica"
size="1">Index</font><fontface="Arial, Helvetica" size="1"> Scan </font><font color="#008000" face="Arial, Helvetica"
size="1">using</font><fontface="Arial, Helvetica" size="1"> profiles_pk </font><font color="#008000" face="Arial,
Helvetica"size="1">on</font><font face="Arial, Helvetica" size="1"> profiles_et  (cost=0.00..24.98 rows=449
width=66)<br/>                                       ->  Sort  (cost=90.27..91.39 rows=446 width=60)<br />
                                           Sort Key: pro_invoice_addr_at.profile<br />
                                           ->  Merge Join  (cost=37.82..70.65 rows=446 width=60)<br />
                                                 Merge Cond: ("outer".company = "inner".id)<br />
                                                 ->  </font><font color="#FF0000" face="Arial, Helvetica"
size="1">Index</font><fontface="Arial, Helvetica" size="1"> Scan </font><font color="#008000" face="Arial, Helvetica"
size="1">using</font><fontface="Arial, Helvetica" size="1"> invaddr_at_company_idx </font><font color="#008000"
face="Arial,Helvetica" size="1">on</font><font face="Arial, Helvetica" size="1"> pro_invoice_addr_at  (cost=0.00..24.68
rows=446width=33)<br />                                                   ->  Sort  (cost=37.82..38.55 rows=291
width=27)<br/>                                                         Sort Key: company_et.id<br />
                                                       ->  Seq Scan </font><font color="#008000" face="Arial,
Helvetica"size="1">on</font><font face="Arial, Helvetica" size="1"> company_et  (cost=0.00..25.91 rows=291 width=27)<br
/>                          ->  Sort  (cost=274262.92..278499.78 rows=1694743 width=19)<br />
                               Sort Key: logs_et.invaddr<br />                                 ->  Seq Scan
</font><fontcolor="#008000" face="Arial, Helvetica" size="1">on</font><font face="Arial, Helvetica" size="1"> logs_et 
(cost=0.00..55404.43rows=1694743 width=19)<br
/></font>------------------------------------------------------------------------------------<br/><br /><font
face="Arial,Helvetica" size="1">The computer is a Pentium III 850 Mhz with 256 mb RAM (Sort memory for postres is 16
mb,and 64 mb of shared memory).<br /><br /><br /> Do someone have any idea on how to speed up the query ? I can give
anymore details about the system if needed.<br /><br /> Thanks a lot in advance<br /><br /> Didrik Pinte<br /><br /><br
/></font><br/> ,"**** DISCLAIMER ****<br /> This e-mail and any attachments thereto may contain information<br /> which
isconfidential and/or protected by intellectual property<br /> rights and are intended for the sole use of the
recipient(s)named above.<br /> Any use of the information contained herein (including, but not limited to,<br /> total
orpartial reproduction, communication or distribution in any form)<br /> by persons other than the designated
recipient(s)is prohibited.<br /> If you have received this e-mail in error, please notify the sender<br /> and delete
thematerial from any computer.<br /> Thank you for your cooperation.