Re: Auto-analyse on insert operations

On Wed, 4 Nov 2015 14:32:37 +0100
Bertrand Roos <bertrand(dot)roos(at)areal(dot)fr> wrote:
>
> I try to configure auto-analyse task with postgresql 9.4.
> I have the following configuration (default configuration):
> track_counts = on
> autovacuum = on
> log_autovacuum_min_duration = -1
> autovacuum_max_workers = 3
> autovacuum_naptime = 300s
> autovacuum_vacuum_threshold = 50
> autovacuum_analyze_threshold = 50
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.2
> autovacuum_freeze_max_age = 200000000
> autovacuum_multixact_freeze_max_age = 400000000
> autovacuum_vacuum_cost_delay = 20ms
> autovacuum_vacuum_cost_limit = -1
>
> With this configuration, I can observe that some tables are
> auto-analysed, but some others are not. Even if there are millions of
> insert operations on an empty table (all tables are in cluster mode).
> In fact it seems that tables with update operations are the only ones
> that are auto-analysed.
> I'm quite suprised because the documentation says that daemon check the
> count of insert, update and delete operations.
> What could it be the reason ? Why tables which have only update
> operation, aren't analysed ?
> Are update operations really taken into account ?

Given that autoanalyze is pretty critical to the way the system functions,
it's unlikely that it just doesn't work (someone else would have noticed).

A more likely scenario is that you've found some extremely obscure edge
case. If that's the case, you're going to have to give very specific
details as to how you're testing it before anyone is liable to be able
to help you.

I get the impression that you're somewhat new to Postgres, in which case
it's very likely that the problem is that you're not testing the situation
correctly. In that case, we're going to need specific details on how you're
observing that tables are or are not being analysed.

As a wild-guess theory: the process that does the analyze only wakes up
to check tables every 5 minutes (based on the config you show) ... so are
you doing the inserts then checking the table without leaving enough time
in between for the system to wake up and notice the change?

--
Bill Moran