PostgreSQL 索引大小和值编号 [英] PostgreSQL index size and value number

问题描述

我正在尝试获取有关索引的统计信息.我正在寻找索引中的总值及其大小.

I am trying to get statistics on indexes. I am looking for total values in an index and it size.

我只能找到表上所有索引的大小.表 pg_class 列 relpages 和 reltuples 显示表的值，而不是特定索引级别.

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_indexes_size 将表名作为参数并返回该表的总索引大小.

In addition, function pg_indexes_size takes table name as an argument and return the total index size for that table.

有没有办法在索引级别获取大小和行号?我使用的是 PostgreSQL 9.3.

is there a way to get the size and row number on index level? I am using PostgreSQL 9.3.

预先感谢您的帮助

推荐答案

pg_table_size('index_name') 用于单个索引 - 但它只会显示磁盘上的大小，而不是数据量

pg_table_size('index_name') for individual index - but it will only show you the size on disk, not the amount of data

count(*) 获取当前确切的行数

sum(pg_column_size(column_name)) from table_name 用于估计列数据量

你可以尝试一下:

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)

并检查 psql 如何获取单个索引大小，使用 psql -E..

and to check how psql gets the individual index size, run it with psql -E..

再一次 - 上面的函数使用磁盘大小 - 它可能/(可能不会)与实际数据量有很大不同.吸尘在这里有帮助

and once again - functions above work with size it takes of disk - it may/(may not) be extremely different from real amount of data. vacuuming helps here

更新不知道你直接从哪里得到行数"的在索引中，因此只能提供间接的方式.例如让我有部分索引，所以行数"与表不同.我可以用 EXPLAIN 检查估计(当然你必须重复 where 子句)检查 rows=66800 在 Index Only Scan using 给我一个关于行数的想法在该索引中(实际上它是 rows=64910，您可以通过 explain analysis 或仅运行 count(*) 获得).我在 pg_stats 中找不到相关信息 - 也许有一些公式.我不知道.

update I 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=66800 in Index Only Scan using gives me an idea on number of rows in that index (actually it is rows=64910 which you can get by explain analyze or just running count(*)). I can't find relevant info in pg_stats - maybe there's some formula. I don't know.

这篇关于PostgreSQL 索引大小和值编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！