Pivotal Knowledge Base

Follow

HowTo - Get AOCO Table Column Disk Sizes

Purpose

How to obtain the AOCO Table Column Disk Size ?

Solution

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

select
   (prn.segment_file_num/128)+1 as column_number,
   pg_size_pretty(sum(prn.mirror_append_only_new_eof)::bigint)  as disk_size
from
   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;

Example

  • 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.
CREATE TABLE
  • Insert some data
test=# insert into aoco select x.a, x.a from generate_series(1, 100000) as x(a);
INSERT 0 100000
test=# 
  • Get the table size
test=# select pg_size_pretty(pg_relation_size('aoco'));
 pg_size_pretty 
----------------
 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)

Comments

Powered by Zendesk