From: Gregory Stark <gsstark@mit.edu>
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: best statistic target for boolean columns
Date: 27 Sep 2004 15:13:45 -0400
Message-ID: <87k6uf7ceu.fsf@stark.xeocode.com> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu
Subject: Re: best statistic target for boolean columns
In-Reply-To: <87k6uf7ceu.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox