如何找到Redshift表中每一列的大小? [英] How can I find out the size of each column in a Redshift table?

问题描述

在Redshift中尝试不同的压缩设置时，了解每一列的大小将非常有用.我知道如何获取表的大小，但是我想知道该表中每个单独列的大小.

While trying out different compression settings in Redshift it would be very useful to know the size of each column. I know how to get the size of a table, but I want to know the size of each individual column in that table.

推荐答案

此查询将为您提供每一列的大小(MB).它的作用是计算数据块的数量，其中每个块使用1 MB(按表和列分组).

This query will give you the size (MB) of each column. What it does is that it counts the number of data blocks, where each block uses 1 MB, grouped by table and column.

SELECT
  TRIM(name) as table_name,
  TRIM(pg_attribute.attname) AS column_name,
  COUNT(1) AS size
FROM
  svv_diskusage JOIN pg_attribute ON
    svv_diskusage.col = pg_attribute.attnum-1 AND
    svv_diskusage.tbl = pg_attribute.attrelid
GROUP BY 1, 2

您可以在此处阅读有关查询中涉及的两个表的更多信息: SVV_DISKUSAGE & pg_attribute .

You can read more about the two tables involved in the query here: SVV_DISKUSAGE & pg_attribute.

这篇关于如何找到Redshift表中每一列的大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！