Detailed progress reporting for "vacuuming indexes" stage

From: Alexander Shchapov <alexanderad(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Detailed progress reporting for "vacuuming indexes" stage
Date: 2017-01-23 11:32:31
Message-ID: CAOVT141G8wuoSEyjRW2m+LURfByygk6V5jEFcCyx02hBa9YHFA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

We've recently upgraded to 9.6.1 and now playing with newly available
pg_stat_progress_vacuum view.

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.

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.

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

Thanks.

--
Alexander Shchapov

Responses

Browse pgsql-general by date

  From Date Subject
Next Message cen 2017-01-23 15:58:06 FTS prefix search - put full hits first
Previous Message Stephen Frost 2017-01-22 21:37:51 Re: R: Partitioned "views"