pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gregory Stark <gsstark@mit.edu>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: best statistic target for boolean columns
Date: Mon, 27 Sep 2004 15:26:32 -0400
Message-ID: <3481.1096313192@sss.pgh.pa.us> (raw)
In-Reply-To: <87k6uf7ceu.fsf@stark.xeocode.com>
References: <87k6uf7ceu.fsf@stark.xeocode.com>

Gregory Stark <gsstark@mit.edu> 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.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <3481.1096313192@sss.pgh.pa.us>
Permalink:  ../3481.1096313192@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3481.1096313192@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, gsstark@mit.edu
  Subject: Re: best statistic target for boolean columns
  In-Reply-To: <3481.1096313192@sss.pgh.pa.us>

* 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