28.4.4. Progress Reporting phase status

On Thu, May 30, 2024, at 12:50 PM, PG Doc comments form wrote:

I noticed that in "28.4.4. Progress Reporting" chapter for
`pg_stat_progress_create_index`, the "Table 28.43. CREATE INDEX Phases" may
be misleading as phase displayed for "building index" is often more
detailed.
I found in this presentation[1] (slide 20) what seems to be the exact status
displayed by the table while creating the index.
Maybe documentation table displaying all status could be extended to display
the following status:

The description is accurate. Since this step (building index) is AM-specific, it
shouldn't contain the additional information (after the semicolon).

- building index: initializing [2]
- building index: scanning table
- building index: sorting live tuples
- building index: sorting dead tuples
- building index: loading tuples in tree

This is the B-tree build phases. Although, the other access methods (such as
Hash, Gin, GiST, BRIN) do not provide a function to report the current building
phase, it might be added in the future. I'm not sure if it is worth adding such
information here. You can certainly obtain the build phases from all access
methods with a query like:

WITH amidx AS (
SELECT oid, amname FROM pg_am WHERE amtype = 'i')
SELECT a.amname, pg_indexam_progress_phasename(a.oid, i)
FROM amidx a, generate_series(0, 100) i
WHERE pg_indexam_progress_phasename(a.oid, i) IS NOT NULL
ORDER BY a.amname, i;

--
Euler Taveira
EDB https://www.enterprisedb.com/

On 2024-Jun-03, Euler Taveira wrote:

On Thu, May 30, 2024, at 12:50 PM, PG Doc comments form wrote:

I noticed that in "28.4.4. Progress Reporting" chapter for
`pg_stat_progress_create_index`, the "Table 28.43. CREATE INDEX Phases" may
be misleading as phase displayed for "building index" is often more
detailed.
I found in this presentation[1] (slide 20) what seems to be the exact status
displayed by the table while creating the index.
Maybe documentation table displaying all status could be extended to display
the following status:

The description is accurate. Since this step (building index) is AM-specific, it
shouldn't contain the additional information (after the semicolon).

However, maybe Eve is right that we should have these steps:

- building index: initializing [2]
- building index: scanning table
- building index: sorting live tuples
- building index: sorting dead tuples
- building index: loading tuples in tree

somewhere in the AM-specific doc pages,
https://www.postgresql.org/docs/devel/btree.html
and perhaps we can link to that from the progress-report chapter.

(Not really related, but: I think the per-index-AM subsections in
https://www.postgresql.org/docs/devel/indexes-types.html
should have forward-links to the appendix sections on them.)

You can certainly obtain the build phases from all access
methods with a query like:

WITH amidx AS (
SELECT oid, amname FROM pg_am WHERE amtype = 'i')
SELECT a.amname, pg_indexam_progress_phasename(a.oid, i)
FROM amidx a, generate_series(0, 100) i
WHERE pg_indexam_progress_phasename(a.oid, i) IS NOT NULL
ORDER BY a.amname, i;

Heh, only btrees show up there. Sad. I wonder to what extent would it
be useful for the other index AMs to report sub-phases of index
creation. It should be pretty simple to implement ...

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"La gente vulgar sólo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"