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 2A6695398B for ; Thu, 7 Apr 2005 18:14:28 +0100 (BST) 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 45557-01 for ; Thu, 7 Apr 2005 17:14:22 +0000 (GMT) Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by svr1.postgresql.org (Postfix) with ESMTP id 9ED8F537CA for ; Thu, 7 Apr 2005 18:14:21 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEL007OP6JX8LGB@vms044.mailsrvcs.net> for pgsql-performance@postgresql.org; Thu, 07 Apr 2005 12:14:22 -0500 (CDT) Date: Thu, 07 Apr 2005 13:14:33 -0400 From: "Joel Fradkin" Subject: Re: Any way to speed this up? In-reply-to: <11372.1112892168@sss.pgh.pa.us> To: "'Tom Lane'" Cc: "'PostgreSQL Perform'" Message-id: <007b01c53b95$450c5850$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: 7bit 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.382 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/169 X-Sequence-Number: 11566 Here is the result after putting it back to 4 the original value (I had done that prior to your suggestion of using 2 or 3) to see what might change. I also vacummed and thought I saw records deleted in associate, which I found odd as this is a test site and no new records were added or deleted. "Merge Join (cost=86788.09..87945.00 rows=10387 width=112) (actual time=19703.000..21154.000 rows=159959 loops=1)" " Merge Cond: ("outer".locationid = "inner".locationid)" " -> Sort (cost=1245.50..1246.33 rows=332 width=48) (actual time=62.000..62.000 rows=441 loops=1)" " Sort Key: l.locationid" " -> Index Scan using ix_location on tbllocation l (cost=0.00..1231.60 rows=332 width=48) (actual time=15.000..62.000 rows=441 loops=1)" " Index Cond: ('SAKS'::text = (clientnum)::text)" " -> Sort (cost=85542.59..86042.39 rows=199922 width=75) (actual time=19641.000..19955.000 rows=159960 loops=1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=60850.40..62453.22 rows=199922 width=75) (actual time=13500.000..14734.000 rows=176431 loops=1)" " Merge Cond: (("outer".id = "inner".jobtitleid) AND ("outer"."?column4?" = "inner"."?column10?"))" " -> Sort (cost=554.11..570.13 rows=6409 width=37) (actual time=94.000..94.000 rows=6391 loops=1)" " Sort Key: jt.id, (jt.clientnum)::text" " -> Seq Scan on tbljobtitle jt (cost=0.00..148.88 rows=6409 width=37) (actual time=0.000..63.000 rows=6391 loops=1)" " Filter: (1 = presentationid)" " -> Sort (cost=60296.29..60796.09 rows=199922 width=53) (actual time=13406.000..13859.000 rows=176431 loops=1)" " Sort Key: a.jobtitleid, (a.clientnum)::text" " -> Seq Scan on tblassociate a (cost=0.00..38388.79 rows=199922 width=53) (actual time=62.000..10589.000 rows=176431 loops=1)" " Filter: ((clientnum)::text = 'SAKS'::text)" "Total runtime: 22843.000 ms" Joel Fradkin -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, April 07, 2005 11:43 AM To: Joel Fradkin Cc: 'PostgreSQL Perform' Subject: Re: [PERFORM] Any way to speed this up? "Joel Fradkin" writes: > random_page_cost = 1.2#4 # units are one sequential page > fetch cost That is almost certainly overoptimistic; it's causing the planner to use indexscans when it shouldn't. Try 2 or 3 or thereabouts. regards, tom lane