How to get AOCO Table Column Disk Sizes

Products

• VMware Tanzu Greenplum

Issue/Introduction

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

 

Environment

Resolution

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)

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions