MIME-Version: 1.0
References: <Z-NLelZdH3GaLRNK@paquier.xyz> <70BA294B-5A33-4F47-A637-8011C1F279CB@gmail.com>
 <Z-OdsRQUmwtSa_4l@paquier.xyz> <CAFh8B==vog16UGpigF5jukg0yZW+MHsvvKG0QBs7gV6cHgqanA@mail.gmail.com>
 <aHbmsdI1k12yIFB2@paquier.xyz>
In-Reply-To: <aHbmsdI1k12yIFB2@paquier.xyz>
From: Alexander Kukushkin <cyberdemn@gmail.com>
Date: Wed, 16 Jul 2025 08:20:39 +0200
Message-ID: <CAFh8B=m5BPeo7vTYDSw2KV=CmjeATWGw-CHCZmJ+3ZKm7kVM1w@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="000000000000f429dc063a05e45b"
Archived-At: <https://www.postgresql.org/message-id/CAFh8B%3Dm5BPeo7vTYDSw2KV%3DCmjeATWGw-CHCZmJ%2B3ZKm7kVM1w%40mail.gmail.com>
Precedence: bulk

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

On Wed, 16 Jul 2025 at 01:39, Michael Paquier <michael@paquier.xyz> wrote:

>
> > 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.
>

Yes.


> 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.


Yes, I've seen this argument and know that aliases will make these queries
look different.
However, we regularly hear from many different customers that they *don't
control queries* sent by application or *can't modify these queries*.
Such kinds of workloads are also not that uncommon and this change makes it
impossible to monitor them.

I would somewhat understand when a table in the query is used without
specifying schema and such queries are merged together:
s1: SET search_path s1; select count(*) from t;
s2: SET search_path s2; select count(*) from t;

But, even this case doesn't feel right, because these tables are still
different and therefore queries.

Regards,
--
Alexander Kukushkin

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 16 Jul =
2025 at 01:39, Michael Paquier &lt;<a href=3D"mailto:michael@paquier.xyz">m=
ichael@paquier.xyz</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><br>
&gt; create schema s1;<br>
&gt; create table s1.t as select id from generate_series(1, 10) as id;<br>
&gt; create schema s2;<br>
&gt; create table s1.t as select id from generate_series(1, 1000000) as id;=
<br>
<br>
I suspect that you mean s2.t and not s1.t here.<br></blockquote><div><br></=
div><div>Yes.=C2=A0</div><div>=C2=A0</div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">
Yes, we had this argument upthread, and it is still possible to<br>
differentiate both cases by using a different alias in the FROM<br>
clause, as of:<br>
select count(id) from s1.t as t1;<br>
select count(id) from s2.t as t2;<br>
<br>
The new behavior where we do not need to worry about temporary tables,<br>
which is not that uncommon because some workloads like using these for<br>
JOIN patterns as a &quot;temporary&quot; anchor in a session, has more bene=
fits<br>
IMO, particularly more if the connections have a rather higher<br>
turnover.</blockquote><div><br></div><div>Yes, I&#39;ve seen this argument =
and know that aliases will make these queries look different.</div><div>How=
ever, we regularly hear from many different=C2=A0customers that they *don&#=
39;t control queries* sent by application or *can&#39;t modify these querie=
s*.</div></div><div><span class=3D"gmail_signature_prefix">Such kinds of wo=
rkloads are also not that uncommon and this change makes it impossible to m=
onitor them.</span></div><div><span class=3D"gmail_signature_prefix"><br></=
span></div><div><span class=3D"gmail_signature_prefix">I would somewhat und=
erstand when a table in the query is used without specifying schema and suc=
h queries are merged together:</span></div><div><span class=3D"gmail_signat=
ure_prefix">s1: SET search_path s1; select count(*) from t;</span></div><di=
v><span class=3D"gmail_signature_prefix">s2: SET search_path s2; select cou=
nt(*) from t;</span></div><div><span class=3D"gmail_signature_prefix"><br><=
/span></div><div><span class=3D"gmail_signature_prefix">But, even this case=
 doesn&#39;t feel right, because these tables are still different and there=
fore queries.</span></div><div><span class=3D"gmail_signature_prefix"><br><=
/span></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><di=
v>Regards,</div><div>--</div><div>Alexander Kukushkin<br></div></div></div>=
</div>

--000000000000f429dc063a05e45b--