RedShift常用使用数据库

## 查看sql执行情况，扫描数据量等
```
select * from svl_query_metrics where query = 41926328
select * from SVCS_S3QUERY_SUMMARY where query=1415

select query, datediff(seconds, starttime, endtime), starttime, endtime,
trim(querytxt) as sqlquery
from stl_query
where starttime >= '2021-04-25 00:00' and endtime < '2021-04-26 00:00'
and sqlquery like '%user_event_%95616%'
order by sqlquery,date_diff desc;
```

## 查看执行日志，如是否使用缓存
```
select * from SVL_QLOG where query = 41926328
```

## 查看仓库表大小
```
select
  trim(pgdb.datname) as database, trim(pgn.nspname) as schema,
  trim(a.name) as Table, b.mbytes, a.rows
from
  (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name) as a
  join pg_class as pgc on pgc.oid = a.id
  join pg_namespace as pgn on pgn.oid = pgc.relnamespace
  join pg_database as pgdb on pgdb.oid = a.db_id
  join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl
order by 1, 2, 4 desc;
```

## 查看压缩建议
```
analyze compression b_user_event_all_96328;

修改字段压缩类型：
https://aws.amazon.com/cn/about-aws/whats-new/2020/10/amazon-redshift-supports-modifying-column-comprression-encodings-to-optimize-storage-utilization-query-performance/
```

## 查看表信息
```
select * from svv_table_info where "table"='b_user_event_all_96328';
select * from pg_table_def where tablename='b_user_event_all_96328';
```

## 删除表数据
### 1. 执行删除命令
```
sql: delete from tablename where xx=xxx;
```
此时不会释放磁盘

### 2. 释放磁盘
```
参考：https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_VACUUM_command.html

sql:
-- 排序表 + 释放磁盘（见下方：unsorted,vacuum_sort_benefit）
vacuum 表名 to 100 percent;

-- 仅释放磁盘
vacuum delete only b_user_event_all_42509 to 100 percent;
```
```
[to 100 percent] 若不加，缺省比例为：95%
如果：(tbl_rows - estimated_visible_rows) / tbl_rows < 5%，这个步骤就会跳过
```
```
查询表信息：
select database, schema, table_id, "table", sortkey_num, size, tbl_rows, estimated_visible_rows, unsorted,vacuum_sort_benefit from svv_table_info where "table" like '%41397%';

参考文档：
https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_SVV_TABLE_INFO.html
https://docs.amazonaws.cn/redshift/latest/dg/t_Reclaiming_storage_space202.html
```
#### unsorted, vacuum_sort_benefit 表中排序影响
```
 table | unsorted | vacuum_sort_benefit 
-------+----------+---------------------
 sales |    85.71 |                5.00
 event |    45.24 |               67.00
对于表“sales”，即使该表的物理未排序项约为 86%，其对查询性能的影响也仅为 5%。这可能是因为查询只访问表的一小部分内容，也可能是因为访问表的查询几近于无。对于表“event”，该表的物理未排序项约为 45%。不过，67% 的查询性能影响表明查询访问了表的更大部分内容，或者访问表的查询的数量很多。表“event”可能会从运行 VACUUM SORT 中受益。
https://docs.amazonaws.cn/redshift/latest/dg/t_Reclaiming_storage_space202.html
```

vanki

再相见，会是怎样的世界？

创建： 2020-11-23 18:03:27

更新： 2021-07-14 15:07:16

RedShift常用使用数据库

TA的最新笔记

TA的最热笔记

欢 迎

登录

新朋友 注册

注册

老朋友 登录

RedShift常用使用 数据库

TA的最新笔记

TA的最热笔记

欢迎

新朋友注册

老朋友登录

RedShift常用使用数据库