Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 16 Jul 2025 08:39:29 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Alexander Kukushkin <cyberdemn@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Christoph Berg <myon@debian.org>, Lukas Fittl <lukas@fittl.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	ma lz <ma100@hotmail.com>
Subject: Re: query_id: jumble names of temp tables for better
 pg_stat_statement UX
Message-ID: <aHbmsdI1k12yIFB2@paquier.xyz>
References: <Z-NLelZdH3GaLRNK@paquier.xyz>
 <70BA294B-5A33-4F47-A637-8011C1F279CB@gmail.com>
 <Z-OdsRQUmwtSa_4l@paquier.xyz>
 <CAFh8B==vog16UGpigF5jukg0yZW+MHsvvKG0QBs7gV6cHgqanA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="PIInvNc1NnQ68D8z"
Content-Disposition: inline
In-Reply-To: <CAFh8B==vog16UGpigF5jukg0yZW+MHsvvKG0QBs7gV6cHgqanA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aHbmsdI1k12yIFB2%40paquier.xyz>
Precedence: bulk


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

On Tue, Jul 15, 2025 at 04:48:05PM +0200, Alexander Kukushkin wrote:
> I totally understand the wish to make pg_stat_statements useful for
> workloads that create/drop a ton of temporary tables.
> However, when pursuing this goal we impacted other types of totally valid
> workloads when tables with the same name exist in different schemas.
> Example:
> create schema s1;
> create table s1.t as select id from generate_series(1, 10) as id;
> create schema s2;
> create table s1.t as select id from generate_series(1, 1000000) as id;

I suspect that you mean s2.t and not s1.t here.

> select count(id) from s1.t;
> select count(id) from s2.t;
>
> That is, two different queries, accessing two absolutely different tables
> (one of them has 100000 times more rows!) were merged together.

Yes, we had this argument upthread, and it is still possible to
differentiate both cases by using a different alias in the FROM
clause, as of:
select count(id) from s1.t as t1;
select count(id) from s2.t as t2;

The new behavior where we do not need to worry about temporary tables,
which is not that uncommon because some workloads like using these for
JOIN patterns as a "temporary" anchor in a session, has more benefits
IMO, particularly more if the connections have a rather higher
turnover.
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmh25rEACgkQnvQgOdby
QH3QSRAApASrwWRMLNoEDD/z8hLe3PCb0PBOsGQE09zzcGz9mbRIz985u7iUPfwV
ElqjbU3jQrLCfN8zd7y1xu1kzgdD65TkdJCJ/Kb8d68lfPAi3KXIiAmKPF0yq8Sa
nzL6EeWCULWHzG3OwBD27ifF4nzyzTnDQIbTMurwG5ODeVaJIIjPzoBy2Wx1jkoh
CDmGHXhlEcZGbVMp+FhpBquGzJReVE+1HDpcnM2O72jRrC+k11gqMsMERFr2MQD6
3TuBp3jCnktD2gBQHK1V7evHfnsE/xs5MrHHwiCvMUyFIkPAFUluUcUyYRFycXTn
uhJzM5rMbWjDMjACymRm1mkFuAoKfNvFW3NSvgw45Tw+DvkOFphdaqlMjwJxcNma
3Jq74ZBWNMS7M+PqsRTkZUvakHu0ftITzfV9+Mt6GUSFuPgNTgBXXtfCkZ+1rpum
5O4mHjojYDvDS5m9oT4PNCWaEYRQCZarjtq6a1j3JeW1QYrtRt/ZAvwRN6n6oM5b
NHwy2mAvxR7WVa+dZZTmI2HDjBDgZxsHL5+GW1QKCeYY5FoTZrffv1YsyM3WJah9
xpHGzaSFRwa1ObE0YB7jLXptycJwkaLkquhXOBGTe/UEzz6XeIYmsuttHbRWcYMI
MiYd73QNHKp5WhW9cRGarvnBvEeAt7cPssml+0Y9KiItklc4T2g=
=A/vg
-----END PGP SIGNATURE-----

--PIInvNc1NnQ68D8z--