pg_stat_statements normalization

normalize_query_function.sql

CREATE OR REPLACE FUNCTION normalize_query(IN TEXT, OUT TEXT) AS $body$
  SELECT
    regexp_replace(
    lower($1),
    -- Remove IN values                            
    'in\s*\([,\s\?]*\)',          'in (...)',    'g'   )
    
  ;
$body$
LANGUAGE SQL;

query.sql

SELECT 
 datname,
 round(total_time::numeric/calls, 2) AS avg_time,
 calls, 
 round(total_time::numeric, 2) AS total_time,
 rows,
 query 
FROM pg_stat_statements_normalized 

JOIN pg_database d
ON d.oid = dbid
ORDER BY 2 DESC, 3 DESC;

view.sql

CREATE OR REPLACE VIEW pg_stat_statements_normalized AS
SELECT userid, dbid, normalize_query(query) AS query, sum(calls) AS calls,
  sum(total_time) AS total_time, sum(rows) as rows,
  sum(shared_blks_hit) AS shared_blks_hit,
  sum(shared_blks_read) AS shared_blks_read,
  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_written) AS local_blks_written, 
  sum(temp_blks_read) AS temp_blks_read,
  sum(temp_blks_written) AS temp_blks_written
FROM pg_stat_statements
GROUP BY 1,2,3;

From: http://blog.ioguix.net/postgresql/2012/08/06/Normalizing-queries-for-pg_stat_statements.html

I adapted the normalize_query function as now pg_stat_statements does most of it (I think?). The remaining normalization is to replace the IN (?, ?, ?) by IN (...)