PSQL - 选择分区表和普通表的大小 [英] PSQL - Select size of tables for both partitioned and normal

问题描述

预先感谢您对此提供的任何帮助，非常感谢.

Thanks in advance for any help with this, it is highly appreciated.

所以，基本上，我有一个 Greenplum 数据库，我想为前 10 个最大的表选择表大小.使用以下内容没有问题:

So, basically, I have a Greenplum database and I am wanting to select the table size for the top 10 largest tables. This isn't a problem using the below:

select 
sotaidschemaname schema_name
,sotaidtablename table_name
,pg_size_pretty(sotaidtablesize) table_size
from gp_toolkit.gp_size_of_table_and_indexes_disk
order by 3 desc
limit 10
;

但是，我的数据库中有几个分区表，这些表与上述 sql 一起显示，因为它们的所有子表"都分成小片段(尽管我知道它们累积起来可以制作最大的 2 个表).有没有办法制作一个选择表(分区或其他)及其总大小的脚本?

However I have several partitioned tables in my database and these show up with the above sql as all their 'child tables' split up into small fragments (though I know they accumalate to make the largest 2 tables). Is there a way of making a script that selects tables (partitioned or otherwise) and their total size?

注意:由于只有 2 个分区表，因此我很乐意包含某种连接，在其中我专门指定了分区表名.但是，我仍然需要取前 10 个(我不能假设分区表在那里)并且我不能指定任何其他表名，因为有将近一千个.

Note: I'd be happy to include some sort of join where I specify the partitoned table-name specifically as there are only 2 partitioned tables. However, I would still need to take the top 10 (where I cannot assume the partitioned table(s) are up there) and I cannot specify any other table names since there are near a thousand of them.

再次感谢，维尼.

推荐答案

你的朋友会使用 pg_relation_size() 函数来获取关系大小，你会选择 pg_class、pg_namespace 和 pg_partition 将它们连接在一起，如下所示:

Your friends would be pg_relation_size() function for getting relation size and you would select pg_class, pg_namespace and pg_partition joining them together like this:

select  schemaname,
        tablename,
        sum(size_mb) as size_mb,
        sum(num_partitions) as num_partitions
    from (
        select  coalesce(p.schemaname, n.nspname) as schemaname,
                coalesce(p.tablename, c.relname) as tablename,
                1 as num_partitions,
                pg_relation_size(n.nspname || '.' || c.relname)/1000000. as size_mb
            from pg_class as c
                inner join pg_namespace as n on c.relnamespace = n.oid
                left join pg_partitions as p on c.relname = p.partitiontablename and n.nspname = p.partitionschemaname    
        ) as q
    group by 1, 2
    order by 3 desc
    limit 10;

这篇关于PSQL - 选择分区表和普通表的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！