Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> 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!

This problem seems to be similar to what I noticed when workign on the REPACK
command: progress reporting of index build needs to be disabled if the build
is part of REPACK, otherwise the index build can overwrite the counters of
REPACK (whether the overwriting actually happens or not is another question).

The solution I suggest is to allow progress tracking of a "sub-command" - see
the attached patch. Wouldn't that also resolve your problem? (My plan is to
incorporate this in the series of REPACK enhancements soon.)

--
Antonin Houska
Web: https://www.cybertec-postgresql.com