如何识别Postgres 15中未使用的视图（含物化与非物化视图）

Postgres本身没有直接跟踪视图查询记录的内置统计项，但可以通过以下几种间接方式判断视图是否在指定时间段内未被访问：

方法1：通过底层表访问统计间接推断 #

视图的查询最终会落到其依赖的底层表上，若某个视图依赖的所有底层表在目标时间段内，没有被该视图相关的查询触发访问，可间接推断视图未被使用。

执行以下查询（自行调整时间段参数）：

WITH view_deps AS (
    SELECT 
        v.relname AS view_name,
        c.relname AS underlying_table
    FROM pg_class v
    JOIN pg_depend d ON v.oid = d.objid
    JOIN pg_class c ON d.refobjid = c.oid
    WHERE v.relkind IN ('v', 'm') -- 'v'为普通视图，'m'为物化视图
      AND c.relkind = 'r' -- 仅关联普通表
),
unused_tables AS (
    SELECT 
        relname AS table_name
    FROM pg_stat_user_tables
    WHERE last_query_time < NOW() - INTERVAL '30 days' -- 设置要检查的时间段
)
SELECT DISTINCT v.view_name
FROM view_deps v
JOIN unused_tables t ON v.underlying_table = t.table_name
WHERE NOT EXISTS (
    SELECT 1 FROM pg_stat_user_tables st
    WHERE st.relname = v.underlying_table
      AND st.last_query_time >= NOW() - INTERVAL '30 days'
);

方法2：用pg_stat_statements跟踪视图查询 #

若已启用pg_stat_statements扩展（需在postgresql.conf中配置shared_preload_libraries = 'pg_stat_statements'并重启实例），可通过分析SQL语句识别视图的访问记录。

执行以下查询查找未被访问的视图：

WITH all_views AS (
    SELECT relname AS view_name
    FROM pg_class
    WHERE relkind IN ('v', 'm')
      AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'))
),
accessed_views AS (
    SELECT DISTINCT regexp_match(query, 'FROM\s+([a-zA-Z0-9_]+)', 'i')[1] AS view_name
    FROM pg_stat_statements
    WHERE query_start >= NOW() - INTERVAL '30 days' -- 设置时间段
      AND query ~* 'FROM\s+[a-zA-Z0-9_]+'
)
SELECT view_name
FROM all_views
WHERE view_name NOT IN (SELECT view_name FROM accessed_views);

注：复杂查询（带schema、别名）需调整正则，比如适配schema的写法：regexp_match(query, 'FROM\s+([a-zA-Z0-9_]+\.)?([a-zA-Z0-9_]+)', 'i')[2]。

方法3：物化视图的直接判断 #

物化视图本质是存储数据的表，可直接通过pg_stat_user_tables查看其访问统计：

SELECT relname AS matview_name, last_query_time
FROM pg_stat_user_tables
WHERE relkind = 'm'
  AND last_query_time < NOW() - INTERVAL '30 days';

注意事项 #

• 以上均为间接推断，没有绝对精准的判定方式，因为Postgres不直接跟踪视图访问。
• 若底层表同时被其他直接查询访问，需结合业务场景进一步筛选。
• pg_stat_statements会带来轻微性能开销，启用前需评估系统负载。
• 统计数据会在实例重启或执行pg_stat_reset()后重置，需确保统计周期覆盖需求范围。

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