X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E87565386D for ; Wed, 27 Apr 2005 02:59:54 -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 44511-07 for ; Wed, 27 Apr 2005 05:59:51 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id CE7F553809 for ; Wed, 27 Apr 2005 02:59:50 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DQfa6-0007iQ-00; Wed, 27 Apr 2005 01:59:30 -0400 To: Tom Lane Cc: Rod Taylor , Greg Stark , pgsql-hackers@postgresql.org, Gurmeet Manku Subject: Re: [PERFORM] Bad n_distinct estimation; hacks suggested? References: <87u0lt5efo.fsf@stark.xeocode.com> <1114557011.77587.107.camel@home> <87oec15d9m.fsf@stark.xeocode.com> <1114560992.77587.112.camel@home> <17677.1114575276@sss.pgh.pa.us> In-Reply-To: <17677.1114575276@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Apr 2005 01:59:30 -0400 Message-ID: <87is2869q5.fsf@stark.xeocode.com> Lines: 32 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/883 X-Sequence-Number: 66752 Tom Lane writes: > Rod Taylor writes: > > If when we have partitions, that'll be good enough. If partitions aren't > > available this would be quite painful to anyone with large tables -- > > much as the days of old used to be painful for ANALYZE. > > Yeah ... I am very un-enthused about these suggestions to make ANALYZE > go back to doing a full scan ... Well one option would be to sample only a small number of records, but add the data found from those records to the existing statistics. This would make sense for a steady-state situation, but make it hard to recover from a drastic change in data distribution. I think in the case of n_distinct it would also bias the results towards underestimating n_distinct but perhaps that could be corrected for. But I'm unclear for what situation this is a concern. For most use cases users have to run vacuum occasionally. In those cases "vacuum analyze" would be no worse than a straight normal vacuum. Note that this algorithm doesn't require storing more data because of the large scan or performing large sorts per column. It's purely O(n) time and O(1) space. On the other hand, if you have tables you aren't vacuuming that means you perform zero updates or deletes. In which case some sort of incremental statistics updating would be a good solution. A better solution even than sampling. -- greg