查看表和数据库的信息 查询表大小 查询表的总大小(包含表的索引和数据)。 select pgsizepretty(pgtotalrelationsize(' . ')); 示例: 先在customert1创建索引: CREATE INDEX index1 ON customert1 USING btree(ccustomersk); 然后查询public模式下,customert1表的大小。 select pgsizepretty(pgtotalrelationsize('public.customert1')); pgsizepretty 264 kB (1 row) 查询表的数据大小(不包括索引) select pgsizepretty(pgrelationsize(' . ')); 示例:查询public模式下,customert1表的大小。 select pgsizepretty(pgrelationsize('public.customert1')); pgsizepretty 208 kB (1 row) 查询系统中所有表占用空间大小排行 SELECT tableschema '.' tablename AS tablefullname, pgsizepretty(pgtotalrelationsize('"' tableschema '"."' tablename '"')) AS size FROM informationschema.tables ORDER BY pgtotalrelationsize('"' tableschema '"."' tablename '"') DESC limit xx; 示例1:查询系统中所有表占用空间大小排行前15。 SELECT tableschema '.' tablename AS tablefullname, pgsizepretty(pgtotalrelationsize('"' tableschema '"."' tablename '"')) AS size FROM informationschema.tables ORDER BY pgtotalrelationsize('"' tableschema '"."' tablename '"') DESC limit 15; tablefullname size + pgcatalog.pgattribute 2048 KB pgcatalog.pgrewrite 1888 KB pgcatalog.pgdepend 1464 KB pgcatalog.pgproc 1464 KB pgcatalog.pgclass 512 KB pgcatalog.pgdescription 504 KB pgcatalog.pgcollation 360 KB pgcatalog.pgstatistic 352 KB pgcatalog.pgtype 344 KB pgcatalog.pgoperator 224 KB pgcatalog.pgamop 208 KB public.tt1 160 KB pgcatalog.pgamproc 120 KB pgcatalog.pgindex 120 KB pgcatalog.pgconstraint 112 KB (15 rows) 示例2:查询public模式下所有表占用空间排行。 SELECT tableschema '.' tablename AS tablefullname, pgsizepretty(pgtotalrelationsize('"' tableschema '"."' tablename '"')) AS size FROM informationschema.tables where tableschema'public' ORDER BY pgtotalrelationsize('"' tableschema '"."' tablename '"') DESC limit 20; tablefullname size + public.tt1 160 KB public.productinfoinput 112 KB public.customert1 96 KB public.warehouset19 48 KB public.emp 32 KB public.customer 0 bytes public.testtriggersrctbl 0 bytes public.warehouset1 0 bytes (8 rows)