GreenPlum查询数据库中前几张大表
👉 本文共约56个字，系统预计阅读时间或需1分钟。
本页目录 隐藏
前50张大表，根据行数倒序排序
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT t.table_catalog as db, n.nspname AS schemaname, c.relname, c.reltuples::numeric as rowcount, pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS indexes_size, pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS total_size --,pg_relation_filepath(table_name) filepath FROM pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" ) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema','gp_toolkit' ) AND relkind in ('r','p') ORDER BY rowcount DESC LIMIT 50; |
其它方式
1 2 3 4 5 6 7 8 9 10 11 12 13 | select pg_size_pretty(pg_relation_size('schema.tablename')); -- 查看schema下所有表和索引占用的磁盘空间 SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB FROM gp_toolkit.gp_size_of_all_table_indexes order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ; -- 查看表占用空间 select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB from gp_toolkit.gp_size_of_table_uncompressed order by sotuschemaname, table_SIZE_MB desc,sotutablename |
