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,
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 view pg_stat_statements_merged as
select
regexp_replace(upper(query), ' ?\$[0-9]+( ?,? ?\$[0-9]+)* ?', ' ? ', 'g') as query,
sum(plans) as plans,
sum(total_plan_time) as total_plan_time,
min(min_plan_time) as min_plan_time,
max(max_plan_time) as max_plan_time,
sum(total_plan_time)/sum(calls) as mean_plan_time,
sum(stddev_plan_time) as stddev_plan_time,
sum(calls) as calls,
sum(total_exec_time) as total_exec_time,
min(min_exec_time) as min_exec_time,
max(max_exec_time) as max_exec_time,
sum(total_exec_time)/sum(calls) as mean_exec_time,
sum(stddev_exec_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,
sum(wal_records) as wal_records,
sum(wal_fpi) as wal_fpi,
sum(wal_bytes) as wal_bytes_m
from pg_stat_statements group by regexp_replace(upper(query), ' ?\$[0-9]+( ?,? ?\$[0-9]+)* ?', ' ? ', 'g');
