X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6274553720 for ; Mon, 11 Apr 2005 14:14:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00499-03 for ; Mon, 11 Apr 2005 17:14:29 +0000 (GMT) Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by svr1.postgresql.org (Postfix) with ESMTP id 4D7055363B for ; Mon, 11 Apr 2005 14:14:28 -0300 (ADT) Received: from jfradkin ([66.15.127.235]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IES00L4NL816QQ1@vms042.mailsrvcs.net> for pgsql-performance@postgresql.org; Mon, 11 Apr 2005 12:14:26 -0500 (CDT) Date: Mon, 11 Apr 2005 13:14:32 -0400 From: "Joel Fradkin" Subject: Is there somthing I need to do on my production server? To: "PostgreSQL Perform" Message-id: <002c01c53eb9$ee07af00$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.26 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/217 X-Sequence-Number: 11614 I am running 8.0.1 on a desktop xp system and a AS4 redhat system. The redhat will be my production server in a week or so and it is = returning slower the my desk top? I understand about the perc cards on the Dell (redhat) but my Dell 2 = proc box runs much faster (MSSQL) then my desktop, so I am wondering if I = messed up Linux or have a postgres config issue.=20 On my desktop (1 proc 2 gigs of memor) I get: "Merge Join=A0 (cost=3D7135.56..7296.25 rows=3D7906 width=3D228) (actual time=3D5281.000..6266.000 rows=3D160593 loops=3D1)" "=A0 Merge Cond: ("outer".locationid =3D "inner".locationid)" "=A0 ->=A0 Sort=A0 (cost=3D955.78..957.07 rows=3D514 width=3D79) (actual time=3D0.000..0.000 rows=3D441 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0 Sort Key: l.locationid" "=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using ix_location on tbllocation = l=A0 (cost=3D0.00..932.64 rows=3D514 width=3D79) (actual time=3D0.000..0.000 = rows=3D441 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ('SAKS'::text =3D = (clientnum)::text)" "=A0 ->=A0 Sort=A0 (cost=3D6179.77..6187.46 rows=3D3076 width=3D173) = (actual time=3D5281.000..5424.000 rows=3D160594 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0 Sort Key: a.locationid" "=A0=A0=A0=A0=A0=A0=A0 ->=A0 Merge Left Join=A0 (cost=3D154.41..6001.57 = rows=3D3076 width=3D173) (actual time=3D94.000..2875.000 rows=3D177041 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Merge Cond: = ((("outer".clientnum)::text =3D "inner"."?column4?") AND ("outer".jobtitleid =3D "inner".id))" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using = ix_tblassoc_jobtitleid on tblassociate a=A0 (cost=3D0.00..5831.49 rows=3D3076 width=3D134) (actual = time=3D0.000..676.000 rows=3D177041 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: = ((clientnum)::text =3D 'SAKS'::text)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Sort=A0 = (cost=3D154.41..154.50 rows=3D34 width=3D67) (actual time=3D78.000..204.000 rows=3D158255 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Sort Key: = (jt.clientnum)::text, jt.id" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq = Scan on tbljobtitle jt=A0 (cost=3D0.00..153.55 rows=3D34 width=3D67) (actual time=3D0.000..31.000 rows=3D6603 = loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 Filter: (1 =3D presentationid)" "Total runtime: 6563.000 ms" On my production (4 proc, 8 gigs of memory) "Merge Join=A0 (cost=3D69667.87..70713.46 rows=3D15002 width=3D113) = (actual time=3D12140.091..12977.841 rows=3D160593 loops=3D1)" "=A0 Merge Cond: ("outer".locationid =3D "inner".locationid)" "=A0 ->=A0 Sort=A0 (cost=3D790.03..791.11 rows=3D433 width=3D49) (actual time=3D2.936..3.219 rows=3D441 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0 Sort Key: l.locationid" "=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using ix_location on tbllocation = l=A0 (cost=3D0.00..771.06 rows=3D433 width=3D49) (actual time=3D0.062..1.981 = rows=3D441 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ('SAKS'::text =3D = (clientnum)::text)" "=A0 ->=A0 Sort=A0 (cost=3D68877.84..69320.17 rows=3D176933 width=3D75) = (actual time=3D12137.081..12305.125 rows=3D160594 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0 Sort Key: a.locationid" "=A0=A0=A0=A0=A0=A0=A0 ->=A0 Merge Right Join=A0 = (cost=3D46271.48..48961.53 rows=3D176933 width=3D75) (actual time=3D9096.623..10092.311 rows=3D177041 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Merge Cond: = ((("outer".clientnum)::text =3D "inner"."?column10?") AND ("outer".id =3D "inner".jobtitleid))" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using = ix_tbljobtitle_id on tbljobtitle jt=A0 (cost=3D0.00..239.76 rows=3D6604 width=3D37) (actual = time=3D0.068..12.157 rows=3D5690 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (1 = =3D presentationid)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Sort=A0 = (cost=3D46271.48..46713.81 rows=3D176933 width=3D53) (actual time=3D9081.546..9295.495 rows=3D177041 loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Sort Key: = (a.clientnum)::text, a.jobtitleid" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq = Scan on tblassociate a=A0 (cost=3D0.00..30849.25 rows=3D176933 width=3D53) (actual time=3D543.931..1674.518 rows=3D177041 = loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 Filter: ((clientnum)::text =3D 'SAKS'::text)" "Total runtime: 13101.402 ms" =A0 I am at a bit of a loss as I would have thought my soon to be production = box should be blowing away my desktop? =A0 Also stupid newb question? I am a bit confused looking at the results of explain analyze. I would have thought the explain analyze select * from viwassoclist = where clientnum =3D'SAKS' Would first limit the result set by clientnum =3D =91SAKS=94 is this the = bottom line? "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq = Scan on tblassociate a=A0 (cost=3D0.00..30849.25 rows=3D176933 width=3D53) (actual time=3D543.931..1674.518 rows=3D177041 = loops=3D1)" "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 Filter: ((clientnum)::text =3D 'SAKS'::text)" which if I understand this (not saying I do) is taking actual time=3D543.931..1674.518 rows=3D177041 loops=3D1 this means 1 loop takes between 543 and 1674 milisecs to return 177041 = rows? And the analyzer thought I would take cost=3D0.00..30849.25? =A0 I am just trying to understand if I can do the sql different to get a = faster result. I am going to try and eliminate my left outer joins and aggregates on = select throughout the app as well as eliminate some unions that exist. =A0 Joel Fradkin =A0 Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel.=A0 941-753-7111 ext 305 =A0 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. =A9 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA,=A0Inc =A0This email message is for the use of the intended recipient(s) and = may contain confidential and privileged information.=A0 Any unauthorized = review, use, disclosure or distribution is prohibited.=A0 If you are not the = intended recipient, please contact the sender by reply email and delete and = destroy all copies of the original message, including attachments. =A0 =A0