Auto Analyze service

To create optimal plans for queries, the query planner needs accurate and up-to-date statistics related to tables and their columns. These statistics are also used by the YugabyteDB cost-based optimizer (CBO) to create optimal execution plans for queries. To generate the statistics, you run the ANALYZE command. ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog.

Similar to PostgreSQL autovacuum, the YugabyteDB Auto Analyze service automates the execution of ANALYZE commands for any table where rows have changed more than a configurable threshold for the table. This ensures table statistics are always up-to-date.

Enable Auto Analyze

For new universes running v2025.2 or later, Auto Analyze is enabled by default when you deploy using yugabyted, YugabyteDB Anywhere, or YugabyteDB Aeon.

In addition, when upgrading a deployment to v2025.2 or later, if the universe has the cost-based optimizer enabled (on), YugabyteDB will enable Auto Analyze.

You can explicitly enable or disable auto analyze by setting ysql_enable_auto_analyze on all yb-tservers.

For example, to create a single-node yugabyted cluster with Auto Analyze explicitly enabled, use the following command:

./bin/yugabyted start \
    --tserver_flags "ysql_enable_auto_analyze=true"

Configure Auto Analyze

The auto analyze service counts the number of mutations (INSERT, UPDATE, and DELETE) to a table and triggers ANALYZE on the table automatically when certain thresholds are reached. You can configure this behavior using the following settings.

A table needs to accumulate a minimum number of mutations before it is considered for ANALYZE. This minimum is the sum of:

• A fraction of the table size. This is controlled by ysql_auto_analyze_scale_factor. This setting defaults to 0.1, which translates to 10% of the current table size. Current table size is determined by the reltuples column value stored in the pg_class catalog entry for that table.
• A static count of ysql_auto_analyze_threshold (default 50) mutations. This setting ensures that small tables are not aggressively ANALYZED because the scale factor requirement is easily met.

Separately, Auto Analyze also considers cooldown settings for a table so as to not trigger ANALYZE aggressively. After every run of ANALYZE on a table, a cooldown period is enforced before the next run of ANALYZE on that table, even if the mutation thresholds are met. The cooldown period starts from ysql_auto_analyze_min_cooldown_per_table (default: 10 seconds) and exponentially increases to ysql_auto_analyze_max_cooldown_per_table (default: 24 hours). Cooldown values for a table do not reset. This means that in most cases, it is expected that, after a while, a frequently updated table is only analyzed once every ysql_auto_analyze_max_cooldown_per_table period.

For more information on flags used to configure the Auto Analyze service, refer to Auto Analyze service flags.

Storage configuration parameters

You can configure these settings per-table using ALTER TABLE storage parameters (also known as relopts) (v2025.2.4.0 and later). Per-table storage parameters take precedence over the values specified via flags.

The following table shows the Auto Analyze flags and their corresponding parameter.

For example, to change the scale factor and minimum threshold for a table, you can run the following:

ALTER TABLE tbl1 SET (
  yb_auto_analyze_threshold = 500,
  yb_auto_analyze_scale_factor = 0.05
);

To disable auto analyze for a specific table:

ALTER TABLE unused_table SET(
  yb_auto_analyze_enabled = false
);

Example

With Auto Analyze enabled, try the following SQL statements.

CREATE TABLE test (k INT PRIMARY KEY, v INT);
SELECT reltuples FROM pg_class WHERE relname = 'test';

 reltuples
-----------
        -1
(1 row)

INSERT INTO test SELECT i, i FROM generate_series(1, 100) i;

Wait for few seconds.

SELECT reltuples FROM pg_class WHERE relname = 'test';

 reltuples
-----------
       100
(1 row)

Observability

To inspect what the Auto Analyze service is tracking for each table, use the yb_stat_auto_analyze() function. This is intended for debugging and observability, with one row per table that the service is currently tracking.

SELECT * FROM yb_stat_auto_analyze();

The function returns the following columns.

The last_analyze_info column contains an analyze_history array holding the most recent auto analyze events for the table (oldest first). Each event has the following fields:

• timestamp - Time at which ANALYZE ran, in microseconds since the Unix epoch.
• cooldown - Cooldown period (in microseconds) enforced after that run before the next ANALYZE is allowed. As described in Configure Auto Analyze, this value starts at ysql_auto_analyze_min_cooldown_per_table and increases on each run (by ysql_auto_analyze_cooldown_per_table_scale_factor) up to ysql_auto_analyze_max_cooldown_per_table.

Example

Continuing the previous Example (table test in the public schema), repeatedly inserting rows causes the service to run ANALYZE more than once. Each ANALYZE adds an entry to analyze_history:

SELECT * FROM yb_stat_auto_analyze() WHERE relname = 'test';

 relid | schemaname | relname | mutations |                                              last_analyze_info
-------+------------+---------+-----------+----------------------------------------------------------------------------------------------------------------------------
 16384 | public     | test    |        25 | {"analyze_history": [{"cooldown": 10000000, "timestamp": 1768779881369060}, {"cooldown": 20000000, "timestamp": 1768779901500000}]}
(1 row)

In this example, ANALYZE has run twice. The first run recorded a cooldown of 10 seconds (10000000 microseconds, the default ysql_auto_analyze_min_cooldown_per_table), and the second run doubled it to 20 seconds (20000000 microseconds, using the default scale factor of 2). The mutations value of 25 reflects rows changed since the last ANALYZE that have not yet crossed the threshold for the next run.

Limitations

ANALYZE is technically considered a DDL statement (schema change) and normally conflicts with other concurrent DDLs. However, when run via the auto analyze service, ANALYZE can run concurrently with other DDL. In this case, ANALYZE is pre-empted by concurrent DDL and will be retried at a later point. However, when transactional DDL is enabled (off by default), certain kinds of transactions that contain DDL may face a kConflict error when a background ANALYZE from the auto analyze service interrupts this transaction. In such cases, it is recommended to disable the auto analyze service explicitly and trigger ANALYZE manually. Issue #28903 tracks this scenario.