How to measure table space on disk in ParAccel
Posted by Diego em Julho 31, 2015
Special attention to the value 262144 which represents the block size in Bytes.
The default is 1 Mb but If the block size is different (in my case for example is 256K), you have to multiply the number of blocks by its size in bytes.
select c.oid as "Table ID", s.oid as "Schema ID", trim(c.relname) as "Name", trim(s.nspname) as "Schema", trim(u.usename) as "Owner", coalesce(c.reltuples, 0)::bigint as "Rows", coalesce(t.blocks * 262144, 0)::bigint/(1024*1024) as "Total MBytes", coalesce(t.blocks, 0) as "Total Blocks", c.relnatts as "Columns", current_database() as db from pg_catalog.pg_class c join pg_catalog.pg_namespace s on s.oid = c.relnamespace join pg_catalog.pg_user u on u.usesysid = c.relowner left join ( select p.id, p.db_id, sum(coalesce(p.rows, 0)) as rows, sum(coalesce(p.sorted_rows, 0)) as sorted_rows, sum(coalesce(p.temp, 0)) as temp, min(coalesce(p.rows, 0)) as min_rows, max(coalesce(p.rows, 0)) as max_rows, sum(b.blocknum) as blocks from (select distinct slice from pg_catalog.stv_blocklist) bl left join pg_catalog.stv_tbl_perm p on p.slice = bl.slice left join pg_catalog.pg_database d on d.datname = current_database() left join ( select bl.tbl, bl.slice, count(bl.blocknum) as blocknum from pg_catalog.stv_blocklist bl group by bl.tbl, bl.slice ) b on b.tbl = p.id and b.slice = p.slice group by p.id, p.db_id ) t on t.id = c.oid where c.relkind = 'r' and nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_bitmapindex', 'pg_internal', 'pg_aoseg') order by c.relname, s.nspname
“Cols” refers to a Zero-based index for the column. Every table you create has three hidden columns appended to it: INSERT_XID, DELETE_XID, and ROW_ID (OID).
A table with 3 user-defined columns contains 6 actual columns, and the user-defined columns are internally numbered as 0, 1, and 2. The INSERT_XID, DELETE_XID, and ROW_ID columns are numbered 3, 4, and 5, respectively, in this example.
select col, count(*) as num_blocks,count(*) * 262144::bigint/(1024*1024) as "Total MBytes" from stv_blocklist, stv_tbl_perm where stv_blocklist.tbl = stv_tbl_perm.id and stv_blocklist.slice = stv_tbl_perm.slice and stv_tbl_perm.name = 'occurencecharts_results2' and col IN (20, 21) group by col order by col;