如何在Redshift中获取使用统计信息及重点表使用分析？

嘿，针对你要分析9张特定表的使用频率、关联用户数、查询次数、资源消耗及总查询时长的需求，我结合你提到的stl_scan和pg_user系统表，整理了几个适配Redshift的实用查询脚本，刚好覆盖你要的所有维度，还专门针对指定表做了过滤，效率更高：

1. 各表的查询次数与关联用户数 #

这个查询能帮你统计每张目标表被多少不同用户访问，以及总查询次数：

WITH target_tables AS (
    -- 这里替换成你的9张表名，格式：'schema_name.table_name'
    SELECT unnest(ARRAY['public.table1', 'public.table2', 'public.table3']) AS table_full_name
)
SELECT 
    s.perm_table_name AS table_name,
    COUNT(DISTINCT u.usename) AS unique_users,
    COUNT(*) AS total_queries
FROM 
    stl_scan s
JOIN 
    pg_user u ON s.userid = u.usesysid
JOIN 
    target_tables tt ON s.perm_table_name = tt.table_full_name
WHERE 
    s.perm_table_name IS NOT NULL -- 排除临时表
GROUP BY 
    s.perm_table_name
ORDER BY 
    total_queries DESC;

说明：target_tables CTE用来限定你要分析的9张表，避免扫描全量系统表；unique_users统计访问过该表的不同用户数，total_queries是该表被扫描的总次数。

2. 每张表的资源消耗与总查询时长 #

这个脚本会计算每张表的扫描数据量、CPU消耗占比以及总查询时长，帮你定位资源大户：

WITH target_tables AS (
    SELECT unnest(ARRAY['public.table1', 'public.table2', 'public.table3']) AS table_full_name
)
SELECT 
    s.perm_table_name AS table_name,
    SUM(s.bytes) AS total_scanned_bytes,
    SUM(s.cpu_time) AS total_cpu_time_ms,
    SUM(s.elapsed) AS total_query_duration_ms,
    ROUND(SUM(s.bytes) / 1024 / 1024 / 1024, 2) AS total_scanned_gb -- 转换为GB更直观
FROM 
    stl_scan s
JOIN 
    target_tables tt ON s.perm_table_name = tt.table_full_name
WHERE 
    s.perm_table_name IS NOT NULL
GROUP BY 
    s.perm_table_name
ORDER BY 
    total_scanned_bytes DESC;

说明：total_scanned_bytes是该表被查询时扫描的总数据量，total_cpu_time_ms是消耗的CPU总时长，total_query_duration_ms是所有涉及该表的查询总耗时，这些指标能直接反映表的资源消耗情况。

3. 额外实用维度：按用户细分的表使用情况 #

如果需要看单个用户对每张表的访问细节，可以用这个查询：

WITH target_tables AS (
    SELECT unnest(ARRAY['public.table1', 'public.table2', 'public.table3']) AS table_full_name
)
SELECT 
    u.usename AS user_name,
    s.perm_table_name AS table_name,
    COUNT(*) AS user_query_count,
    SUM(s.elapsed) AS user_total_duration_ms
FROM 
    stl_scan s
JOIN 
    pg_user u ON s.userid = u.usesysid
JOIN 
    target_tables tt ON s.perm_table_name = tt.table_full_name
WHERE 
    s.perm_table_name IS NOT NULL
GROUP BY 
    u.usename, s.perm_table_name
ORDER BY 
    user_query_count DESC;

说明：这个脚本可以帮你找出哪些用户对特定表的访问最频繁，或者耗时最长，方便做权限优化或查询调优。

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