pg_table_size、pg_relation_size 和 pg_total_relation_size 之间有什么区别？(PostgreSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41991380/
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
What's the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
提问by Marco Roy
What's the difference between pg_table_size(), pg_relation_size()& pg_total_relation_size()?
pg_table_size(), pg_relation_size()& 有pg_total_relation_size()什么区别？
I understand the basic differences explained in the documentation, but what does it imply in terms of how much space my table is actually using?
我了解文档中解释的基本差异，但是就我的表实际使用了多少空间而言，这意味着什么？
回答by jmelesky
For a random table:
对于随机表：
# select pg_relation_size(20306, 'main') as main,
pg_relation_size(20306, 'fsm') as fsm,
pg_relation_size(20306, 'vm') as vm,
pg_relation_size(20306, 'init') as init,
pg_table_size(20306), pg_indexes_size(20306) as indexes,
pg_total_relation_size(20306) as total;
main | fsm | vm | init | pg_table_size | indexes | total
--------+-------+------+------+---------------+---------+--------
253952 | 24576 | 8192 | 0 | 286720 | 196608 | 483328
(1 row)
From that, you can tell pg_table_sizeis the sum of all the return values of pg_relation_size. And pg_total_relation_sizeis the sum of pg_table_sizeand pg_indexes_size.
从中，您可以看出pg_table_size是 的所有返回值的总和pg_relation_size。和pg_total_relation_size是的总和pg_table_size和pg_indexes_size。
If you want to know how much space your tables are using, use pg_table_sizeand pg_total_relation_sizeto think about them -- one number is table-only, and one number is table + indexes.
如果您想知道您的表使用了多少空间，请使用pg_table_size并pg_total_relation_size考虑它们——一个数字是仅限表的，一个数字是表 + 索引。
Check the storage file layoutfor some info about what fsm, vm, and initmean, and how they're stored on disk.
检查存储文件格式为什么一些信息fsm，vm和init平均值，以及他们是如何存储在磁盘上。
回答by Rachad Abi Chahine
pg_table_size:Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)
pg_table_size:指定表使用的磁盘空间，不包括索引（但包括TOAST、空闲空间映射和可见性映射）
pg_relation_size:The size of the main data fork of the relation,
pg_relation_size:关系的主数据叉的大小，
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid, 'main')) as relation_size_main,
pg_size_pretty(pg_relation_size(relid, 'fsm')) as relation_size_fsm,
pg_size_pretty(pg_relation_size(relid, 'vm')) as relation_size_vm,
pg_size_pretty(pg_relation_size(relid, 'init')) as relation_size_init,
pg_size_pretty(pg_table_size(relid)) as table_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size
from pg_catalog.pg_statio_user_tables
where schemaname = 'XXXX'
and relname like 'XXXXXX';
total_size | 6946 MB
relation_size_main | 953 MB
relation_size_fsm | 256 kB
relation_size_vm | 32 kB
relation_size_init | 0 bytes
table_size | 6701 MB
external_size | 5994 MB
so pg_table_size is not onlt the sum of all the return values of pg_relation_size but you have to add toast size
所以 pg_table_size 不是 pg_relation_size 的所有返回值的总和，但你必须添加 toast 大小
toast_bytes | 5748 MB
toast_bytes | 5748 MB
