From: Didrik Pinte <dp@adhocsolutions.com>
To: pgsql-sql@postgresql.org
Subject: bad query performance
Date: Mon, 12 May 2003 14:38:15 +0200
Message-ID: <5.2.0.9.0.20030512143014.01bff508@192.168.0.11> (raw)
Hi everybody,
I'm having some trouble optimizing the performance of a query on my web log
system. The table is 1,6 millions records ( secu.logs_et table).
Here is the query :
------------------------------------------------------------------------------------
SELECT profiles_et.username, profiles_et.name, profiles_et.firstname,
profiles_et.email, company_et.name AS company, count(logs_et.dt) AS cnt,
pro_invoice_addr_at.id
FROM
(
(
(
secu.company_et JOIN secu.pro_invoice_addr_at
ON (pro_invoice_addr_at.company = company_et.id)
)
JOIN secu.profiles_et ON (pro_invoice_addr_at.profile =
profiles_et.id)
)
JOIN secu.logs_et ON (logs_et.invaddr = pro_invoice_addr_at.id)
)
GROUP BY profiles_et.username, profiles_et.name, profiles_et.firstname,
profiles_et.email, company_et.name, pro_invoice_addr_at.id
ORDER BY count(logs_et.dt) DESC;
------------------------------------------------------------------------------------
The secu_company, secu_prov_invoice_addr_at and secu_profiles contains only
informations about the users.
The query is executing in 8,6 minutes.... Indexes are defined on all the
field used in the different joins.
Here is the query plan :
------------------------------------------------------------------------------------
Sort (cost=895649.54..896073.23 rows=169474 width=145)
Sort Key: count(logs_et.dt)
-> Aggregate (cost=831240.24..865135.10 rows=169474 width=145)
-> Group (cost=831240.24..860898.24 rows=1694743 width=145)
-> Sort (cost=831240.24..835477.10 rows=1694743 width=145)
Sort Key: profiles_et.username, profiles_et.name,
profiles_et.firstname, profiles_et.email, company_et.name,
pro_invoice_addr_at.id
-> Merge Join (cost=274406.73..304066.75
rows=1694743 width=145)
Merge Cond: ("outer".id = "inner".invaddr)
-> Sort (cost=143.81..144.92 rows=446 width=126)
Sort Key: pro_invoice_addr_at.id
-> Merge Join (cost=90.27..124.18
rows=446 width=126)
Merge Cond: ("outer".id =
"inner".profile)
-> Index Scan using profiles_pk on
profiles_et (cost=0.00..24.98 rows=449 width=66)
-> Sort (cost=90.27..91.39
rows=446 width=60)
Sort Key:
pro_invoice_addr_at.profile
-> Merge
Join (cost=37.82..70.65 rows=446 width=60)
Merge Cond:
("outer".company = "inner".id)
-> Index Scan using
invaddr_at_company_idx on pro_invoice_addr_at (cost=0.00..24.68 rows=446
width=33)
-> Sort
(cost=37.82..38.55 rows=291 width=27)
Sort Key:
company_et.id
-> Seq Scan on
company_et (cost=0.00..25.91 rows=291 width=27)
-> Sort (cost=274262.92..278499.78
rows=1694743 width=19)
Sort Key: logs_et.invaddr
-> Seq Scan on
logs_et (cost=0.00..55404.43 rows=1694743 width=19)
------------------------------------------------------------------------------------
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).
Do someone have any idea on how to speed up the query ? I can give any more
details about the system if needed.
Thanks a lot in advance
Didrik Pinte
,"**** DISCLAIMER ****
This e-mail and any attachments thereto may contain information
which is confidential and/or protected by intellectual property
rights and are intended for the sole use of the recipient(s) named above.
Any use of the information contained herein (including, but not limited to,
total or partial reproduction, communication or distribution in any form)
by persons other than the designated recipient(s) is prohibited.
If you have received this e-mail in error, please notify the sender
and delete the material from any computer.
Thank you for your cooperation.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dp@adhocsolutions.com
Subject: Re: bad query performance
In-Reply-To: <5.2.0.9.0.20030512143014.01bff508@192.168.0.11>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox