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 7043053676 for ; Wed, 13 Apr 2005 11:40:58 -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 53555-10 for ; Wed, 13 Apr 2005 14:40:55 +0000 (GMT) Received: from vms048pub.verizon.net (vms048pub.verizon.net [206.46.252.48]) by svr1.postgresql.org (Postfix) with ESMTP id 273CC53675 for ; Wed, 13 Apr 2005 11:40:54 -0300 (ADT) Received: from jfradkin ([63.162.100.127]) by vms048.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEW00GFE3GAWPA3@vms048.mailsrvcs.net> for pgsql-performance@postgresql.org; Wed, 13 Apr 2005 09:40:59 -0500 (CDT) Date: Wed, 13 Apr 2005 10:41:08 -0400 From: "Joel Fradkin" Subject: speed of querry? To: "PostgreSQL Perform" Message-id: <001b01c54036$d49a2290$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=us-ascii 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=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/250 X-Sequence-Number: 11647 I must be missing something important, because I am just not seeing why = this query is slower on a 4 processor 8 gig machine running redhat AS4. The SQL: explain analyze SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, a.firstname, jt.value AS jobtitle, l.name AS "location", l.locationid AS mainlocationid, l.divisionid, l.regionid, l.districtid, (a.lastname::text || ', '::text) || a.firstname::text AS assocname, a.isactive, a.isdeleted FROM tblassociate a left JOIN tbljobtitle jt ON a.jobtitleid =3D jt.id AND = jt.clientnum::text =3D a.clientnum::text AND 1 =3D jt.presentationid JOIN tbllocation l ON a.locationid =3D l.locationid AND = l.clientnum::text =3D a.clientnum::text where a.clientnum =3D 'SAKS';=20 Machine 1 my desktop: "Merge Join (cost=3D74970.51..75975.46 rows=3D8244 width=3D113) (actual time=3D5141.000..6363.000 rows=3D160593 loops=3D1)" " Merge Cond: ("outer".locationid =3D "inner".locationid)" " -> Sort (cost=3D656.22..657.11 rows=3D354 width=3D49) (actual time=3D16.000..16.000 rows=3D441 loops=3D1)" " Sort Key: l.locationid" " -> Index Scan using ix_location on tbllocation l (cost=3D0.00..641.23 rows=3D354 width=3D49) (actual time=3D0.000..0.000 = rows=3D441 loops=3D1)" " Index Cond: ('SAKS'::text =3D (clientnum)::text)" " -> Sort (cost=3D74314.29..74791.06 rows=3D190710 width=3D75) = (actual time=3D5125.000..5316.000 rows=3D160594 loops=3D1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=3D0.00..52366.50 rows=3D190710 = width=3D75) (actual time=3D16.000..1973.000 rows=3D177041 loops=3D1)" " Merge Cond: ((("outer".clientnum)::text =3D ("inner".clientnum)::text) AND ("outer".id =3D "inner".jobtitleid))" " -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt (cost=3D0.00..244.75 rows=3D6622 width=3D37) (actual = time=3D0.000..16.000 rows=3D5690 loops=3D1)" " Filter: (1 =3D presentationid)" " -> Index Scan using ix_tblassoc_jobtitleid on = tblassociate a (cost=3D0.00..50523.83 rows=3D190710 width=3D53) (actual = time=3D0.000..643.000 rows=3D177041 loops=3D1)" " Index Cond: ((clientnum)::text =3D 'SAKS'::text)" "Total runtime: 6719.000 ms" Test Linux machine: "Merge Join (cost=3D48126.04..49173.57 rows=3D15409 width=3D113) = (actual time=3D11832.165..12678.025 rows=3D160593 loops=3D1)" " Merge Cond: ("outer".locationid =3D "inner".locationid)" " -> Sort (cost=3D807.64..808.75 rows=3D443 width=3D49) (actual time=3D2.418..2.692 rows=3D441 loops=3D1)" " Sort Key: l.locationid" " -> Index Scan using ix_location on tbllocation l (cost=3D0.00..788.17 rows=3D443 width=3D49) (actual time=3D0.036..1.677 = rows=3D441 loops=3D1)" " Index Cond: ('SAKS'::text =3D (clientnum)::text)" " -> Sort (cost=3D47318.40..47758.44 rows=3D176015 width=3D75) = (actual time=3D11829.660..12002.746 rows=3D160594 loops=3D1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=3D24825.80..27512.71 rows=3D176015 width=3D75) (actual time=3D8743.848..9750.775 rows=3D177041 loops=3D1)" " Merge Cond: ((("outer".clientnum)::text =3D "inner"."?column10?") AND ("outer".id =3D "inner".jobtitleid))" " -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt (cost=3D0.00..239.76 rows=3D6604 width=3D37) (actual = time=3D0.016..11.323 rows=3D5690 loops=3D1)" " Filter: (1 =3D presentationid)" " -> Sort (cost=3D24825.80..25265.84 rows=3D176015 = width=3D53) (actual time=3D8729.320..8945.292 rows=3D177041 loops=3D1)" " Sort Key: (a.clientnum)::text, a.jobtitleid" " -> Index Scan using ix_associate_clientnum on tblassociate a (cost=3D0.00..9490.20 rows=3D176015 width=3D53) (actual time=3D0.036..1071.867 rows=3D177041 loops=3D1)" " Index Cond: ((clientnum)::text =3D = 'SAKS'::text)" "Total runtime: 12802.019 ms" I tried to remove the left outer thinking it would speed it up, and it = used a seq search on tblassoc and ran 2 times slower. Joel Fradkin =20 Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 =20 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized = review, use, disclosure or distribution is prohibited. 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. =20 =20