如何让pg_stat_statements合并IN子句参数数量可变的查询？

应用中存在大量IN子句内元素数量可变的查询，例如：

SELECT * FROM my_table WHERE id IN ($1, $2, $3, $4, ...) -- 参数数量从1到数千不等

但pg_stat_statements会将这些查询视为不同语句，而实际上它们属于同一类查询。

已尝试的临时解决方案 #

• 将pg_stat_statements.max设置为极大值（如100000），之后在读取数据时合并查询，但这种方式效率低下且浪费资源。
• 重写查询，将所有ID嵌套到单个参数中：

with id_list as (select unnest(string_to_array('1377776,1377792,1377793,1377794,1377795, ...',','))::integer id) 
select * from my_table join id_list on my_table.id = id_list.id;

但这需要重写应用中所有相关查询，并非理想方案。

核心疑问 #

1. 是否有更优的解决方法？希望强制pg_stat_statements将IN子句内的所有参数合并为同一类查询。
2. 想提交该问题作为特性请求，应该在哪里提交？

分享的临时解决方案 #

在配置文件中设置pg_stat_statements.max=100000，然后创建对应视图：

PostgreSQL 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');

PostgreSQL 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));

编辑说明：根据用户反馈多次调整优化。

内容的提问来源于stack exchange，提问作者bokan