PostgreSQL 获取索引大小

需求

获取索引占用的磁盘空间。

pg_indexes_size

pg_indexes_size函数获取表的所有索引的大小总和。

注意：参数指定的是表名。

select pg_size_pretty(pg_indexes_size('tmp'));

pg_relation_size

pg_relation_size函数获取单个索引的大小。

注意：参数指定的是索引名。

select pg_size_pretty(pg_relation_size('tmp_content_index'));

pg_table_size

pg_table_size函数也能获取单个索引的大小。（虽然函数名称叫table size）

psql中的\di+使用的就是pg_table_size来统计每个索引的大小。

select pg_size_pretty(pg_table_size('tmp_content_index'));

示例

数据准备

create table tmp(id text primary key, content text);
-- 创建 tmp_content_index 索引
create index tmp_content_index on public.tmp (content);

-- 写入数据
insert into tmp select gen_random_uuid() id, 'text'::text content 
from generate_series(1,1000000);

查看表结构

使用\d table_name或\d+ table_name查看表的字段及索引信息。

postgres=# \d+ tmp
                                          Table "public.tmp"
 Column  | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 id      | text |           | not null |         | extended |             |              | 
 content | text |           |          |         | extended |             |              | 
Indexes:
    "tmp_pkey" PRIMARY KEY, btree (id)
    "tmp_content_index" btree (content)
Access method: heap

查看索引总大小

postgres=# select pg_size_pretty(pg_indexes_size('tmp'));
 pg_size_pretty 
----------------
 63 MB
(1 row)

查看单个索引大小

主键索引

postgres=# select pg_size_pretty(pg_relation_size('tmp_pkey'));
 pg_size_pretty 
----------------
 56 MB
(1 row)

普通索引

postgres=# select pg_size_pretty(pg_relation_size('tmp_content_index'));
 pg_size_pretty 
----------------
 6792 kB
(1 row)

\di+ 方式查看索引大小

使用psql -E进入命令行即可看到\d等命令实际执行的SQL。

可以看到\di+使用的是pg_table_size来统计每个索引的大小。

postgres=# \di+
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  c2.relname as "Table",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  am.amname as "Access method",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                                              List of relations
 Schema |        Name        | Type  |  Owner   | Table | Persistence | Access method |  Size   | Description 
--------+--------------------+-------+----------+-------+-------------+---------------+---------+-------------
 public | tmp_content_index  | index | postgres | tmp   | permanent   | btree         | 6792 kB | 
 public | tmp_pkey           | index | postgres | tmp   | permanent   | btree         | 56 MB   | 
(2 rows)

文档

https://www.postgresql.org/docs/16/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE