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 E6AB33109B6 for ; Tue, 10 Jun 2003 09:12:32 +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 62871-04 for ; Tue, 10 Jun 2003 06:12:22 -0300 (ADT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 8C328310B2A for ; Tue, 10 Jun 2003 05:57:09 -0300 (ADT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h5A8v9l24061 for ; Tue, 10 Jun 2003 14:27:09 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h5A8v8Q24050 for ; Tue, 10 Jun 2003 14:27:09 +0530 From: "Shridhar Daithankar" To: pgsql-performance@postgresql.org Date: Tue, 10 Jun 2003 14:26:17 +0530 MIME-Version: 1.0 Subject: Re: Hash or merge join instead of inner loop Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3EE5EA89.19260.8C8B669@localhost> References: <20030609204009.GJ40542@flake.decibel.org> In-reply-to: <274.1055225711@sss.pgh.pa.us> X-mailer: Pegasus Mail for Windows (v4.02) Content-type: text/plain; charset=ISO-8859-1 Content-description: Mail message body Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from Quoted-printable to 8bit by www.pspl.co.in id h5A8v8Q24050 X-Archive-Number: 200306/29 X-Sequence-Number: 2113 On 10 Jun 2003 at 2:15, Tom Lane wrote: > 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.) Just being naïve here, but if planner and executor account for shared buffers+effective OS cache, even a boolean choice could be a start. Say a query needs 100MB of data according to estimates so if shared buffers+effective OS cache covers that, we can lower the cost. May be we should have two config. parameters for tuple cost? Disk read tuple cost and memory read tuple cost. Later being 1/10th of former? Bye Shridhar -- All new: Parts not interchangeable with previous model.