X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D0BEED1E947 for ; Fri, 16 Apr 2004 13:46:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84539-10 for ; Fri, 16 Apr 2004 13:46:42 -0300 (ADT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with SMTP id 141F8D1E8AD for ; Fri, 16 Apr 2004 13:46:45 -0300 (ADT) Received: (qmail 50223 invoked by uid 1001); 16 Apr 2004 16:46:44 -0000 Date: Fri, 16 Apr 2004 11:46:44 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Horribly slow hash join Message-ID: <20040416164644.GH87362@nasby.net> References: <20040416154502.GF87362@nasby.net> <450.1082133251@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <450.1082133251@sss.pgh.pa.us> X-Operating-System: FreeBSD 4.9-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/216 X-Sequence-Number: 6516 Yes, stats are up to date, and the values should be fairly unique. Combined with the hash aggregate problem I saw (see my other email to the list), do you think there could be some issue with the performance of the hash function on FreeBSD 5.2 on AMD64? I'll post the table you requested someplace you can grab it. On Fri, Apr 16, 2004 at 12:34:11PM -0400, Tom Lane wrote: > "Jim C. Nasby" writes: > > Note the time for the hash join step: > > Have you ANALYZEd these tables lately? > > It looks to me like it's hashing on some column that has only a small > number of distinct values, so that the hash doesn't actually help to > avoid comparisons. The planner should know better than to choose such > a plan, but if it's working with obsolete stats ... > > regards, tom lane > -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"