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 46D2F558AC; Mon, 25 Apr 2005 18:11:23 -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 25743-10; Mon, 25 Apr 2005 21:11:10 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id DE1FE55278; Mon, 25 Apr 2005 18:11:09 -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 j3PLAtc0024356; Mon, 25 Apr 2005 17:10:55 -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: <1114454941.21529.245.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> <19276.1114442580@sss.pgh.pa.us> <1114454941.21529.245.camel@localhost.localdomain> Comments: In-reply-to Simon Riggs message dated "Mon, 25 Apr 2005 19:49:01 +0100" Date: Mon, 25 Apr 2005 17:10:55 -0400 Message-ID: <24355.1114463455@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/684 X-Sequence-Number: 12081 Simon Riggs writes: > On Mon, 2005-04-25 at 11:23 -0400, Tom Lane wrote: >> 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. > Assuming you don't use the HashAgg plan, which seems very appropriate > for the task? (...but I understand the plan otherwise). The context here is a case with a very large number of distinct values... keep in mind also that we have to do this for *all* the columns of the table. A full-table scan for each column seems right out to me. regards, tom lane