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 7508C54073 for ; Fri, 15 Apr 2005 19:38:22 -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 45233-05 for ; Fri, 15 Apr 2005 22:38:21 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1E8A753A2F for ; Fri, 15 Apr 2005 19:38:20 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3FMc9QN008325; Fri, 15 Apr 2005 18:38:09 -0400 (EDT) To: "Joel Fradkin" Cc: "'Dave Held'" , "'PERFORM'" Subject: Re: speed of querry? In-reply-to: <000001c541e7$133f3f10$797ba8c0@jfradkin> References: <000001c541e7$133f3f10$797ba8c0@jfradkin> Comments: In-reply-to "Joel Fradkin" message dated "Fri, 15 Apr 2005 14:15:15 -0400" Date: Fri, 15 Apr 2005 18:38:09 -0400 Message-ID: <8324.1113604689@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/370 X-Sequence-Number: 11767 "Joel Fradkin" writes: > Turning off merg joins seems to of done it but what do I need to do so I am > not telling the system explicitly not to use them, I must be missing some > setting? > " -> Hash Left Join (cost=185.57..226218.77 rows=177236 width=75) (actual > time=21.147..2221.098 rows=177041 loops=1)" > " Hash Cond: (("outer".jobtitleid = "inner".id) AND > (("outer".clientnum)::text = ("inner".clientnum)::text))" It's overestimating the cost of this join for some reason ... and I think I see why. It's not accounting for the combined effect of the two hash clauses, only for the "better" one. What are the statistics for tbljobtitle.id and tbljobtitle.clientnum --- how many distinct values of each, and are the distributions skewed to a few popular values? regards, tom lane