Track Auto Analyze with yb_stat_auto_analyze() in YugabyteDB

YugabyteDB’s Auto Analyze service helps keep table statistics fresh automatically. That matters because the YSQL planner, including the cost-based optimizer, depends on table and column statistics to choose efficient query plans.

In earlier YugabyteDB versions, checking whether Auto Analyze had run could be a little awkward. You might have needed to check pg_stat_all_tables across every YSQL node returned by yb_servers(), because the visible timestamp could show up on only one node. I covered that method in this earlier tip:

👉 How to Track Auto Analyze in YugabyteDB Across All YSQL Nodes

Starting in YugabyteDB 2025.2.3, per GH #15667, there is a much cleaner option:

				
					yb_stat_auto_analyze()

This function exposes Auto Analyze observability directly from YSQL.

Demo

First, confirm the YugabyteDB version:

				
					SELECT split_part(version(), '-', 3) AS yb_version;

Example output:

				
					.yb_version
------------
2025.2.3.0
(1 row)

Now create a test table and insert enough rows to trigger Auto Analyze:

				
					DROP TABLE IF EXISTS test;

CREATE TABLE test (
    c1 INT PRIMARY KEY
);

INSERT INTO test
SELECT generate_series(1, 1000000);

Check Auto Analyze status:

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

Example output after Auto Analyze has run:

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

Notice two important things:

● The mutations value is now 0.
● The last_analyze_info column has an analyze_history entry.

That means Auto Analyze has already processed the table and recorded a successful analyze event.

What the Columns Mean

Column	Meaning
`relid`	The relation OID for the table being tracked.
`schemaname`	The schema that owns the table.
`relname`	The table name.
`mutations`	The current accumulated mutation count being tracked for Auto Analyze. Mutations include inserts, updates, and deletes. After Auto Analyze runs successfully, this value is expected to reset.
`last_analyze_info`	A JSONB field containing recent successful Auto Analyze history. The most useful value is usually the `timestamp` inside the `analyze_history` array.

Quick note: You may also see pg_catalog rows in the output. That is normal. Creating tables, indexes, and constraints mutates system catalog tables such as pg_class, pg_attribute, pg_index, and pg_depend. For application-table monitoring, filter on your schema.

Convert the Timestamp to Human-Readable Time

The timestamp inside last_analyze_info is stored as a Unix epoch value in microseconds.

That means this value:

				
					1782360682794868

Needs to be divided by 1000000.0 before passing it to to_timestamp().

Here is a query that extracts the Auto Analyze history and converts the timestamp:

				
					WITH auto_analyze_history AS (
    SELECT
        relid,
        schemaname,
        relname,
        mutations,
        jsonb_array_elements(last_analyze_info -> 'analyze_history') AS analyze_event
    FROM yb_stat_auto_analyze()
    WHERE schemaname = 'public'
      AND relname = 'test'
      AND last_analyze_info ? 'analyze_history'
)
SELECT
    schemaname,
    relname,
    mutations,
    (analyze_event ->> 'timestamp')::bigint AS analyze_epoch_us,
    to_timestamp(
        (analyze_event ->> 'timestamp')::double precision / 1000000.0
    ) AS analyzed_at,
    (analyze_event ->> 'cooldown')::bigint AS cooldown
FROM auto_analyze_history
ORDER BY analyzed_at DESC;

Example output:

				
					. schemaname | relname | mutations | analyze_epoch_us |          analyzed_at          | cooldown
------------+---------+-----------+------------------+-------------------------------+----------
 public     | test    |         0 | 1782360682794868 | 2026-06-25 04:11:22.794868+00 | 10000000
(1 row)

If you want to display the timestamp in a specific time zone, use AT TIME ZONE:

				
					WITH auto_analyze_history AS (
    SELECT
        relid,
        schemaname,
        relname,
        mutations,
        jsonb_array_elements(last_analyze_info -> 'analyze_history') AS analyze_event
    FROM yb_stat_auto_analyze()
    WHERE schemaname = 'public'
      AND relname = 'test'
      AND last_analyze_info ? 'analyze_history'
)
SELECT
    schemaname,
    relname,
    mutations,
    to_timestamp(
        (analyze_event ->> 'timestamp')::double precision / 1000000.0
    ) AS analyzed_at_utc,
    to_timestamp(
        (analyze_event ->> 'timestamp')::double precision / 1000000.0
    ) AT TIME ZONE 'America/New_York' AS analyzed_at_new_york
