X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 32503D1B8AC for ; Fri, 29 Aug 2003 07:57:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 01002-03 for ; Fri, 29 Aug 2003 04:57:45 -0300 (ADT) Received: from mail.speakeasy.net (mail7.speakeasy.net [216.254.0.207]) by svr1.postgresql.org (Postfix) with ESMTP id 7C917D1B8BB for ; Fri, 29 Aug 2003 04:57:42 -0300 (ADT) Received: (qmail 16522 invoked from network); 29 Aug 2003 07:57:43 -0000 Received: from unknown (HELO speakeasy.org) (kgeis@[66.93.130.165]) (envelope-sender ) by mail7.speakeasy.net (qmail-ldap-1.03) with AES256-SHA encrypted SMTP for ; 29 Aug 2003 07:57:43 -0000 Message-ID: <3F4F0777.8090503@speakeasy.org> Date: Fri, 29 Aug 2003 00:57:43 -0700 From: Ken Geis User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030612 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruno Wolff III Cc: pgsql-performance@postgresql.org Subject: Re: bad estimates References: <3F4E99F7.7080604@speakeasy.org> <20030829025109.GD4373@wolff.to> <3F4EBF7E.8020602@speakeasy.org> <20030829030156.GF4373@wolff.to> <3F4EC1D0.5010503@speakeasy.org> <20030829033818.GA5577@wolff.to> <3F4ECC78.7090502@speakeasy.org> <20030829040519.GA5746@wolff.to> <3F4ED1DC.50808@speakeasy.org> <20030829042453.GA5931@wolff.to> In-Reply-To: <20030829042453.GA5931@wolff.to> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/476 X-Sequence-Number: 3292 Sorry, all, to wipe out the context, but it was getting a little long. Bruno Wolff III wrote: > I am calling it quits for tonight, but will check back tomorrow > to see how things turned out. I went through the code (7.4 beta2) that estimates the cost of an index scan path. What I need to be sure of is that when running a query in pgsql that uses only the columns that are in an index, the underlying table need not be accessed. I know that Oracle does this. The cost_index function is assuming that after finding an entry in the index it will be looking it up in the underlying table. That table is not well correlated to the index, so it is assuming (in the worst case) a random page lookup for each of 17 million records! In my case, if the underlying table is indeed not touched, the estimated cost is 1000 times the real cost. 63388.624000 to scan the index 67406506.915595 to scan the index and load a random page for each entry Ken