From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: AJ Weber <aweber@comcast.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Analyze and default_statistics_target
Date: Mon, 21 Jan 2013 17:47:09 +0200
Message-ID: <50FD62FD.2080802@vmware.com> (raw)
In-Reply-To: <50FD5EDE.8010002@comcast.net>
References: <CAMxPiKHyJkMAqO62wH6aVzpvpfxhHd_P4pZZRgTCAdxLAnLeCw@mail.gmail.com>
<50FD5EDE.8010002@comcast.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 21.01.2013 17:29, AJ Weber wrote:
> I was under the impression that the default_statistics_target was a
> percentage of rows to analyze. Maybe this is not the case?
Nope.
> I ran an analyze during a "quiet point" last night and for a few of my
> large tables, I didn't get what I consider a reasonable sampling of
> rows. When running with "verbose" enabled, it appeared that a maximum of
> 240000 rows were being analyzed, including on tables exceeding 4-8mm
> rows. My default_statistics_target = 80.
>
> Shouldn't I be analyzing a larger percentage of these big tables?
Analyze only needs a fairly small random sample of the rows in the table
to get a picture of what the data looks like. Compare with e.g opinion
polls; you only need to sample a few thousand people to get a result
with reasonable error bound.
That's for estimating the histogram. Estimating ndistinct is a different
story, and it's well-known that the estimates of ndistinct are sometimes
wildly wrong.
> What is the unit-of-measure used for default_statistics_target?
It's the number of entries stored in the histogram and
most-common-values list in pg_statistics.
See also http://www.postgresql.org/docs/devel/static/planner-stats.html:
"The amount of information stored in pg_statistic by ANALYZE, in
particular the maximum number of entries in the most_common_vals and
histogram_bounds arrays for each column, can be set on a
column-by-column basis using the ALTER TABLE SET STATISTICS command, or
globally by setting the default_statistics_target configuration
variable. The default limit is presently 100 entries."
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: hlinnakangas@vmware.com, aweber@comcast.net
Subject: Re: Analyze and default_statistics_target
In-Reply-To: <50FD62FD.2080802@vmware.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