Re: Detailed progress reporting for "vacuuming indexes" stage

On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov
<alexanderad(at)gmail(dot)com> wrote:
> There are 7 stages for standard VACUUM process which is reportable via
> this view. For time consuming stages like "scanning heap" or
> "vacuuming heap" you are able to get stage progress idea by doing:
>
> (heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100%
>
> However stage "vacuuming indexes" does not give any idea of how long
> this stage might take. We have some rather big (500M+ records) with
> 5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and
> all progress reporting we have for stage 3 is that it is stage 3.

Yes, things could be improved here.

> According to code
> (https://github.com/postgres/postgres/blob/1d25779284fe1ba08ecd57e647292a9deb241376/src/backend/commands/vacuumlazy.c#L708-L712)
> looks like there might be a way to report number of indexes left to be
> vacuumed.

Indexes may vary in size. For example if a table has a bunch of BRIN
indexes and one huge btree most of the time would be spent on the
btree part. So the number would not make much sense.

> The question is: is it a right thing to expect a patch which adds more
> detailed stage 3 reporting? Say index number being vacuumed.

What would be more interesting would be like the heap to get
information on the index block being cleaned up with reports being
done via index_bulk_delete(). That's more work, and that would be more
helpful than just a number.

So patches welcome.
--
Michael