X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4630E9FA3A6 for ; Wed, 10 May 2006 19:10:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 37951-06 for ; Wed, 10 May 2006 19:10:52 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 1CC9C9F931E for ; Wed, 10 May 2006 19:10:51 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id CC8BB56435; Wed, 10 May 2006 17:10:50 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 10 May 2006 17:10:48 -0500 Date: Wed, 10 May 2006 17:10:48 -0500 From: "Jim C. Nasby" To: Mario Splivalo Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Lot'sa joins - performance tip-up, please? Message-ID: <20060510221048.GX99570@pervasive.com> References: <1146533274.14006.11.camel@localhost.localdomain> <17793.1146679117@sss.pgh.pa.us> <1146753957.8538.30.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1146753957.8538.30.camel@localhost.localdomain> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060510:msplival@jagor.srce.hr::wMSTinPS9QnpN8GM:000000000000000 000000000000000000000000EmHx X-Hashcash: 1:20:060510:tgl@sss.pgh.pa.us::PKiGNbaMBfDpaXNO:00000000000000000000 000000000000000000000000CDzX X-Hashcash: 1:20:060510:pgsql-performance@postgresql.org::D6Xkqj24YGZVRAMg:00000 0000000000000000000000002U5S X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/276 X-Sequence-Number: 19063 On Thu, May 04, 2006 at 04:45:57PM +0200, Mario Splivalo wrote: Well, here's the problem... > -> Nested Loop (cost=0.00..176144.30 rows=57925 width=26) > (actual time=1074.984..992536.243 rows=57925 loops=1) > -> Seq Scan on ticketing_codes_played > (cost=0.00..863.25 rows=57925 width=8) (actual time=74.479..2047.993 > rows=57925 loops=1) > -> Index Scan using ticketing_codes_pk on > ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual > time=17.044..17.052 rows=1 loops=57925) > Index Cond: (ticketing_codes.code_id = > "outer".code_id) Anyone have any idea why on earth it's doing that instead of a hash or merge join? In any case, try swapping the order of ticketing_codes_played and ticketing_codes. Actually, that'd probably make it worse. Try SET enable_nestloop = off; -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461