Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 7 Oct 2024 09:41:36 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Guillaume Lelarge <guillaume@lelarge.info>,
	Bertrand Drouvot <bertranddrouvot.pg@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add parallel columns for seq scan and index scan on
 pg_stat_all_tables and _indexes
Message-ID: <ZwMuQHt42IyMDyxD@paquier.xyz>
References: <CAECtzeXXuMkw-RVGTWvHGOJsmFdsRY+jK0ndQa80sw46y2uvVQ@mail.gmail.com>
 <Ztgejz8q7NBdcReD@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeUJ2y1yZcdcHfzkFX=CCYV7RwnHfwhpHyTe-hrzaUgm0g@mail.gmail.com>
 <ZthsUUUU+MTvtKRB@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeXi2b+hc-=xFUSN2phvmenivOz0CO9fi9pLaaObpAVWHA@mail.gmail.com>
 <ZtlDSbo1pxTrKXQv@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeVdwg1tA53RABSMs9wOCAn6ROWo4LfDstA=bistGLMZ2A@mail.gmail.com>
 <CAECtzeXUA-gv6Y5_+-y+LHkcYXuxSFHhKgHeMd9KWRnH+47Ttg@mail.gmail.com>
 <09424f5e-4ddf-4830-a21f-161e0427f112@postgrespro.ru>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="IimKSzunjYZeidT4"
Content-Disposition: inline
In-Reply-To: <09424f5e-4ddf-4830-a21f-161e0427f112@postgrespro.ru>
Archived-At: <https://www.postgresql.org/message-id/ZwMuQHt42IyMDyxD%40paquier.xyz>
Precedence: bulk


--IimKSzunjYZeidT4
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Oct 07, 2024 at 12:43:18AM +0300, Alena Rybakina wrote:
> Maybe I'm not aware of the whole context of the thread and maybe my
> questions will seem a bit stupid, but honestly
> it's not entirely clear to me how this statistics will help to adjust the
> number of parallel workers.
> We may have situations when during overestimation of the cardinality during
> query optimization a several number of parallel workers were unjustifiably
> generated and vice versa -
> due to a high workload only a few number of workers were generated.
> How do we identify such cases so as not to increase or decrease the number
> of parallel workers when it is not necessary?

Well.  For spiky workloads, only these numbers are not going to help.
If you can map them with the number of times a query related to these
tables has been called, something that pg_stat_statements would be
able to show more information about.

FWIW, I have doubts that these numbers attached to this portion of the
system are always useful.  For OLTP workloads, parallel workers would
unlikely be spawned because even with JOINs we won't work with a high
number of tuples that require them.  This could be interested with
analytics, however complex query sequences mean that we'd still need
to look at all the plans involving the relations where there is an
unbalance of planned/spawned workers, because these can usually
involve quite a few gather nodes.  At the end of the day, it seems to
me that we would still need data that involves statements to track
down specific plans that are starving.  If your application does not
have that many statements, looking at individial plans is OK, but if
you have hundreds of them to dig into, this is time-consuming and
stats at table/index level don't offer data in terms of stuff that
stands out and needs adjustments.

And this is without the argument of bloating more the stats entries
for each table, even if it matters less now that these stats are in
shmem lately.
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmcDLkAACgkQnvQgOdby
QH0rYQ//cs1aL3SyQQw17Pa9N6TuGNNnGLqMXhRoJmOtsdGnUaw6wdaKYJ7gIjpt
M8j6OahbyNAbClYvEyoCa6wyE3ogGVX+AyrckcbUeYecDBXVZBxAYg+wxfF+XLnr
k4sFbVezJCd5pe9V/RTKMXzjYprQbVLjlayhSN9Y0lElhfB2bAt5XwSpm4HRnxNV
mxuGveQ1wQ9lqSkgxf/vxV1aiHST4NK7WIygCC2Yz6kc5vDNYbWXZN2MI1zTMmyl
+gtcRzqGK7yv7L8weBG6OqQlcrspsPAOSXYiA/x4RTgji3CNcs5fZQf8zP5SVEqn
EMJiSXtgUSDyFjRyX7ZzbHS1P/uB7JaUJlDn3IF6YRcyZ6GOBv9+o3O0D2ek2Xue
+7NAbzmW7awpB1A0GTaI5LeQTjxHLC0R+mR9weTBdFoK4FjIX4QkfJ3UXDv1qDEd
3DKqdU92kQHrznJYLtOJuPdBgu4T9o8DlFAwLkVMj3/szlREfKMQ1OkzclA94JZr
beP22uTi5U5uk+Ff30r08iWF+EiCaOdg+jCF1W10s/3Sfm9c9cbqlsW3YviQSv15
huuzz6OGUYghApVt70Q2PQc6eB1DF8usjv62IMSaH1CZs3aOeXiYOJygso3CfKQp
Ab+r887/ucgnCAuuHh2SqHZpNl+lGCeKWRJoKI6cJww0Vj/n7Cw=
=zy7d
-----END PGP SIGNATURE-----

--IimKSzunjYZeidT4--