SELECT COUNT(Catalog.CatalogSysID) as Rati= ng,=20 VendorName, ProductCategory,=20
            = ;          =20 ProductMfr,=20 ProductModelName,
         =             &nb= sp;=20 ProductDescr, ProductURL, ImageURL, Price
FROM Catalog, Keywords= =20
WHERE (Catalog.CatalogSysID =3D Keywords.CatalogSysID)=20 and
        ((Keywords.Keywords =3D= =20 SearchString1) or
        =20 (Keywords.Keywords =3D SearchString2)=20 or
         (Keywords.Keywords = =3D=20 SearchString3) or
        =20 (Keywords.Keywords =3D SearchString4)=20 or
         (Keywords.Keywords = =3D=20 SearchString5) or
        =20 (Keywords.Keywords =3D SearchString6)=20 or
         (Keywords.Keywords = =3D=20 SearchString7))          =          =20
            = ;      =20
GROUP BY VendorName, ProductCategory, ProductMfr,=20 ProductModelName,
         = =20 ProductDescr, ProductURL, ImageURL, Price
ORDER BY CASE WHEN SortO= rder=20 =3D ''store'' Then VendorName=20
            = ;  =20 WHEN SortOrder =3D ''category'' Then ProductCategory=20
            = ;  =20 WHEN SortOrder =3D ''brand'' Then ProductMfr END

Thanks in advance for your help.

Regards,

Samuel

------=_NextPart_000_3956_01C1F838.B1092C70-- From josh@agliodbs.com Fri May 10 20:52:45 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id AABBF475973; Fri, 10 May 2002 16:52:45 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 4D51F47595F; Fri, 10 May 2002 16:52:39 -0400 (EDT) Received: from [209.122.114.82] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1456567; Fri, 10 May 2002 13:52:40 -0700 From: "Josh Berkus" Subject: Re: [SQL] Why very high CPU usage To: "Samuel J. Sutjiono" , , X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Fri, 10 May 2002 13:52:40 -0700 Message-ID: In-Reply-To: <395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200205/390 X-Sequence-Number: 24370 Samuel, > 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 ? And this is a problem because .... ? High CPU usage is a *good* thing. Under perfect tuning, Postgres would hit about 97% CPU usage ... for about 0.1 seconds (or less, depending on your processor). The more CPU power used, the faster the query is solved. If you're having performaince problems, I suspect that they are located somewhere other than your CPU. -Josh Berkus From sthomas@townnews.com Fri May 10 21:43:36 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id B6445476923; Fri, 10 May 2002 17:43:36 -0400 (EDT) Received: from hamster.lee.net (leepcC-053.sub-c.lee.net [208.205.126.53]) by postgresql.org (Postfix) with ESMTP id 0C4A0476406; Fri, 10 May 2002 17:42:00 -0400 (EDT) Received: from localhost (localhost.localdomain [127.0.0.1]) by hamster.lee.net (8.12.2/8.12.2) with ESMTP id g4ALfjGS002252; Fri, 10 May 2002 16:41:46 -0500 Date: Fri, 10 May 2002 16:41:45 -0500 (CDT) From: Shaun Thomas X-X-Sender: sthomas@hamster.lee.net To: "Samuel J. Sutjiono" Cc: pgsql-general@postgresql.org, Subject: Re: Why very high CPU usage In-Reply-To: <395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/365 X-Sequence-Number: 24345 On Fri, 10 May 2002, Samuel J. Sutjiono wrote: > This is the SQL that I used: Can't really say much, not knowing the schema you're using. By looking at this query, I can probably guess it isn't pretty. 7 search columns instead of a search table? Putting a sort case in the query itself instead of predetermining the sort order and dynmaically writing the query? That gigantic group-by isn't helping you, either. My guess is that your schema could use an overhaul. We of course are going to ask you the standard questions. I'll list them off, because you asked your question without listing the obvious elements: 1.) Have you vacuumed/analyzed recently? 2.) What kind of indexes are you using? 3.) Can we see a schema to see what kind of column types you're using? 4.) Can you send an explain plan or two? 5.) Did you try turning off sequence scans? Do not expect us to help you with something if you can't give us the proper tools to do so. We're not magical mind-readers. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Administrator | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : www.townnews.com | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ From grant@amadensor.com Fri May 10 21:57:46 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id E8C2D476145; Fri, 10 May 2002 17:57:46 -0400 (EDT) Received: from dbms.amadensor.com (12-254-50-76.client.attbi.com [12.254.50.76]) by postgresql.org (Postfix) with ESMTP id C56F7475AC1; Fri, 10 May 2002 17:57:45 -0400 (EDT) Received: from localhost (grant@localhost) by dbms.amadensor.com (8.11.2/8.11.2/SuSE Linux 8.11.1-0.5) with ESMTP id g4ANJiH04458; Fri, 10 May 2002 16:19:44 -0700 X-Authentication-Warning: dbms.amadensor.com: grant owned process doing -bs Date: Fri, 10 May 2002 16:19:44 -0700 (MST) From: grant To: Shaun Thomas Cc: "Samuel J. Sutjiono" , , Subject: Re: Why very high CPU usage In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/366 X-Sequence-Number: 24346 I have found turning off seq scans to be a mess at times. Instead, I just made them more expensive so that the system is very unlikely to use them. ______________________________________________________________________________ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);