Re: monitoring CREATE INDEX [CONCURRENTLY] - Mailing list pgsql-hackers

Hi Alvaro,

Resending the email as earlier one didn't get sent on pgsql-hackers.

On 2019-Feb-13, Amit Langote wrote:

Doesn't the name amphasename sound a bit too generic, given that it can
only describe the phases of ambuild?  Maybe ambuildphase?

Hmm, yeah, maybe it does.  I renamed it "ambuildphasename", since it's
not about reporting the phase itself -- it's about translating the phase
number to the string that's reported to the user.

The attached patch does it that way.  Also, when an index build uses an
AM that doesn't support progress reporting, it no longer reports a NULL
phase name while building.  I also changed it to report the progress of
phase 7 (heap scan validation) using block numbers rather than tuple
counts.  I also tweaked the strings reported in the view.  They're
clearer now IMO.

One slight annoyance is that when parallel workers are used, the last
block number reported in phase 3/subphase 2 (IndexBuildHeapScan stuff)
is not necessarily accurate, since the tail of the table could well be
scanned by a worker that's not the leader, and we only report in the
leader when it gets a new block.

When the AM does not support progress reporting, everything stays as
zeros during the index build phase.  It's easy to notice how slow hash
indexes are to build compared to btrees this way!  Maybe it'd be
better fallback on reporting block numbers in IndexBuildHeapScan when
this happens.  Thoughts?

I added docs to the monitoring section -- that's the bulkiest part of
the patch.

15:56:44.694 | building index (3 of 8): initializing (1/5)            |       442478 |      442399 |            0 |           0 |                0 |               015:56:44.705 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               015:56:44.716 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               015:56:44.727 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |       79057 |                0 |               015:56:44.738 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      217018 |                0 |               015:56:44.75  | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      353804 |                0 |               0