Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 1 Jun 2022 13:37:37 +0900
From: Michael Paquier <michael@paquier.xyz>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Praneel Devisetty <devisettypraneel@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: REINDEXdb performance degrading gradually PG13.4
Message-ID: <YpbtEfXC7X1llLk+@paquier.xyz>
References: 
 <CAHnVB4C2pawGCfNhO6eX5KL8d9BFjw5igWJ0_ogtknsxOLyfYg@mail.gmail.com>
 <CAHnVB4ADj=dzzYYsZMu+pTHgTA0=2NCuKXoXbN3ceJ1dUfetHg@mail.gmail.com>
 <CAKFQuwZvaWqDRjc-p3bTbvT=0+UhGtYUMQJjCei2w7jJ9h0g2g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="CYu2fJ8sNGEJSd9F"
Content-Disposition: inline
In-Reply-To: 
 <CAKFQuwZvaWqDRjc-p3bTbvT=0+UhGtYUMQJjCei2w7jJ9h0g2g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/YpbtEfXC7X1llLk%2B%40paquier.xyz>
Precedence: bulk


--CYu2fJ8sNGEJSd9F
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, May 31, 2022 at 08:42:06AM -0700, David G. Johnston wrote:
> Building indexes is so IO heavy that the non-IO bottlenecks that exists
> likely have minimal impact on the overall times this rebuild everything
> will take.  That said, I=E2=80=99ve never done anything at this scale bef=
ore.  I
> wouldn=E2=80=99t be too surprised if per-session cache effects are coming=
 into play
> given the number of objects involved and the assumption that each session
> used for parallelism is persistent.  I=E2=80=99m not sure how the paralle=
lism works
> for managing the work queue though as it isn=E2=80=99t documented and I h=
aven=E2=80=99t
> inspected the source code.

get_parallel_object_list() in reindexdb.c would give the idea, where
the list of tables to rebuild are ordered based on an "ORDER BY
c.relpages DESC", then the table queue is processed with its own
command, moving on to the next item once we are done with an item in
the list.
--
Michael

--CYu2fJ8sNGEJSd9F
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmKW7REACgkQnvQgOdby
QH0u7g//QRH0/+HIypQGAK7OK+17Df3gEBfSbvbw9CObhMmg7FnjS9XI8Z3D8CDt
425NUzCFMzLQyHNh/jyJNor2YFSavt87yoQVjqeU9gKO78kLURf//Hi4k0lamomG
EWFsFNhFW7T6TFu00HLldyLfdaWi4sB3i+5idllqsVJL/Tj1GCNieo1cOxkXvH2w
pM3otrZE0ehLLT+cVQWVSiUm3GIYERUUVSQsIrIIB1c1+Oi9/+spmoSEx8P84nBl
k5cgtNAdS2XIvXoI2rajsFMj9q9AIFpM9zkp3iW5kcmCENpZgVhJZKBzMO60Ah5G
X9KsX8fkhMpWewRnySdNoIK3cN43ZMfIxMDzrpq+h4R5VPhMBLN/amgXByHYPjmN
Ouk0SG2vO4vdZw22RbDBVgCrBJY8Is6SLW6xUeHB3T+HmPjMvQn2WbL4apX9wvPd
DF7GrZ8QAtUreWL0C94SAoEstchcZrniJ+9w6rL0fIWOYdn57u35iiN2HGo7zL4Z
cJB8FR6lTImj8tViRVYSo6Gf9w6vYmFbihJ4rP28NTWzvUwVZ8o/wRLWfsNdu9wC
1gkds3tWgs+cevQG3WhqGgX+AAsmz23F0JnRbxI4nbiAb5l9ztID/njy23WMAzW1
kpQKwf9s60reEVVa8di9O5BPCNuqnxTkmRwXsRDMXDLwFGt9kMo=
=JOuW
-----END PGP SIGNATURE-----

--CYu2fJ8sNGEJSd9F--