From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>
Cc: nikolai.berkoff <nikolai.berkoff@pm.me>
Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Subject: Re: vacuumdb --analyze-in-stages
Date: Mon, 18 Oct 2021 20:53:52 -0300
Message-ID: <202110182353.6lbwp4redjd2@alvherre.pgsql> (raw)
In-Reply-To: <CAKFQuwbZjSji05khF1dH5_v36PhcuaAjasdM7Ohk47bzpi8inA@mail.gmail.com>
On 2021-Oct-18, David G. Johnston wrote:
> On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
> > Given that the first stage uses statistic target=1, running this option
> > in a database with any stats at all is probably a bad idea.
>
> Add the word "only"?
>
> This option is only useful to analyze a database...
Maybe this is sufficient, since it would drive people away from trying
to do anything else than help upgrades with it.
> Beyond that maybe adjust the procedure description to include a comment
> that we don't actually skip tables that already have a higher statistics
> target than the current pass would apply. (can we do this?)
Hmm, vacuumdb can certainly query the catalogs to see what we have and
skip tables for which we have more than that number, perhaps with a
query based on this number
select tablename,
max(coalesce(cardinality(histogram_bounds), cardinality(most_common_freqs))) - 1
from pg_stats
group by tablename;
(and of course *don't* skip tables in the final stage, since the stored
stats could be obsolete.)
> "Run several (currently three) stages of analyze with different
> configuration settings, to produce usable statistics faster. The first of
> these stages will remove any existing statistics even if they use a larger
> statistic target configuration."
.. yeah, this is another option.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
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-docs@postgresql.org
Cc: alvherre@alvh.no-ip.org, david.g.johnston@gmail.com, euler@eulerto.com, nikolai.berkoff@pm.me, pgsql-docs@lists.postgresql.org
Subject: Re: vacuumdb --analyze-in-stages
In-Reply-To: <202110182353.6lbwp4redjd2@alvherre.pgsql>
* 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