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

On Fri, Feb 1, 2013 at 9:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> writes:

>> 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 ---

IMHO this case is just an example, but I'm sure there would be similar
such examples which may not involve inheritance. For example, say user
has a very large table which is updated very frequently but not in a
way that his query plans will be affected. The user may want to turn
auto analyze in such cases. And given that we allow the user to
control all other parameters, I don't understand why we would not let
him turn it off completely.

There is another problem that I noticed while looking at this case.
The analyze took close to 500sec on a fairly good hardware (40GB RAM,
10K rpm disks on RAID10) because many large child tables were scanned
at once. We analyze all of them in a single transaction. This long
running transaction will cause a lot of bloat for heavily updated
tables since HOT will fail to keep up. I wonder if we should set up
the child tables in the tableoid_list just like we do for toast tables
so that each table is analyzed in its own transaction. This is also
important because partitioning will typically involve very large
tables.

Of course, if we could ever run analyze on a single table in multiple
smaller transactions, that will be even better. But I'm not sure if
thats feasible.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee