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 CAEC6329F01 for ; Mon, 27 Sep 2004 20:13:53 +0100 (BST) 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 01474-06 for ; Mon, 27 Sep 2004 19:13:47 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 6BBB2329F41 for ; Mon, 27 Sep 2004 20:13:47 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CC0wU-0005YZ-00 for ; Mon, 27 Sep 2004 15:13:46 -0400 To: PostgreSQL Performance List Subject: Re: best statistic target for boolean columns From: Gregory Stark Date: 27 Sep 2004 15:13:45 -0400 Message-ID: <87k6uf7ceu.fsf@stark.xeocode.com> Lines: 28 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.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/291 X-Sequence-Number: 8419 > Gaetano, > > > don't you think the best statistic target for a boolean > > column is something like 2? Or in general the is useless > > have a statistics target > data type cardinality ? > > It depends, really, on the proportionality of the boolean values; if they're > about equal, I certainly wouldn't raise Stats from the default of 10. If, > however, it's very dispraportionate -- like 2% true and 98% false -- then it > may pay to have better statistics so that the planner doesn't assume 50% > hits, which it otherwise might. No, actually the stats table keeps the n most common values and their frequency (usually in percentage). So really a target of 2 ought to be enough for boolean values. In fact that's all I see in pg_statistic; I'm assuming there's a full histogram somewhere but I don't see it. Where would it be? However the target also dictates how large a sample of the table to take. A target of two represents a very small sample. So the estimations could be quite far off. I ran the experiment and for a table with 2036 false rows out of 204,624 the estimate was 1720. Not bad. But then I did vacuum full analyze and got an estimate of 688. Which isn't so good. -- greg