PostgreSQL 索引大小和值编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46470030/
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 index size and value number
提问by oved_s
I am trying to get statistics on indexes. I am looking for total values in an index and it size.
我正在尝试获取有关索引的统计信息。我正在寻找索引中的总值及其大小。
I can only find the size of all indexes on the table. Table pg_class column relpages and reltuples shows the values for the table and not on specific index level.
我只能找到表上所有索引的大小。表 pg_class 列 relpages 和 reltuples 显示了表的值，而不是特定索引级别的值。
In addition, function pg_indexes_size takes table name as an argument and return the total index size for that table.
此外，函数 pg_indexes_size 将表名作为参数并返回该表的总索引大小。
is there a way to get the size and row number on index level? I am using PostgreSQL 9.3.
有没有办法在索引级别获取大小和行号？我正在使用 PostgreSQL 9.3。
Thanks in advance for your help
在此先感谢您的帮助
回答by Vao Tsun
pg_table_size('index_name')for individual index - but it will only show you the size on disk, not the amount of data
pg_table_size('index_name')对于单个索引 - 但它只会显示磁盘大小，而不是数据量
count(1)to get the exact current mount of rows
count(1)获取确切的当前行数
sum(pg_column_size(column_name)) from table_namefor estimations on column data amount
sum(pg_column_size(column_name)) from table_name用于估计列数据量
you can try yourself smth like:
你可以试试自己喜欢的东西：
t=# \di+ tbl*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------------+-------+----------+----------------+--------+-------------
public | tbl_pkey | index | postgres | tbl | 156 MB |
public | tbl_unpic | index | postgres | tbl | 46 MB |
public | tbl_x1 | index | postgres | tbl | 57 MB |
(3 rows)
t=# \dt+ tbl
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------+-------+----------+-------+-------------
public | tbl | table | postgres | 78 MB |
(1 row)
t=# select pg_size_pretty(pg_total_relation_size('tbl'));
pg_size_pretty
----------------
337 MB
(1 row)
t=# select 78+57+46+156;
?column?
----------
337
(1 row)
and to check how psql gets the individual index size, run it with psql -E..
并检查 psql 如何获取单个索引大小，请使用psql -E..
and once again - functions above work with size it takes of disk - it may/(may not) be extrimely different from real amount of data. vacuuming helps here
再一次 - 上面的函数使用它占用的磁盘大小 - 它可能/（可能不会）与实际数据量完全不同。吸尘在这里有帮助
updateI don't know where you directly get the number of "rows" in index, thus can offer only indirect way. Eg let me have partial index, so "number of rows" is different from table. I can check estimations with EXPLAIN (of course you have to repeat where clause for that) checking the rows=66800in Index Only Scan usinggives me an idea on number of rows in that index (actually it is rows=64910which you can get by explain analyzeor just running count(1)). I can't find relevant info in pg_stats - maybe there's some formula... dunno.
更新我不知道您从哪里直接获得索引中的“行”数，因此只能提供间接方式。例如让我有部分索引，所以“行数”与表不同。我可以用 EXPLAIN 检查估计（当然你必须为此重复 where 子句）检查rows=66800inIndex Only Scan using给我一个关于该索引中的行数的想法（实际上它是rows=64910你可以通过explain analyze或只是运行count(1)）。我在 pg_stats 中找不到相关信息 - 也许有一些公式......不知道。
