Re: vacuumdb --analyze-in-stages

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Euler Taveira <euler(at)eulerto(dot)com>, "nikolai(dot)berkoff" <nikolai(dot)berkoff(at)pm(dot)me>, "pgsql-docs(at)lists(dot)postgresql(dot)org" <pgsql-docs(at)lists(dot)postgresql(dot)org>
Subject: Re: vacuumdb --analyze-in-stages
Date: 2021-10-18 23:53:52
Message-ID: 202110182353.6lbwp4redjd2@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

On 2021-Oct-18, David G. Johnston wrote:

> On Mon, Oct 18, 2021 at 4:02 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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/

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Euler Taveira 2021-10-19 00:02:08 Re: vacuumdb --analyze-in-stages
Previous Message David G. Johnston 2021-10-18 23:34:25 Re: vacuumdb --analyze-in-stages