X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0E35A53AA1; Mon, 25 Apr 2005 12:23:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12025-08; Mon, 25 Apr 2005 15:23:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6A49E537F4; Mon, 25 Apr 2005 12:23:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3PFN1kR019277; Mon, 25 Apr 2005 11:23:01 -0400 (EDT) To: Simon Riggs Cc: josh@agliodbs.com, Greg Stark , Marko Ristola , pgsql-perform , pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested? In-reply-to: <1114419467.21529.210.camel@localhost.localdomain> References: <200504191209.05181.josh@agliodbs.com> <200504221336.08325.josh@agliodbs.com> <8764ydapky.fsf@stark.xeocode.com> <200504231639.11897.josh@agliodbs.com> <1114419467.21529.210.camel@localhost.localdomain> Comments: In-reply-to Simon Riggs message dated "Mon, 25 Apr 2005 09:57:47 +0100" Date: Mon, 25 Apr 2005 11:23:00 -0400 Message-ID: <19276.1114442580@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/676 X-Sequence-Number: 12073 Simon Riggs writes: > My suggested hack for PostgreSQL is to have an option to *not* sample, > just to scan the whole table and find n_distinct accurately. > ... > What price a single scan of a table, however large, when incorrect > statistics could force scans and sorts to occur when they aren't > actually needed ? It's not just the scan --- you also have to sort, or something like that, if you want to count distinct values. I doubt anyone is really going to consider this a feasible answer for large tables. regards, tom lane