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