Just thought of sharing this with all.
This can be handy SQL snippet in projects
Suppose you want to calculate the individual table size of any table in your database.
You can use the COLUMN_STORAGE system table for calculating the size.
Lets see and understand this step by step
COLUMN_STORAGE table shows the space in bytes used for every column in database.
so Let so go ahead querying a table
Code: Select all
nnani=> select anchor_table_schema, anchor_table_name,node_name, column_name, used_bytes as column_used_bytes
nnani-> from column_storage where anchor_table_schema='DV0' and anchor_table_name='feed_prvdr' and
nnani-> node_name ='vnode0002';
anchor_table_schema | anchor_table_name | node_name | column_name | column_used_bytes
---------------------+-------------------+-----------------+-----------------+-------------------
DV0 | feed_prvdr | vnode0002 | prvdr_id | 6
DV0 | feed_prvdr | vnode0002 | prvdr_nm | 65
DV0 | feed_prvdr | vnode0002 | prvdr_desc | 120
DV0 | feed_prvdr | vnode0002 | pdt_by | 62
DV0 | feed_prvdr | vnode0002 | dt_tm | 20
DV0 | feed_prvdr | vnode0002 | dt_tm | 20
DV0 | feed_prvdr | vnode0002 | | 96
DV0 | feed_prvdr | vnode0002 | prvdr_id | 3
DV0 | feed_prvdr | vnode0002 | prvdr_nm | 32
DV0 | feed_prvdr | vnode0002 | prvdr_desc | 49
DV0 | feed_prvdr | vnode0002 | pdt_by | 31
DV0 | feed_prvdr | vnode0002 | dt_tm | 10
DV0 | feed_prvdr | vnode0002 | dt_tm | 10
DV0 | feed_prvdr | vnode0002 | | 48
(14 rows)
This might be due to super projection and the buddy projection residing on the same node so all the column are appearing twice.
Now lets find the size of this table
Code: Select all
nnani=> select sum(column_used_bytes) as table_size_in_bytes from
nnani-> (select anchor_table_schema, anchor_table_name,node_name, column_name, sum(used_bytes) as column_used_bytes
nnani(> from column_storage where anchor_table_schema='DV0' and anchor_table_name='feed_prvdr' and
nnani(> node_name IN (select node_name from nodes)
nnani(> group by 1,2,3,4) sub;
table_size_in_bytes
---------------------
2952
(1 row)
Hope this helps..