MIME-Version: 1.0
References: <Z-NLelZdH3GaLRNK@paquier.xyz> <70BA294B-5A33-4F47-A637-8011C1F279CB@gmail.com>
 <Z-OdsRQUmwtSa_4l@paquier.xyz>
In-Reply-To: <Z-OdsRQUmwtSa_4l@paquier.xyz>
From: Alexander Kukushkin <cyberdemn@gmail.com>
Date: Tue, 15 Jul 2025 16:48:05 +0200
Message-ID: <CAFh8B==vog16UGpigF5jukg0yZW+MHsvvKG0QBs7gV6cHgqanA@mail.gmail.com>
Subject: Re: query_id: jumble names of temp tables for better
 pg_stat_statement UX
To: Michael Paquier <michael@paquier.xyz>
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>
Content-Type: multipart/alternative; boundary="000000000000d621420639f8dd15"
Archived-At: <https://www.postgresql.org/message-id/CAFh8B%3D%3Dvog16UGpigF5jukg0yZW%2BMHsvvKG0QBs7gV6cHgqanA%40mail.gmail.com>
Precedence: bulk

--000000000000d621420639f8dd15
Content-Type: text/plain; charset="UTF-8"

Hi,

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;
select count(id) from s1.t;
select count(id) from s2.t;

select * from pg_stat_statements;
userid                     | 10
dbid                       | 5
toplevel                   | t
queryid                    | -8317141500049987426
query                      | select count(id) from s1.t
plans                      | 0
total_plan_time            | 0
min_plan_time              | 0
max_plan_time              | 0
mean_plan_time             | 0
stddev_plan_time           | 0
calls                      | 2
total_exec_time            | 22.577107
min_exec_time              | 0.325021
max_exec_time              | 22.252086000000002
mean_exec_time             | 11.2885535
stddev_exec_time           | 10.963532500000001
rows                       | 2
shared_blks_hit            | 4425

That is, two different queries, accessing two absolutely different tables
(one of them has 100000 times more rows!) were merged together.

Regards,
--
Alexander Kukushkin

--000000000000d621420639f8dd15
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hi,</div><div><br></div><div>I total=
ly understand the wish to make pg_stat_statements useful for workloads that=
 create/drop a ton of temporary tables.</div><div>However,=C2=A0when pursui=
ng this goal we impacted other types of totally valid workloads when tables=
 with the same name exist in different schemas.</div><div>Example:</div><di=
v>create schema s1;</div><div>create table s1.t as select id from generate_=
series(1, 10) as id;</div><div></div><div>create schema s2;<br>create table=
 s1.t as select id from generate_series(1, 1000000) as id;</div><div>select=
 count(id) from s1.t;</div><div>select count(id) from s2.t;</div><div><br><=
/div><div>select * from pg_stat_statements;<br>userid =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 10<br>dbid =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 5<br>topl=
evel =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | t<br>=
queryid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| -8317141500049987426<br>query =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| select count(id) from s1.t<br>plans=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| 0<br>total_plan_time =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0<br>m=
in_plan_time =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0<br>max_pla=
n_time =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0<br>mean_plan_tim=
e =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0<br>stddev_plan_time =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0<br>calls =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2<br>total_exec_time =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 22.577107<br>min_exec_time =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.325021<br>max_exec_time =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 22.252086000000002<br>mean_exec_time =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 11.2885535<br>stddev_exec_time =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 10.963532500000001<br>rows =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2<br>s=
hared_blks_hit =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 4425<br></div><di=
v><br></div><div>That is, two different queries, accessing two absolutely d=
ifferent tables (one of them has 100000 times more rows!) were merged toget=
her.</div><div><span class=3D"gmail_signature_prefix">=C2=A0</span></div></=
div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Regard=
s,</div><div>--</div><div>Alexander Kukushkin<br></div></div></div></div>

--000000000000d621420639f8dd15--