Pivotal Knowledge Base


How to get AOCO Table Column Disk Sizes



This article describes how to obtain the AOCO Table Column Disk Sizes.


SQL to get AOCO table column disk size (replace "table_name" and "schema_name" with the details of your particular object):

   (prn.segment_file_num/128)+1 as column_number,
   pg_size_pretty(sum(prn.mirror_append_only_new_eof)::bigint)  as disk_size
   gp_dist_random('gp_persistent_relation_node') prn join
    (select relfilenode
        FROM   pg_class c, pg_namespace n
        WHERE  n.oid = c.relnamespace
        AND    c.relname = "table_name"
        AND    n.nspname = "schema_name"
        AND    c.relkind = 'r'
        AND    c.relstorage = 'c'
    ) parts
   on (prn.relfilenode_oid = parts.relfilenode)
group by 1
order by 1;


  • Create AOCO table with 2 columns (int and float):
test=# create table aoco(a int, b float) with (appendonly=true, orientation=column);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
  • Insert some data
test=# insert into aoco select x.a, x.a from generate_series(1, 100000) as x(a);
INSERT 0 100000
  • Get the table size
test=# select pg_size_pretty(pg_relation_size('aoco'));
 1173 kB
(1 row)
  • Get the size of each column separately
test=# select
test-#    (prn.segment_file_num/128)+1 as column_number,
test-#    pg_size_pretty(sum(prn.mirror_append_only_new_eof)::bigint)  as disk_size
test-# from
test-#    gp_dist_random('gp_persistent_relation_node') prn join
test-#     (select relfilenode
test(#         FROM   pg_class c, pg_namespace n
test(#         WHERE  n.oid = c.relnamespace
test(#         AND    c.relname = 'aoco'
test(#         AND    n.nspname = 'public'
test(#         AND    c.relkind = 'r'
test(#         AND    c.relstorage = 'c'
test(#     ) parts
test-#    on (prn.relfilenode_oid = parts.relfilenode)
test-# group by 1
test-# order by 1;
 column_number | disk_size 
             1 | 391 kB
             2 | 782 kB
(2 rows)


Powered by Zendesk