We have migrated to new Google sites!
--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;