Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id CBE0B4768FF
	for <pgsql-general@postgresql.org>;
	Fri, 10 May 2002 15:42:07 -0400 (EDT)
Received: from qmsql.netservers.net (hpmail2.netservers.net [209.196.128.168])
	by postgresql.org (Postfix) with SMTP id 325914764AE
	for <pgsql-general@postgresql.org>;
	Fri, 10 May 2002 15:38:46 -0400 (EDT)
Received: (qmail 12538 invoked from network); 10 May 2002 19:38:44 -0000
Received: from unknown (HELO AARON) (208.61.135.80)
	by hpmail2.netservers.net with SMTP; 10 May 2002 19:38:44 -0000
Message-ID: <395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com>
From: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
To: <pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Why very high CPU usage
Date: Fri, 10 May 2002 15:38:11 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_3956_01C1F838.B1092C70"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200205/364
X-Sequence-Number: 24344

This is a multi-part message in MIME format.

------=_NextPart_000_3956_01C1F838.B1092C70
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

I ran a search query against two tables keywords and catalog. Keywords tabl=
e has about 765,000 records and catalog hase about 147,000 records. The que=
ry 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,=
=20
                       ProductMfr, ProductModelName,
                       ProductDescr, ProductURL, ImageURL, Price
 FROM Catalog, Keywords=20
 WHERE  (Catalog.CatalogSysID =3D Keywords.CatalogSysID) and
        ((Keywords.Keywords =3D SearchString1) or
         (Keywords.Keywords =3D SearchString2) or
         (Keywords.Keywords =3D SearchString3) or
         (Keywords.Keywords =3D SearchString4) or
         (Keywords.Keywords =3D SearchString5) or
         (Keywords.Keywords =3D SearchString6) or
         (Keywords.Keywords =3D SearchString7))=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
 GROUP BY VendorName, ProductCategory, ProductMfr, ProductModelName,
          ProductDescr, ProductURL, ImageURL, Price
 ORDER BY CASE WHEN SortOrder =3D ''store'' Then VendorName=20
               WHEN SortOrder =3D ''category'' Then ProductCategory=20
               WHEN SortOrder =3D ''brand'' Then ProductMfr END

Note:
I use PostgreSQL version 7.2 runs on Compaq DL580 (2 CPUs, 2 gigabytes of R=
AM) and Red Hat Linux 7.2.

Postgresql.conf:
max_connections =3D 200
shared_buffers =3D 20000
sort_mem =3D 5120

Thanks in advance for your help.

Regards,
Samuel





------=_NextPart_000_3956_01C1F838.B1092C70
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I ran a search query against two tables ke=
ywords=20
and catalog. Keywords table has about 765,000 records and catalog hase abou=
t=20
147,000 records. The query returns 9,000 records. This query had 70 - 80% o=
f CPU=20
usage.&nbsp; Are there any configuration parameter(s) that I need to check =
and=20
change ?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This is the SQL that I used:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>SELECT COUNT(Catalog.CatalogSysID) as Rati=
ng,=20
VendorName, ProductCategory,=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
ProductMfr,=20
ProductModelName,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
ProductDescr, ProductURL, ImageURL, Price<BR>&nbsp;FROM Catalog, Keywords=
=20
<BR>&nbsp;WHERE&nbsp; (Catalog.CatalogSysID =3D Keywords.CatalogSysID)=20
and<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ((Keywords.Keywords =3D=
=20
SearchString1) or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
(Keywords.Keywords =3D SearchString2)=20
or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Keywords.Keywords =
=3D=20
SearchString3) or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
(Keywords.Keywords =3D SearchString4)=20
or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Keywords.Keywords =
=3D=20
SearchString5) or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
(Keywords.Keywords =3D SearchString6)=20
or<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Keywords.Keywords =
=3D=20
SearchString7))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;GROUP BY VendorName, ProductCategory, ProductMfr,=20
ProductModelName,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
ProductDescr, ProductURL, ImageURL, Price<BR>&nbsp;ORDER BY CASE WHEN SortO=
rder=20
=3D ''store'' Then VendorName=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;=20
WHEN SortOrder =3D ''category'' Then ProductCategory=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;=20
WHEN SortOrder =3D ''brand'' Then ProductMfr END</FONT></DIV></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Note:</FONT></DIV>
<DIV>I use PostgreSQL version 7.2 runs on Compaq DL580 (2 CPUs, 2 gigabytes=
=20
of&nbsp;RAM)&nbsp;and&nbsp;Red Hat Linux 7.2.</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Postgresql.conf:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>max_connections =3D 200<BR></FONT><FONT fa=
ce=3DArial=20
size=3D2>shared_buffers =3D 20000<BR>sort_mem =3D 5120</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance for your help.</FONT></D=
IV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Samuel</DIV>
<DIV><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT>=
&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_3956_01C1F838.B1092C70--