Re: support fix query_id for temp table

Re: Michael Paquier
> On Thu, Feb 01, 2024 at 07:37:32AM +0000, ma lz wrote:
> > session 1:
> > create temp table ttt ( a int );
> > insert into ttt values(3); -- query_id is XXX from pg_stat_activity
> >
> > session 2:
> > create temp table ttt ( a int );
> > insert into ttt values(3); -- query_id is YYY from pg_stat_activity
> >
> > I know temp table has different oid, so query_id is different, is
> > there a way to use table name for temp table instead of oid?
>
> The CREATE TABLE statements have indeed the same query ID (in 16~),
> and the inserts have a different one as they touch different schemas
> and relations. That's quite an old problem, that depends on the
> RangeVar attached to an InsertStmt. I don't quite see a way to
> directly handle that except by using a custom implementation in query
> jumbling for this node and its RangeVar, so there is no "easy" way to
> tackle that :/

A customer reported that pg_stat_statements is not useful for them
because they are seeing 160k different query ids in 6-8 hours. They
also proposed to use the temp table name for query jumbling and wrote
a patch for it, which I would also see as the obvious solution to the
problem.

Here's that patch with regression tests added. I would think changing
this would be a big usability improvement for anyone using temp tables
a lot.

There does not seem to be a performance impact - all test were run
with pg_stat_statements active:

Standard pgbench -S (-s 10):
without patch: tps = 154155.407337 (without initial connection time)
with patch: tps = 154223.966534 (without initial connection time)

pgbench -S on temp tables where each table has just one record:
without patch: tps = 184430.801954 (without initial connection time)
with patch: tps = 185692.602764 (without initial connection time)

Christoph