PostgreSQL索引大小和值数量

Question

我正在尝试获取索引的统计信息。我想要的是索引中的总值和它的大小。

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

此外，函数 pg_indexes_size 以表名作为参数，并返回该表的总索引大小。

有没有一种方法可以获取索引级别的大小和行数？我正在使用 PostgreSQL 9.3。

- oved_s

---

pg_stat_all_indexes是什么？ - Vao Tsun

pg_stat_all_indexes 只显示有关读取/提取/扫描的统计信息，而不是索引中实际值的大小和数量。 - oved_s

是的，而你提到的 pg_indexes_size 显示的是索引在磁盘上占用的空间大小，而不是“实际数据量” - 我以为你可以接受一些近似值？ :) - Vao Tsun

pg_indexes_size 显示表的总索引大小。我想要特定的索引大小。 - oved_s

针对单个索引，请使用 pg_table_size('index_name') 函数获取其在磁盘上的大小，但这只能显示索引本身的大小，无法显示其中的数据量。 - Vao Tsun

这里有很多例子：https://wiki.postgresql.org/wiki/Index_Maintenance - jmny

Answer 1

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

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运行。

再次强调-上面的函数用于处理磁盘占用大小-它与实际数据量可能相差很大。这时候需要进行Vacuum操作。

---

更新

我不知道你直接从索引中获取"行数"的位置，因此我只能提供间接的方法。例如，让我拥有一个部分索引，所以索引中的“行数”与表中的行数不同。我可以通过使用EXPLAIN（当然必须重复使用WHERE子句）检查Index Only Scan using中的rows=66800来估算索引中的行数（实际上是rows=64910，可以通过explain analyze 或者直接运行count(*)来获取）。我无法在pg_stats中找到相关信息-也许有一些公式，我不知道。

- Vao Tsun

---

Vao Tsun，非常感谢您的回答，但也许我并不完全理解Pg索引维护。我运行了以下命令：create table stg.test_index_size as select t.id, case when t.id<25000 then 'small' else 'high' end as flag from generate_series(1,50000) as t(id); create unique index idx_id on stg.test_index_size(id); create index idx_flag on stg.test_index_size(flag); vacuum analyze stg.test_index_size; 现在当我使用pg_table_size(quote_ident(indexrelname)::text)检查两个索引的大小时，我得到了相同的结果。我原本期望flag索引会更小。 - oved_s

关于统计表的观点很中肯 - 已编辑答案 - 谢谢。至于其他问题 - 最好另开一个新问题来问。 - Vao Tsun

另外，pg_stat_all_indexes并不显示索引的估计行数，而只显示idx_scan、idx_tup_read和idx_tup_fetch。我期望得到idx_id 50000和idx_flag = 2。 - oved_s

Answer 2

此SQL将为您提供表格和索引大小的详细信息

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

- akshay jain

---

pg_size_pretty(pg_indexes_size(relid)) as "Index Size" 作为“索引大小”， - Dek4nice

请注意，“Actual Size”指的是“用于长列的未使用TOAST的表数据”。您可能想使用pg_size_pretty（pg_table_size（relid））as“Data Size”，这将为您提供数据大小+索引大小=总大小。有关更多信息，请参见https://dev59.com/QlgQ5IYBdhLWcg3w7ofY#70397779。 - Samuli Asmala

Answer 3

这是检索以下信息的查询：

• 总大小

• 所有索引的总大小

• 表大小

• 索引大小

• 估计的表行数

 SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
 pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
 pg_size_pretty(pg_relation_size(relid)) as "Table Size",
 pg_size_pretty(pg_relation_size(indexrelid)) "Index Size",
 reltuples::bigint "Estimated table row count"
 FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
 WHERE i.relname='uploads'

也许对某些人来说这会很有用。

也许对某些人来说这会很有用。

- Elvin Ahmadov