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 53B8E32A002 for ; Mon, 27 Sep 2004 23:45:59 +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 68156-10 for ; Mon, 27 Sep 2004 22:45:39 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id CA682329F45 for ; Mon, 27 Sep 2004 23:45:40 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i8RMjbfg073826 for ; Mon, 27 Sep 2004 22:45:37 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i8RMgLG8072876 for pgsql-performance@postgresql.org; Mon, 27 Sep 2004 22:42:21 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: best statistic target for boolean columns Date: Tue, 28 Sep 2004 00:42:06 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 49 Message-ID: <4158973E.7010408@bigfoot.com> References: <87k6uf7ceu.fsf@stark.xeocode.com> <3481.1096313192@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Tom Lane User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: <3481.1096313192@sss.pgh.pa.us> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org 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/301 X-Sequence-Number: 8429 Tom Lane wrote: > Gregory Stark writes: > >>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? > > > It's not going to be there. The histogram only covers values that are > not in the most-frequent-values list, and therefore it won't exist for a > column that is completely describable by most-frequent-values. > > >>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. > > > Right. The real point of stats target for such columns is that it > determines how many rows to sample, and thereby indirectly implies > the accuracy of the statistics. For a heavily skewed boolean column > you'd want a high target so that the number of occurrences of the > infrequent value would be estimated accurately. > > It's also worth noting that the number of rows sampled is driven by the > largest per-column stats target in the table, and so reducing stats > target to 2 for a boolean column will save *zero* effort unless all the > columns in the table are booleans. Thank you all, now I have more clear how it works. Btw last time I was thinking: why during an explain analyze we can not use the information on about the really extracted rows vs the extimated rows ? Now I'm reading an article, written by the same author that ispired the magic "300" on analyze.c, about "Self-tuning Histograms". If this is implemented, I understood we can take rid of "vacuum analyze" for mantain up to date the statistics. Have someone in his plans to implement it ? After all the idea is simple: compare during normal selects the extimated rows and the actual extracted rows then use this "free" information to refine the histograms. Regards Gaetano Mendola