SQL to display Database Size,Schema Size, Table and Index Size in Greenplum

--Database size

select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;

\qecho

\qecho  -- Schema size

select sosdnsp as SchemaName,

round(sosdschematablesize/power(1024,3)::numeric,2) as table_size_GB ,

round(sosdschemaidxsize/power(1024,3)::numeric,2) as index_size_GB,

round((sosdschematablesize + sosdschemaidxsize)/power(1024,3)::numeric,2) as schema_size_GB

from gp_toolkit.gp_size_of_schema_disk ;

\qecho

 

set statement_mem='1GB';

-- Table and Index Size

SELECT tabs.nspname AS schema_name

, COALESCE(parts.tablename, tabs.relname) AS table_name

, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3) AS table_GB

, ROUND(SUM(sotaididxsize)/1024/1024/1024,3) AS index_GB

, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3) total_gb

FROM   gp_toolkit.gp_size_of_table_and_indexes_disk sotd

,     (SELECT c.oid, c.relname, n.nspname

       FROM   pg_class c

       ,      pg_namespace n

       WHERE  n.oid = c.relnamespace

       AND    c.relname NOT LIKE '%_err'

      )tabs

LEFT JOIN pg_partitions parts

ON     tabs.nspname = parts.schemaname

AND    tabs.relname = parts.partitiontablename

WHERE  sotd.sotaidoid = tabs.oid and tabs.nspname = 'public'

GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)

ORDER BY 1 desc,(ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3)) desc,2;

 

 -- Schema Size

select

sosdnsp as schemaname,

sosdschematablesize/(1024*1024*1024) as SchemaTableSizeGB,

sosdschemaidxsize/(1024*1024*1024) as SchemaIndxSizeGB,

round((sosdschematablesize/power(1024,4)+sosdschemaidxsize/power(1024,4)):: numeric,2) as SchemaSizeTB

from schema_size() where sosdschematablesize+sosdschemaidxsize >0;


where schema_size() function is like



As gpadmin:
-- Schema Size
create or replace function schema_size()
returns setof gp_toolkit.gp_size_of_schema_disk
as
$$
select * from gp_toolkit.gp_size_of_schema_disk;
$$
language sql volatile security definer;

-- Table Size
create or replace function table_size()
returns setof gp_toolkit.gp_size_of_table_disk
as
$$
select * from gp_toolkit.gp_size_of_table_disk;
$$
language sql volatile security definer;

-- Table and Index Size

create or replace function tableandindex_size()

returns setof gp_toolkit.gp_size_of_table_and_indexes_disk
as
$$
select * from gp_toolkit.gp_size_of_table_and_indexes_disk;
$$
language sql volatile security definer;

grant execute on function schema_size() to sachi;
grant execute on function table_size() to sachi;
grant execute on function tableandindex_size() to sachi;

Comments