From: Tom Lane <tgl@sss.pgh.pa.us>
To: AlexK987 <alex.cue.987@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: How to tell ANALYZE to collect statistics from the whole table?
In-reply-to: <1422142399144-5835339.post@n5.nabble.com>
References: <1422142399144-5835339.post@n5.nabble.com>
Comments: In-reply-to AlexK987 <alex.cue.987@gmail.com>
 message dated "Sat, 24 Jan 2015 16:33:19 -0700"
Date: Sun, 25 Jan 2015 00:14:41 -0500
Message-ID: <18230.1422162881@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance