Re: How to tell ANALYZE to collect statistics from the whole table? - Mailing list pgsql-performance

From Tom Lane
Subject Re: How to tell ANALYZE to collect statistics from the whole table?
Date
Msg-id 18230.1422162881@sss.pgh.pa.us
Whole thread Raw
In response to How to tell ANALYZE to collect statistics from the whole table?  (AlexK987 <alex.cue.987@gmail.com>)
Responses Re: How to tell ANALYZE to collect statistics from the whole table?  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
AlexK987 <alex.cue.987@gmail.com> writes:
> The documentation states that "The extent of analysis can be controlled by
> adjusting the default_statistics_target configuration variable". It looks
> like I can tell Postgres to create more histograms with more bins, and more
> distinct values. This implicitly means that Postgres will use a larger
> random subset to calculate statistics.

> However, this is not what I want. My data may be quite skewed, and I want
> full control over the size of the sample. I want to explicitly tell Postgres
> to analyze the whole table. How can I accomplish that?

You can't, and you wouldn't want to if you could, because that would
result in slurping the entire table into backend local memory.  All
the rows constituting the "random sample" are held in memory while
doing the statistical calculations.

In practice, the only stat that would be materially improved by taking
enormously large samples would be the number-of-distinct-values estimate.
There's already a way you can override ANALYZE's estimate of that number
if you need to.

            regards, tom lane


pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Initial insert
Next
From: Joe Van Dyk
Date:
Subject: Query performance