Re: monitoring CREATE INDEX [CONCURRENTLY]

Hi Alvaro,

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

On 2/23/19 3:24 AM, Alvaro Herrera wrote:
> 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.

1. Thank you for incorporating review comments.
Can you please rebase the latest 0001-Report-progress-of-
CREATE-INDEX-operations.patch on master? Currently it does not apply on 
754b90f657bd54b482524b73726dae4a9165031c
>   15:56:44.694 | building index (3 of 8): initializing (1/5)            |       442478 |      442399 |            0 |           0 |                0 |               0
>   15:56:44.705 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
>   15:56:44.716 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
>   15:56:44.727 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |       79057 |                0 |               0
>   15:56:44.738 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      217018 |                0 |               0
>   15:56:44.75  | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      353804 |                0 |               0
>   
2. In the above report, even though we are reporting progress in terms 
of tuples_done for final btree sort & load phase we have not cleared
the blocks_done entry from previous phases. I think this can be 
confusing as the blocks_done does not correspond to the tuples_done in 
the final btree sort & load phase.

Thank you,
Rahila Syed