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?
Date: Sun, 25 Jan 2015 00:14:41 -0500
Message-ID: <18230.1422162881@sss.pgh.pa.us> (raw)
In-Reply-To: <1422142399144-5835339.post@n5.nabble.com>
References: <1422142399144-5835339.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
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: tgl@sss.pgh.pa.us, alex.cue.987@gmail.com
Subject: Re: How to tell ANALYZE to collect statistics from the whole table?
In-Reply-To: <18230.1422162881@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