From: Samuel J. Sutjiono <ssutjiono@wc-group.com>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Why very high CPU usage
Date: Fri, 10 May 2002 15:38:11 -0400
Message-ID: <395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com> (raw)
Hello,
I ran a search query against two tables keywords and catalog. Keywords table has about 765,000 records and catalog hase about 147,000 records. The query returns 9,000 records. This query had 70 - 80% of CPU usage. Are there any configuration parameter(s) that I need to check and change ?
This is the SQL that I used:
SELECT COUNT(Catalog.CatalogSysID) as Rating, VendorName, ProductCategory,
ProductMfr, ProductModelName,
ProductDescr, ProductURL, ImageURL, Price
FROM Catalog, Keywords
WHERE (Catalog.CatalogSysID = Keywords.CatalogSysID) and
((Keywords.Keywords = SearchString1) or
(Keywords.Keywords = SearchString2) or
(Keywords.Keywords = SearchString3) or
(Keywords.Keywords = SearchString4) or
(Keywords.Keywords = SearchString5) or
(Keywords.Keywords = SearchString6) or
(Keywords.Keywords = SearchString7))
GROUP BY VendorName, ProductCategory, ProductMfr, ProductModelName,
ProductDescr, ProductURL, ImageURL, Price
ORDER BY CASE WHEN SortOrder = ''store'' Then VendorName
WHEN SortOrder = ''category'' Then ProductCategory
WHEN SortOrder = ''brand'' Then ProductMfr END
Note:
I use PostgreSQL version 7.2 runs on Compaq DL580 (2 CPUs, 2 gigabytes of RAM) and Red Hat Linux 7.2.
Postgresql.conf:
max_connections = 200
shared_buffers = 20000
sort_mem = 5120
Thanks in advance for your help.
Regards,
Samuel
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: ssutjiono@wc-group.com, pgsql-general@postgresql.org
Subject: Re: Why very high CPU usage
In-Reply-To: <395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com>
* 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