Date: Mon, 18 Oct 2021 20:53:52 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>,
	"nikolai.berkoff" <nikolai.berkoff@pm.me>,
	"pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Subject: Re: vacuumdb --analyze-in-stages
Message-ID: <202110182353.6lbwp4redjd2@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKFQuwbZjSji05khF1dH5_v36PhcuaAjasdM7Ohk47bzpi8inA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202110182353.6lbwp4redjd2%40alvherre.pgsql>
Precedence: bulk

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/