如何在PostgreSQL中列出表的所有索引以及它们对应的大小？

Question

我可以使用以下语句查看表中所有索引的总大小:

SELECT pg_size_pretty (pg_indexes_size('table_name'));

还可以使用以下语句查看特定索引的大小:

select pg_size_pretty(pg_relation_size('index_name'));,

但我希望获取一个单独列出每个索引大小信息的列表 (每个索引名称及其大小信息).

- Z. M.

Answer 1

使用pg_indexes。

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';

- klin

Answer 2

您可以使用\di+ psql 命令：

postgres=> \di+ schema.*
                           List of relations
 Schema |  Name  | Type  | Owner | Table  | Persistence |  Size  | Description
--------+--------+-------+-------+----------------------+--------+-------------
 schema | index1 | index | owner | table1 | permanent   | 139 MB |
 schema | index2 | index | owner | table1 | permanent   | 77 MB  |
 schema | index3 | index | owner | table1 | permanent   | 73 MB  |
 schema | index4 | index | owner | table1 | permanent   | 38 MB  |
(4 rows)

- Franklin Amorim