X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id B6DF0311030 for ; Tue, 10 Jun 2003 08:13:58 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 53193-08 for ; Tue, 10 Jun 2003 05:13:45 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 58E4630F881 for ; Tue, 10 Jun 2003 03:15:16 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5A6FBU6000275; Tue, 10 Jun 2003 02:15:11 -0400 (EDT) To: jim@nasby.net Cc: pgsql-performance@postgresql.org Subject: Re: Hash or merge join instead of inner loop In-reply-to: <20030609204009.GJ40542@flake.decibel.org> References: <20030609204009.GJ40542@flake.decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Mon, 09 Jun 2003 15:40:09 -0500" Date: Tue, 10 Jun 2003 02:15:11 -0400 Message-ID: <274.1055225711@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/28 X-Sequence-Number: 2112 "Jim C. Nasby" writes: > I have a query that's cauing pgsql choose either a hash or merge join > depending on how I mess with the stats variables, but it won't choose an > nested loop, even though it's the fastest. There's been some discussion about that before; you could check the archives (now that they're up again ;-)). I believe that the planner overestimates the cost of a nestloop with inner indexscan, because it costs the indexscans as though each one is an independent ab-initio index search. In reality, most of the upper btree levels will no doubt stay in memory during such a query, and so this estimate charges many more reads than really occur. Fixing this is on the todo list, but no one's got to it yet. (It's not clear to me how to put the consideration into the planner's cost algorithms in a clean way.) regards, tom lane