This viewawslabs开源的可以用来查询最常查询的表。
创建视图
CREATE OR REPLACE VIEW admin.v_get_table_scan_frequency
AS
SELECT
database,
schema AS schemaname,
table_id,
"table" AS tablename,
size,
sortkey1,
NVL(s.num_qs,0) num_qs
FROM svv_table_info t
LEFT JOIN (SELECT
tbl, perm_table_name,
COUNT(DISTINCT query) num_qs
FROM
stl_scan s
WHERE
s.userid > 1
AND s.perm_table_name NOT IN ('Internal Worktable','S3')
GROUP BY
tbl, perm_table_name) s ON s.tbl = t.table_id
AND t."schema" NOT IN ('pg_internal')
ORDER BY 7 desc;
Table
\d admin.v_get_table_scan_frequency
Column | Type | Modifiers
------------+--------+-----------
database | text |
schemaname | text |
table_id | oid |
tablename | text |
size | bigint |
sortkey1 | text |
num_qs | bigint |
Query
select * from admin.v_get_table_scan_frequency order by num_qs;
Result
database | schemaname | table_id | tablename | size | sortkey1 | num_qs
-----------------+------------+----------+------------------------------------------+-------+---------------+--------
db | product | 1 | table1 | 92 | AUTO(SORTKEY) | 13448
db | product | 2 | table2 | 180 | AUTO(SORTKEY) | 13389
在 Prometheus 中保存此查询的时间序列数据可以帮助找到每个表随时间变化的速率和频率趋势。基于此,我们可以决定在 Redshift 中刷新数据的频率。