Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 20 Nov 2024 07:07:16 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: "Andrey M. Borodin" <x4mmm@yandex-team.ru>,
	Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Sample rate added to pg_stat_statements
Message-ID: <Zz0MFPq1s4WFxxpL@paquier.xyz>
References: <fe99e0ca-e564-480e-b865-5f0cee30bc60@tantorlabs.com>
 <c7ce658a-e0eb-4c5f-92a8-511314d89bbb@tantorlabs.com>
 <6707FCA9-FD1A-4609-A1A6-142456C14E0C@yandex-team.ru>
 <CAKAnmmLD8C-Y6CVF74Ccup8h9YpzBYzSvWWRG_Qf8+5YEMh4-g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="okv+DDOfJM6YhplI"
Content-Disposition: inline
In-Reply-To: <CAKAnmmLD8C-Y6CVF74Ccup8h9YpzBYzSvWWRG_Qf8+5YEMh4-g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Zz0MFPq1s4WFxxpL%40paquier.xyz>
Precedence: bulk


--okv+DDOfJM6YhplI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 19, 2024 at 09:39:21AM -0500, Greg Sabino Mullane wrote:
> Oh, and a +1 in general to the patch, OP, although it would also be nice =
to
> start finding the bottlenecks that cause such performance issues.

FWIW, I'm not eager to integrate this proposal without looking at this
exact argument in depth.

One piece of it would be to see how much of such "bottlenecks" we
would be able to get rid of by integrating pg_stat_statements into=20
the central pgstats with the custom APIs, without pushing the module
into core.  This means that we would combine the existing hash of pgss
to shrink to 8 bytes for objid rather than 13 bytes now as the current
code relies on (toplevel, userid, queryid) for the entry lookup (entry
removal is sniped with these three values as well, or dshash seq
scans).  The odds of conflicts still still play in our favor even if
we have a few million entries, or even ten times that.

This would also get rid of the pgss text file for the queries, which
is a source of one of the bottlenecks, as we could just store query
strings upper-bounded based on a postmaster GUC to control the size of
the entries in the pgstats dshash.  More normalization for IN and ANY
clauses would also help a not here, these are a cause of a lot of
bloat.

This integration is not something I will be able to work on for the
PG18 dev cycle as I'm in full review/commit mode for the rest of this
release, but I got some plans for it in PG19 except if somebody beats
me to it.
--
Michael

--okv+DDOfJM6YhplI
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmc9DBQACgkQnvQgOdby
QH3wnw/8D/XygIWPtPgFYpgOV98L5SWyG7lu5bTLp9EaWKM6LSpSyO8tOwYj+VU5
XH471uuKW6QIBeO8LTDSu0vIFOp2kDzIXXlUbPD0TxdLz/eXYnIJgIM1VtQy7xAI
5h7CAcn0ebNz1PHFLdTO1BXdyonM5El8cXS6xECHHgx/k2XvEXsinVgN0TWrJQNJ
GRj1mrGOOiOKxIGhxzIZn5wsBjIrgIhaBnNmLc4mW6SN+pavEva9AM9B3A6TgCTV
/H0Kpb19E7Bt+mfeDdArK5eM1k6z8BE7joJUQoR8gqDRF4HuEn13qJPLAHATwNlR
jm8rT6nqKn5ALX139VSon+63PIdlCbF5JTjynkp36gJ/VPIhl3G8WIeuAEy9ceAN
hY9AS1qqKEY1Mpr+R05hCMiUrAVdqRUDavjcr5eMnI9mdHbx6lYQ+fsRd36y+fbU
XsuLnCgPEejZFINRYxvINqWE94kqQNGS3DHls/BHvT+gQSeLmcFKn1zxn1f0BuH+
EFFoVq/8Y9vNlJ68psvSoJXfaK/xaAH+uGfByoeKUburov+XlV60J0NOe7Rginrt
OhTGrBPIflETP7cXSlGRIBL+ixk3C36AX4ctftFgmIW8g8YAsKEDstU4vpsnnyFo
r3wmvNb0aVK5DT/+U5wt7OiL+9HjCN9v5a/KTAq+tWMpo2nv0MQ=
=IQKy
-----END PGP SIGNATURE-----

--okv+DDOfJM6YhplI--