AWS - Redshift - 表和表存储的一些查询

redshift 针对 表信息，存储的查询：

-- 查看表信息， diststyle， size
select * from pg_catalog.svv_table_info 
where "table" = 'tablename';

-- 查询每个 slice 
select slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage
where name='tablename';

-- 表中每一列的 1 MB 块的数目
select col, count(*) from stv_blocklist, stv_tbl_perm
where stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name = 'tablename'
group by col
order by col;

-- 查询显示表数据是否实际分布于所有分片间：
select trim(name) as table, stv_blocklist.slice, stv_tbl_perm.rows
from stv_blocklist,stv_tbl_perm
where stv_blocklist.tbl=stv_tbl_perm.id
and stv_tbl_perm.slice=stv_blocklist.slice
and stv_blocklist.id > 10000 and name not like '%#m%'
and name not like 'systable%'
group by name, stv_blocklist.slice, stv_tbl_perm.rows
order by 1, 2, 3 desc;

--是否向磁盘提交了任何已逻辑删除的块： 
select slice, col, tbl, blocknum, newblock
from stv_blocklist
where  tombstone > 0
order by 1y,2;

-- 每个表总行数，总空间，pct
select trim(pgdb.datname) as Database, trim(pgns.nspname) as Schema, trim(a.name) as Table,
c.rows, ((b.mbytes/part.total::decimal)*100)::decimal(5,3) as pct_of_total, b.mbytes, b.unsorted_mbytes
from stv_tbl_perm a
join pg_class as pgtbl on pgtbl.oid = a.id
join pg_namespace as pgns on pgns.oid = pgtbl.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl
join (select id, sum(rows) as rows from stv_tbl_perm group by id) c on a.id=c.id
join (select sum(capacity) as total from stv_partitions where part_begin=0) as part on 1=1
where a.slice=0
order by 6 desc, db_id, name;