Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

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
				                   

 

 

By Column:

“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;

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: