Feedback-ID: i0c21471d:Fastmail
User-Agent: Cyrus-JMAP/3.11.0-alpha0-491-g033e30d24-fm-20240520.001-g033e30d2
MIME-Version: 1.0
Message-Id: <0754dc58-c0b9-4193-a48a-ca031da37147@app.fastmail.com>
In-Reply-To: 
 <171708424943.2021730.15027954353131825880@wrigleys.postgresql.org>
References: 
 <171708424943.2021730.15027954353131825880@wrigleys.postgresql.org>
Date: Mon, 03 Jun 2024 17:15:25 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: eve.fritz@qonto.com, pgsql-docs@lists.postgresql.org
Subject: Re: 28.4.4. Progress Reporting phase status
Content-Type: multipart/alternative;
 boundary=9f72c29aabd7443c8533acdcb67d9781
Archived-At: 
 <https://www.postgresql.org/message-id/0754dc58-c0b9-4193-a48a-ca031da37147%40app.fastmail.com>
Precedence: bulk

--9f72c29aabd7443c8533acdcb67d9781
Content-Type: text/plain

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/

--9f72c29aabd7443c8533acdcb67d9781
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Thu, May 30,=
 2024, at 12:50 PM, PG Doc comments form wrote:<br></div><blockquote typ=
e=3D"cite" id=3D"qt" style=3D""><div>I noticed that in "28.4.4. Progress=
 Reporting" chapter for<br></div><div>`pg_stat_progress_create_index`, t=
he&nbsp; "Table 28.43. CREATE INDEX Phases" may<br></div><div>be mislead=
ing as phase displayed for "building index" is often more<br></div><div>=
detailed.&nbsp;<br></div><div>I found in this presentation[1] (slide 20)=
 what seems to be the exact status<br></div><div>displayed by the table =
while creating the index.&nbsp;<br></div><div>Maybe documentation table =
displaying all status could be extended to display<br></div><div>the fol=
lowing status:<br></div></blockquote><div><br></div><div>The description=
 is accurate. Since this step (building index) is AM-specific, it<br></d=
iv><div>shouldn't contain the additional information (after the semicolo=
n).<br></div><div><br></div><blockquote type=3D"cite" id=3D"qt" style=3D=
""><div>- building index: initializing [2]<br></div><div>- building inde=
x: scanning table<br></div><div>- building index: sorting live tuples<br=
></div><div>- building index: sorting dead tuples<br></div><div>- buildi=
ng index: loading tuples in tree<br></div></blockquote><div><br></div><d=
iv>This is the B-tree build phases. Although, the other access methods (=
such as<br></div><div>Hash, Gin, GiST, BRIN) do not provide a function t=
o report the current building<br></div><div>phase, it might be added in =
the future. I'm not sure if it is worth adding such<br></div><div>inform=
ation here. You can certainly obtain the build phases from all access<br=
></div><div>methods with a query like:<br></div><div><br></div><div>WITH=
 amidx AS (<br></div><div>SELECT oid, amname FROM pg_am WHERE amtype =3D=
 'i')<br></div><div>SELECT a.amname, pg_indexam_progress_phasename(a.oid=
, i)<br></div><div>FROM amidx a, generate_series(0, 100) i<br></div><div=
>WHERE pg_indexam_progress_phasename(a.oid, i) IS NOT NULL<br></div><div=
>ORDER BY a.amname, i;<br></div><div><br></div><div><br></div><div id=3D=
"sig112409080"><div class=3D"signature">--<br></div><div class=3D"signat=
ure">Euler Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp; <a =
href=3D"https://www.enterprisedb.com/">https://www.enterprisedb.com/</a>=
<br></div></div><div><br></div></body></html>
--9f72c29aabd7443c8533acdcb67d9781--