Re: monitoring CREATE INDEX [CONCURRENTLY]

Hi Alvaro,

> 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
>

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 current
phase.

--
Rahila Syed
Performance Engineer
2ndQuadrant
http://www.2ndQuadrant.com <http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services