Re: Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)

Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> writes:
> While looking at this particular case on -general, I realized that there is
> no way to *only* disable auto-analyze on a table. While one can cheat like
> what I suggested to the OP by setting threshold very high, I think it will
> be useful to be able to just off analyze. In this particular case, the OP
> is inserting and then deleting the same rows from the parent table, thus
> keeping it almost empty. Of course, he would want to run auto-vacuum on the
> table to remove the dead rows. Usually auto-analyze would have returned
> quite fast, especially because we vacuum a table first and then analyze it.
> But in this case, since the table is a parent of a number of large child
> tables, we end up analyzing the child tables too, which takes significantly
> longer time and is quite unnecessary because in this case the activity on
> the parent table must not have changed any stats for the child tables.

> A new reloption such as autovacuum_analyze_enabled is what we need.

This seems to me to be a wart that doesn't fix the actual problem ---
the actual problem is to make the autovac daemon smarter about when an
inheritance-tree ANALYZE pass is needed. That should be done somehow
based on the total row churn across the parent + children. Looking
at the parent only, as we do now, can result in analyzing too often
(the OP's case) or too seldom (the much more common case). A manual
"off" switch fixes only the less common case, and requires user
intervention that we'd be better off without.

regards, tom lane