Subject: Re: query_id: jumble names of temp tables for better pg_stat_statement UX

From: Michael Paquier <[email-masked]>
Date: Wed, 26 Mar 2025 15:24:49 +0900

On Tue, Mar 25, 2025 at 07:56:29PM -0500, Sami Imseih wrote:
> FWIW, the pg_stat_statements docs in a few places refer to 
> queries that may look different but have the same meaning 
> as “semantically equivalent”, this is why I used the same 
> terminology here.  But, I have no issue with the simplified
> rewrite either.
> 
> The patch LGTM as well. 

If any adjustments are required, it would be always possible to do
these later.  Anyway, workloads with a lot of temporary tables are
going to benefit from this change, so let's see how it goes.

Applied, after eyeing much more the PGSS dumps from installcheck
before and after the patch, to make sure that I'm not missing
something..
--
Michael

---

From: Alexander Kukushkin <[email-masked]>
Date: Tue, 15 Jul 2025 16:48:05 +0200

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

---

From: Michael Paquier <[email-masked]>
Date: Wed, 16 Jul 2025 08:39:29 +0900

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

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

---

From: Alexander Kukushkin <[email-masked]>
Date: Wed, 16 Jul 2025 08:20:39 +0200

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

Yes.

> IMO, particularly more if the connections have a rather higher

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