postgresql列表和按大小排序表

Is there an easy way to list all tables from PostgreSQL database and order them by size?

是否有一种简单的方法列出PostgreSQL数据库中的所有表并按大小排序?

Pseudo-code

伪代码

SELECT * FROM tables
ORDER by tables.size

I am using PostgreSQL 9.3.2.

我使用的是PostgreSQL 9.3.2。

5 个解决方案

#1

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

这将显示模式公共中的所有表的大小，如果您有多个模式，您可能希望使用:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

SQLFiddle示例:http://sqlfiddle.com/ ! 15/13157/3

List of all object size functions in the manual:
https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

手册中所有对象大小函数的列表:https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

#2

This will show you the schema name, table name, size pretty and size (needed for sort).

这将向您显示模式名、表名、大小和大小(排序所需)。

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

I build this based on the solutions from here list of schema with sizes (relative and absolute) in a PostgreSQL database

我在这里根据PostgreSQL数据库中带有大小(相对和绝对)的模式列表的解决方案构建这个模型

#3

select table_name,n_live_tup, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
inner join pg_stat_user_tables  on table_name=relname
where table_schema = 'public'
order by 2 desc

Another alternative

另一种替代方法

#4

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

taken from here https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

从这里https://wiki-bsse.ethz.ch/display/ITDOC/Check +大小+ +表+和+对象+ + PostgreSQL数据库中

#5

 select uv.a tablename, pg_size_pretty(uv.b) sizepretty from 
 (select tb.tablename a, pg_table_size('schemaname.'||tb.tablename::text) b from pg_tables tb where tb.schemaname ilike 'schemaname' order by 2 desc) uv

#1

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2