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