Re: Report index currently being vacuumed in pg_stat_progress_vacuum - Mailing list pgsql-hackers

Hi,

When VACUUM is in the "vacuuming indexes" or "cleaning up indexes" phase, there is currently no easy way to tell which specific index is being processed. The progress report view shows indexes_total and indexes_processed counters, but not which index is actively being worked on.

This makes it difficult to debug slow or stuck autovacuum workers on tables with multiple indexes of different types (btree, GIN, GiST, BRIN, HNSW, etc.), since one cannot determine which index type or which specific index is causing the delay.

Please find the attached patch adds a new column current_index_relid to pg_stat_progress_vacuum that reports the OID of the index currently being vacuumed or cleaned up. The column is reported for both the "vacuuming indexes" phase and the "cleaning up indexes" phase.

When indexes are being vacuumed in parallel, each parallel worker emits its own row in pg_stat_progress_vacuum with current_index_relid set to the index it is currently processing, and leader_pid pointing to the leader process.

Appreciate any feedback. Thank you!

[1] Example output:

 pid  | datname  | relid | table_name |       phase       | started_by | current_index_relid |  index_name   | leader_pid
------+----------+-------+------------+-------------------+------------+---------------------+---------------+------------
 1420 | postgres | 16395 | vac_test   | vacuuming indexes | autovacuum |               16398 | vac_test_idx1 |          
 1421 | postgres | 16395 | vac_test   | vacuuming indexes |            |               16399 | vac_test_idx2 |       1420
 1423 | postgres | 16395 | vac_test   | vacuuming indexes |            |               16400 | vac_test_idx3 |       1420
(3 rows)

 pid  | datname  | relid | table_name |       phase       | started_by | current_index_relid |  index_name   | leader_pid
------+----------+-------+------------+-------------------+------------+---------------------+---------------+------------
 1346 | postgres | 16395 | vac_test   | vacuuming indexes | manual     |               16398 | vac_test_idx1 |          
(1 row)

[2]
SELECT v.pid, v.datname, v.relid, c.relname AS table_name,
       v.phase, v.started_by, v.current_index_relid,
       COALESCE(ic.relname, '') AS index_name, v.leader_pid
FROM pg_stat_progress_vacuum v
JOIN pg_class c
    ON c.oid = v.relid
LEFT JOIN pg_class ic
    ON ic.oid = v.current_index_relid
WHERE v.relid = $tbl_oid
ORDER BY
    v.leader_pid,
    v.pid;