PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How is manual ANALYZE different from being scheduled by autovacuum

We encountered a situation where CPU usage hit 100% due to a frequently running query. The issue resolved after performing a manual ANALYZE on the table involved in the query. However, I noticed the following:

Auto-analyze ran at 11:46 UTC (as confirmed by last_auto_analyze) and had run prior to that as well.
There were no DML operations on the table between 11:46 UTC and the manual ANALYZE at 12:02 UTC.
Despite the recent auto-analyze, CPU usage only dropped after the manual ANALYZE.

If auto-vacuum triggers auto-analyze, and it successfully ran at 11:46 UTC, why didn’t it resolve the high CPU usage? What might explain the difference in behavior between the auto-analyze and the manual ANALYZE in this scenario?

Additional Context:

The query in question is frequently executed.
The database version is 14.
Relevant settings: default settings.

Any insights into why manual ANALYZE worked while auto-analyze didn’t would be greatly appreciated.

We were expecting that auto vacuum will do the auto analyze job and it should update the statistics for the frequent query but CPU went down only after manual analyze.

Tags:

2

Answers


  1. 0

    Commands launched by autovacuum are throttled by default, to not let interference with user commands get out of hand.
    The manual:

    During the execution of VACUUM and ANALYZE commands, the system
    maintains an internal counter that keeps track of the estimated cost
    of the various I/O operations that are performed. When the accumulated
    cost reaches a limit (specified by vacuum_cost_limit), the process
    performing the operation will sleep for a short period of time, as
    specified by vacuum_cost_delay. Then it will reset the counter and
    continue execution.

    Manual ANALYZE (or VACUUM) runs at full speed, so completes faster than autovacuum (with default settings) for big tables.

    Plus, after scanning tables (also customizable), autovacuum only actually launches ANALYZE after a certain number of written tuples, which is governed by autovacuum_analyze_threshold and autovacuum_vacuum_scale_factor.

    Still, unless your table is big, ANALYZE is not that expensive and should finish in timely manner. If your evidence is just the one case, then don’t draw premature conclusions. Column statistics are based on random samples, resulting estimates fluctuate along a normal distribution. While ANALYZE typically improves statistics, there is no strict guarantee.

    Login or Signup to reply.
  2. 0

    If there were really no data modifications, there are few possible explanations, since the effects of an automatic and a manual ANALYZE are identical.

    Possible explanations are:

    1. The row sample that ANALYZE takes is randomized. It is possible that the good plan and the bad plan have almost the same cost, and the random sample can tip the scales this way or that.

      You can figure out if that is the case by manually running ANALYZE several times. If performance is sometimes bad after ANALYZE and sometimes bad, you may have found the explanation.

    2. Perhaps you didn’t perform a DML operation, but a DDL operation like CREATE INDEX on an expression or CREATE STATISTICS. That doesn’t trigger autoanalyze, but the new statistics after a manual ANALYZE can make a difference.

    3. Perhaps the table is partitioned. Autoanalyze doesn’t ever process partitioned tables (only the partitions), and the added information can make a difference to the planner.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search