pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)  latest in thread

Message-ID: <87k6uf7ceu.fsf@stark.xeocode.com>
Permalink:  ../87k6uf7ceu.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87k6uf7ceu.fsf@stark.xeocode.com

 · 

reply

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