SQL postgresql 按大小列出和排序表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21738408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql list and order tables by size
提问by nothing-special-here
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 PostgreSQL9.3.2.
我正在使用PostgreSQL9.3.2.
回答by a_horse_with_no_name
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 publicif you have multiple schemas, you might want to use:
public如果您有多个架构，这将显示架构中所有表的大小，您可能想要使用：
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.
手册中所有对象大小函数的列表。
回答by Kuchi
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 数据库中具有大小（相对和绝对）的架构列表中的解决方案来构建它
回答by Vignesh Raja
This will be more clear.
这样会更清楚。
pg_size_pretty(<numeric_value>)- converts no.of bytes to human-readable format.
pg_size_pretty(<numeric_value>)- 将字节数转换为人类可读的格式。
pg_database_size(<db_name>)- gets database size in bytes.
pg_database_size(<db_name>)- 以字节为单位获取数据库大小。
pg_total_relation_size(<relation_name>)- gets total size of table and its index in bytes.
pg_total_relation_size(<relation_name>)- 获取表的总大小及其索引（以字节为单位）。
pg_relation_size(<relation_name>)- gets relation (table/index) size in bytes.
pg_relation_size(<relation_name>)- 以字节为单位获取关系（表/索引）大小。
pg_index_size(<relation_name>)- gets index size of the relationin bytes.
pg_index_size(<relation_name>)- 获取关系的索引大小（以字节为单位）。
current_database()- gets the currently used database on which this query is being performed.
current_database()- 获取正在执行此查询的当前使用的数据库。
Query:
询问：
select current_database() as database,
pg_size_pretty(total_database_size) as total_database_size,
schema_name,
table_name,
pg_size_pretty(total_table_size) as total_table_size,
pg_size_pretty(table_size) as table_size,
pg_size_pretty(index_size) as index_size
from ( select table_name,
table_schema as schema_name,
pg_database_size(current_database()) as total_database_size,
pg_total_relation_size(table_name) as total_table_size,
pg_relation_size(table_name) as table_size,
pg_indexes_size(table_name) as index_size
from information_schema.tables
where table_schema=current_schema() and table_name like 'table_%'
order by total_table_size
) as sizes;
Result:
结果：
database | total_database_size | schema_name | table_name | total_table_size | table_size | index_size
-----------+---------------------+-------------+------------+------------------+------------+------------
vigneshdb | 1586 MB | corpdata | table_aaa | 16 kB | 0 bytes | 8192 bytes
vigneshdb | 1586 MB | corpdata | table_bbb | 24 kB | 0 bytes | 16 kB
vigneshdb | 1586 MB | corpdata | table_ccc | 640 kB | 112 kB | 488 kB
vigneshdb | 1586 MB | corpdata | table_ddd | 9760 kB | 3152 kB | 6568 kB
vigneshdb | 1586 MB | corpdata | table_eee | 1120 MB | 311 MB | 808 MB
The humanized format is represent in bytes, kB, MB, GB, and TB.
人性化的格式表示bytes，kB，MB，GB，和TB。
bytesto kB- begins from 10240 bytes
bytes到kB- 开始于10240 bytes
bytesto MB- begins from 10485248 bytes= 10239.5 kB~ 10 MB
bytesto MB- 从10485248 bytes= 10239.5 kB~开始10 MB
bytesto GB- begins from 10736893952 bytes= 10239.5 MB~ 10 BG
bytesto GB- 从10736893952 bytes= 10239.5 MB~开始10 BG
bytesto TB- begins from 10994579406848 bytes= 10239.5 GB~ 10 TB
bytesto TB- 从10994579406848 bytes= 10239.5 GB~开始10 TB
All unit conversions starts from 10 + <unit>.
所有单位转换从10 + <unit>.
For reference - Postgres Official Documentation
供参考 - Postgres 官方文档
回答by aki
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+size+of+tables+and+objects+in+PostgreSQL+database
回答by Lauri Lüüs
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
另一种选择
回答by Spike
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
回答by Benjamin Crouzier
I needed to find which tables use the most space.
我需要找出哪些表使用的空间最多。
Based on other answers, I used that query:
根据其他答案，我使用了该查询：
select table_name, pg_size_pretty( pg_relation_size(quote_ident(table_name)) )
from information_schema.tables
where table_schema = 'public'
order by pg_relation_size(quote_ident(table_name)) desc
I get the following result:
我得到以下结果：
table_name pg_size_pretty
--------------------------------------
trade_binance 96 GB
closs_v2_binance_stash 46 GB
closs_bitfinex_stash 5725 MB
trade_bitfinex 5112 MB
...
api_requests 0 bytes
trade_huobi 0 bytes
I should have bought a bigger SSD.
我应该买一个更大的SSD。
