PostgreSQL多Schema下pg_stat_statements等指标拆分方案咨询

一、无需额外扩展的原生方案 #

1. 解析pg_stat_statements查询提取Schema（显式指定Schema场景） #

如果查询中显式写了Schema名（如SELECT * FROM schema1.table1），可通过正则从pg_stat_statements的query字段提取Schema，聚合统计核心指标：

SELECT
  COALESCE(
    regexp_match(query, 'FROM (\w+)\.', 'i')[1],
    regexp_match(query, 'INSERT INTO (\w+)\.', 'i')[1],
    regexp_match(query, 'UPDATE (\w+)\.', 'i')[1]
  ) AS schema_name,
  queryid,
  left(query, 100) AS query_sample,
  sum(calls) AS total_calls,
  sum(total_time) AS total_exec_time,
  mean_time AS avg_exec_time
FROM pg_stat_statements
WHERE query ~* 'FROM \w+\.|INSERT INTO \w+\.|UPDATE \w+\.'
GROUP BY schema_name, queryid, query, mean_time
ORDER BY total_exec_time DESC;

该SQL覆盖常见DML操作，自动提取Schema名并聚合执行次数、耗时等数据。

2. 结合会话search_path匹配隐式Schema（未显式指定Schema场景） #

如果查询依赖search_path隐式访问Schema，可关联pg_stat_activity会话信息与pg_class匹配表所属Schema：

SELECT
  n.nspname AS schema_name,
  s.queryid,
  left(s.query, 100) AS query_sample,
  count(*) AS exec_count,
  sum(s.total_time) AS total_exec_time
FROM pg_stat_statements s
JOIN pg_stat_activity a ON s.queryid = a.queryid
JOIN pg_class c ON s.query LIKE '%' || c.relname || '%'
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE a.state IN ('idle', 'active')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schema_name, s.queryid, s.query
ORDER BY total_exec_time DESC;

注意：模糊匹配可能存在误判，建议结合业务表名特征优化LIKE条件。

3. 创建自定义聚合视图 #

将上述逻辑封装为视图，方便日常快速查询：

CREATE VIEW schema_statistics AS
SELECT
  COALESCE(
    regexp_match(query, 'FROM (\w+)\.', 'i')[1],
    regexp_match(query, 'INSERT INTO (\w+)\.', 'i')[1],
    regexp_match(query, 'UPDATE (\w+)\.', 'i')[1],
    (SELECT n.nspname FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relname = regexp_match(query, 'FROM (\w+)', 'i')[1])
  ) AS schema_name,
  queryid,
  left(query, 150) AS query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
AND query NOT LIKE '%schema_statistics%';

二、使用第三方扩展增强统计能力 #

pg_stat_monitor（Percona开源扩展） #

作为pg_stat_statements的增强版，该扩展原生支持按Schema、用户、数据库等维度分组统计，无需手动解析查询：

1. 安装后启用扩展：

CREATE EXTENSION pg_stat_monitor;

2. 直接按Schema聚合查询：

SELECT
  schema_name,
  queryid,
  left(query, 100) AS query_sample,
  calls,
  total_time,
  mean_time
FROM pg_stat_monitor
WHERE schema_name IS NOT NULL
GROUP BY schema_name, queryid, query, calls, total_time, mean_time
ORDER BY total_time DESC;

它还支持时间窗口统计、慢查询追踪等功能，适配多Schema场景的批量统计需求。

三、注意事项 #

• 确保pg_stat_statements已启用（shared_preload_libraries = 'pg_stat_statements'），并设置足够的track_activity_query_size以存储完整查询。
• 隐式Schema访问场景下，建议业务代码尽量显式指定Schema，避免统计误判。
• 定期清理pg_stat_statements历史数据（SELECT pg_stat_statements_reset();），避免统计结果过大。

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