How to make pg_stat_statement merge queries with variable number of parameters in a IN close?

Question

I your application have many queries with variable number of elements in a IN like this :

SELECT * FROM my_table WHERE id IN ($1, $2, $3, $4, ...) -- number of parameters varies from one to thousands

Then pg_stat_statements consider those queries as different but it's one same query. I don't know it is supposed to be usefull in this case.

As workaround I tried :

• Set pg_stat_statements.max to a huge value (like 100.000). Then merge the queries when reading the information. But it's inefficient and waste of resources.
• Rewrite the queries to nest all IDs in one parameter with id_list as (select unnest(string_to_array('1377776,1377792,1377793,1377794,1377795, ...',','))::integer id) select * from my_table join id_list on ma_table.id = id_list.id; but this is not really a solution as it requires to rewrite all queries of the application

Is there a better way to solve this issue ? I like to force pg_stat_statements to merge all parameters inside IN as one.

By the way I like to submit this problem as a feature request. Where can it be done ?

---

Here is the workaround I'm using for those who need it : Increase pg_stat_statements.max=100000 in conf

Then you can create and query this view :

-- PG before 13
create view pg_stat_statements_merged as 
select
regexp_replace(upper(query), ' *\$[0-9]+( *,? *\$[0-9]+)* *', ' ? ', 'g') as query,
sum(calls) as calls,
round(sum(total_time)) as total_exec_time,
min(min_time) as min_exec_time,
max(max_time) as max_exec_time,
sum(total_time)/sum(calls) as mean_exec_time,
sum(stddev_time) as stddev_exec_time,
sum(rows) as rows,
sum(shared_blks_hit) as shared_blks_hit,
sum(shared_blks_read) as shared_blks_read,
sum(shared_blks_dirtied) as shared_blks_dirtied,
sum(shared_blks_written) as shared_blks_written,
sum(local_blks_hit) as local_blks_hit,
sum(local_blks_read) as local_blks_read,
sum(local_blks_dirtied) as local_blks_dirtied,
sum(local_blks_written) as local_blks_written,
sum(temp_blks_read) as temp_blks_read,
sum(temp_blks_written) as temp_blks_written,
sum(blk_read_time) as blk_read_time,
sum(blk_write_time) as blk_write_time
from pg_stat_statements group by regexp_replace(upper(query), ' *\$[0-9]+( *,? *\$[0-9]+)* *', ' ? ', 'g');


-- PG 13+
CREATE OR REPLACE VIEW public.pg_stat_statements_merged
AS SELECT regexp_replace(upper(pg_stat_statements.query), ' *\$[0-9]+( *,? *\$[0-9]+)* *'::text, ' ? '::text, 'g'::text) AS query_merged,
sum(pg_stat_statements.calls) AS calls,
round(sum(pg_stat_statements.total_exec_time)) AS total_exec_time,
min(pg_stat_statements.min_exec_time) AS min_exec_time,
max(pg_stat_statements.max_exec_time) AS max_exec_time,
sum(pg_stat_statements.total_exec_time) / sum(pg_stat_statements.calls)::double precision AS mean_exec_time,
sum(pg_stat_statements.stddev_exec_time) AS stddev_exec_time,
sum(pg_stat_statements.plans) AS plans,
sum(pg_stat_statements.total_plan_time) AS total_plan_time,
min(pg_stat_statements.min_plan_time) AS min_plan_time,
max(pg_stat_statements.max_plan_time) AS max_plan_time,
sum(pg_stat_statements.total_plan_time) / sum(pg_stat_statements.calls)::double precision AS mean_plan_time,
sum(pg_stat_statements.stddev_plan_time) AS stddev_plan_time,
sum(pg_stat_statements.rows) AS rows,
sum(pg_stat_statements.shared_blks_hit) AS shared_blks_hit,
sum(pg_stat_statements.shared_blks_read) AS shared_blks_read,
sum(pg_stat_statements.shared_blks_dirtied) AS shared_blks_dirtied,
sum(pg_stat_statements.shared_blks_written) AS shared_blks_written,
sum(pg_stat_statements.local_blks_hit) AS local_blks_hit,
sum(pg_stat_statements.local_blks_read) AS local_blks_read,
sum(pg_stat_statements.local_blks_dirtied) AS local_blks_dirtied,
sum(pg_stat_statements.local_blks_written) AS local_blks_written,
sum(pg_stat_statements.temp_blks_read) AS temp_blks_read,
sum(pg_stat_statements.temp_blks_written) AS temp_blks_written,
sum(pg_stat_statements.blk_read_time) AS blk_read_time,
sum(pg_stat_statements.blk_write_time) AS blk_write_time,
sum(pg_stat_statements.wal_records) AS wal_records,
sum(pg_stat_statements.wal_fpi) AS wal_fpi,
sum(pg_stat_statements.wal_bytes) AS wal_bytes_m
FROM pg_stat_statements
GROUP BY (regexp_replace(upper(pg_stat_statements.query), ' *\$[0-9]+( *,? *\$[0-9]+)* *'::text, ' ? '::text, 'g'::text));

Edits : Changed many times to improve thanks to users comments.