pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org, Dawid Kuroczko <qnex42@gmail.com>
Subject: Re: When to bump up statistics?
Date: Fri, 19 Nov 2004 12:32:59 -0800
Message-ID: <200411191232.59600.josh@agliodbs.com> (raw)
In-Reply-To: <758d5e7f04111905594cf9c9f8@mail.gmail.com>
References: <758d5e7f04111905594cf9c9f8@mail.gmail.com>

Dawid,

> I wonder what are the implications of using this statement,
> I know by using, say n=100, ANALYZE will take more time,
> pg_statistics will be bigger, planner will take longer time,
> on the other hand it will make better decisions... Etc, etc.

Yep.   And pg_statistics will need to be vacuumed more often.

> Is bumping up statistics is only useful for indexed columns?

No.   It's potentially useful for any queried column.

> 1) huge table with huge number of distinct values (_almost_
>     unique ;))

Yes.

> 2) huge table with relatively equally distributed values
>     (like each value is in between, say, 30-50 rows).

Not usually.

> 3) huge table with unequally distributed values (some
>     values are in 1-5 rows, some are in 1000-5000 rows).

Yes.

> 4) huge table with small number values (around ~100
>     distinct values, equally or uneqally distributed).

Not usually, especially if they are equally distributed.

> 5) boolean column.

Almost never, just as it is seldom useful to index a boolean column.

> I think SET STATISTICS 100 is very useful for case with
> unequally distributed values, but I wonder what about
> the other cases.  And as a side note -- what are the
> reasonable bounds for statistics (between 10 and 100?)

Oh, no, I've used values up to 500 in production, and we've tested up to the 
max on DBT-3.    In my experience, if the default (10) isn't sufficient, you 
often have to go up to > 250 to get a different plan.

> What are the runtime implications of setting statistics
> too large -- how much can it affect queries?

It won't affect select queries.   It will affect ANALYZE time (substantially 
in the aggregate) and maintenance on the pg_statistics table.

> And finally -- how other RDBMS and RDBM-likes deal
> with this issue? :)

Most don't allow such fine-tuned adjustment.   MSSQL, for example, allows only 
setting it per-table or maybe even database-wide, and on that platform it 
doesn't seem to have much effect on query plans.    Oracle prefers to use 
HINTS, which are a brute-force method to manage query plans.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



view thread (3+ messages)

Message-ID: <200411191232.59600.josh@agliodbs.com>
Permalink:  ../200411191232.59600.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200411191232.59600.josh@agliodbs.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: josh@agliodbs.com, qnex42@gmail.com
  Subject: Re: When to bump up statistics?
  In-Reply-To: <200411191232.59600.josh@agliodbs.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