관리 목적의 table별 사용량 check를 위해 사용하는 Query 입니다.
주로 TOP 10 table resource(disk) 사용관리(모니터링) 용도로 사용을 합니다.
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
TO_CHAR( pg_catalog.pg_table_size(c.oid)/1024 , '999,999,999,999,999') as "Size_MB",
TRIM(TRIM(n.nspname)||'.'||TRIM(c.relname))AS TB_NAME ,
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname <> 'sys'
AND n.nspname <> 'dbo'
AND n.nspname !~ '^pg_toast'
ORDER BY pg_catalog.pg_table_size(c.oid) desc