FROM auto_analyze_history
ORDER BY analyzed_at_utc DESC;

Example output:

				
					.schemaname | relname | mutations |        analyzed_at_utc        |    analyzed_at_new_york
------------+---------+-----------+-------------------------------+----------------------------
 public     | test    |         0 | 2026-06-25 04:11:22.794868+00 | 2026-06-25 00:11:22.794868
(1 row)

Show the Latest Auto Analyze Time for User Tables

For day-to-day use, you probably do not want to see system catalog tables. This query filters those out and shows the latest Auto Analyze time for user tables:

				
					WITH auto_analyze_history AS (
    SELECT
        relid,
        schemaname,
        relname,
        mutations,
        jsonb_array_elements(last_analyze_info -> 'analyze_history') AS analyze_event
    FROM yb_stat_auto_analyze()
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
      AND last_analyze_info ? 'analyze_history'
)
SELECT
    schemaname,
    relname,
    mutations,
    to_timestamp(
        (analyze_event ->> 'timestamp')::double precision / 1000000.0
    ) AS analyzed_at,
    (analyze_event ->> 'cooldown')::bigint AS cooldown
FROM auto_analyze_history
ORDER BY analyzed_at DESC;

Pair It with `pg_class.reltuples`

yb_stat_auto_analyze() tells you what Auto Analyze has tracked and when it last recorded analyze history.

If you also want to confirm that the planner’s row-count estimate was refreshed, join the result to pg_class:

				
					WITH latest_auto_analyze AS (
    SELECT
        relid,
        schemaname,
        relname,
        mutations,
        max(
            to_timestamp(
                (analyze_event ->> 'timestamp')::double precision / 1000000.0
            )
        ) AS latest_auto_analyze_at
    FROM (
        SELECT
            relid,
            schemaname,
            relname,
            mutations,
            jsonb_array_elements(last_analyze_info -> 'analyze_history') AS analyze_event
        FROM yb_stat_auto_analyze()
        WHERE last_analyze_info ? 'analyze_history'
    ) h
    GROUP BY
        relid,
        schemaname,
        relname,
        mutations
)
SELECT
    n.nspname AS schemaname,
    c.relname,
    c.reltuples::bigint AS planner_estimated_rows,
    a.mutations,
    a.latest_auto_analyze_at
FROM pg_class c
JOIN pg_namespace n
  ON n.oid = c.relnamespace
LEFT JOIN latest_auto_analyze a
  ON a.relid = c.oid
WHERE n.nspname = 'public'
  AND c.relname = 'test';

Example output:

				
					.schemaname | relname | planner_estimated_rows | mutations |    latest_auto_analyze_at
------------+---------+------------------------+-----------+-------------------------------
 public     | test    |                1000000 |         0 | 2026-06-25 04:11:22.794868+00
(1 row)

Why This Replaces the Older Method

The earlier tip used a shell script to connect to every YSQL node because pg_stat_all_tables.last_analyze could be populated on only one node, while last_autoanalyze might remain empty.

With yb_stat_auto_analyze(), you no longer need that shell loop just to answer:

● Did Auto Analyze run?
● How many mutations are currently tracked?
● When was the last successful Auto Analyze event recorded?
● The new function gives you that information directly from YSQL.

Practical upgrade: For YugabyteDB 2025.2.3 and later, use yb_stat_auto_analyze() instead of connecting to every YSQL node to hunt for Auto Analyze timestamps in pg_stat_all_tables. Keep pg_class.reltuples in your toolbox when you also want to confirm that planner row estimates were refreshed.

Final Takeaway

yb_stat_auto_analyze() makes Auto Analyze observability much easier in YugabyteDB 2025.2.3 and later.

Use it to see:

● Which tables Auto Analyze is tracking.
● How many mutations have accumulated.
● Whether Auto Analyze has run successfully.
● When the last successful Auto Analyze event was recorded.

Just remember that the timestamp in last_analyze_info is stored in microseconds since the Unix epoch, so divide it by 1000000.0 before converting it with to_timestamp().

Have Fun!