如何找到 Postgres / PostgreSQL 表及其索引的磁盘大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2596624/
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
How do you find the disk size of a Postgres / PostgreSQL table and its indexes
提问by mmrobins
I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer.
我从 Oracle 来到 Postgres，正在寻找一种方法来查找表和索引大小bytes/MB/GB/etc，甚至更好地查找所有表的大小。在 Oracle 中，我有一个令人讨厌的长查询，它查看 user_lobs 和 user_segments 以给出答案。
I assume in Postgres there's something I can use in the information_schematables, but I'm not seeing where.
我假设在 Postgres 中有一些我可以在information_schema表格中使用的东西，但我没有看到在哪里。
回答by aib
Try the Database Object Size Functions. An example:
试试数据库对象大小函数。一个例子：
SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));
For all tables, something along the lines of:
对于所有表格，大致如下：
SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
Edit: Here's the query submitted by @phord, for convenience:
编辑：这是@phord 提交的查询，为方便起见：
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
I've modified it slightly to use pg_table_size()to include metadata and make the sizes add up.
我稍微修改了它以用于pg_table_size()包含元数据并使大小相加。
回答by Hendy Irawan
Show database sizes:
显示数据库大小：
\l+
\l+
e.g.
例如
=> \l+
berbatik_prd_commerce | berbatik_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 19 MB | pg_default |
berbatik_stg_commerce | berbatik_stg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8633 kB | pg_default |
bursasajadah_prd | bursasajadah_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1122 MB | pg_default |
Show table sizes:
显示桌子尺寸：
\d+
\d+
e.g.
例如
=> \d+
public | tuneeca_prd | table | tomcat | 8192 bytes |
public | tuneeca_stg | table | tomcat | 1464 kB |
Only works in psql.
仅适用于psql.
(Summary of @zkutch's answer.)
（@zkutch 的回答摘要。）
回答by zkutch
If the database name is snort, the following sentence give it size:
如果数据库名称是snort，下面这句话给出了它的大小：
psql -c "\l+ snort" | awk -F "|" '{print }'
回答by Ahmed MANSOUR
Tyr this : (Index size/usage statistics)
Tyr this :(索引大小/使用统计）
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;
回答by Greg Smith
PostgreSQL tables have three components: the table itself, any indexes on it, and potentially TOAST data. There's a couple of examples showing how to slide and dice the available information various ways at http://wiki.postgresql.org/wiki/Disk_Usage
PostgreSQL 表具有三个组成部分：表本身、其上的任何索引，以及潜在的 TOAST 数据。有几个例子展示了如何在http://wiki.postgresql.org/wiki/Disk_Usage 以各种方式滑动和切块可用信息
回答by Ciges
Just for info, I have got the excelent answer from @aib and modified it a little for:
仅供参考，我从@aib 那里得到了很好的答案，并对其进行了一些修改：
- getting only tables from "public" schema
- show also materialized viewsdata and index size
- 仅从“公共”模式中获取表
- 还显示物化视图数据和索引大小
On materialized view we can use index for refreshing materialized views concurrently, which allows using them while updating.
在物化视图上，我们可以使用索引同时刷新物化视图，这允许在更新时使用它们。
Well, my query will be the following:
好吧，我的查询如下：
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
-- tables from 'public'
SELECT table_name
FROM information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
union
-- materialized views
SELECT oid::regclass::text as table_name
FROM pg_class
WHERE relkind = 'm'
order by table_name
) AS all_tables
-- ORDER BY total_size DESC
order by table_name
) AS pretty_sizes
回答by Sajeev
The Query below will serve you
下面的查询将为您服务
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
See this Link: https://wiki.postgresql.org/wiki/Disk_Usage
回答by Uma
check this wiki. https://wiki.postgresql.org/wiki/Disk_Usage
检查这个维基。https://wiki.postgresql.org/wiki/Disk_Usage
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a
回答by Anvesh
Try this script to find all table size:
尝试使用此脚本查找所有表大小：
SELECT
table_schema || '.' || table_name AS TableName,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
For other different script to find size in PostgreSQL, Please visit this url: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/
对于在 PostgreSQL 中查找大小的其他不同脚本，请访问此 URL：http: //www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/
