PostgreSQL的视图pg_indexes

PostgreSQL的视图pg_indexes

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

pg_indexes 是 PostgreSQL 中的一个系统视图，用于显示数据库中的索引信息。通过查询 pg_indexes 视图，可以获取关于所有索引的详细信息，包括索引所属表、模式（schema）、索引定义等。

pg_indexes 视图的主要列

列名	类型	描述
schemaname	name	索引所属的模式（schema）名。
tablename	name	索引所属的表的名字。
indexname	name	索引的名字。
indexdef	text	索引的定义（DDL 语句）。

示例查询

以下是一些常用的查询示例，可以帮助你获取和分析索引信息。

查看所有索引

SELECT * FROM pg_indexes;

查看特定模式下的所有索引

white=# SELECT * FROM pg_indexes WHERE schemaname = 'yewu1';
 schemaname | tablename | indexname | tablespace |                         indexdef                         
------------+-----------+-----------+------------+----------------------------------------------------------
 yewu1      | t4        | idx_t4    |            | CREATE INDEX idx_t4 ON yewu1.t4 USING btree (id)
 yewu1      | t4        | idx_t2    |            | CREATE INDEX idx_t2 ON yewu1.t4 USING btree (id)
 yewu1      | t4        | t4_pkey   |            | CREATE UNIQUE INDEX t4_pkey ON yewu1.t4 USING btree (id)
(3 rows)

查看特定表的所有索引

white=# SELECT * FROM pg_indexes WHERE tablename = 't1';
 schemaname | tablename | indexname | tablespace |                     indexdef                     
------------+-----------+-----------+------------+--------------------------------------------------
 yewu1      | t1        | idx_t1    |            | CREATE INDEX idx_t1 ON yewu1.t1 USING btree (id)
(1 row)

获取所有唯一索引的信息

SELECT * FROM pg_indexes 
WHERE indexdef LIKE '%UNIQUE%';

分析索引使用情况

可以结合 pg_stat_user_indexes 和 pg_stat_user_tables 视图来分析索引的使用情况。例如：

SELECT 
    idx.schemaname,
    idx.tablename,
    idx.indexname,
    stat.idx_scan AS index_scans,
    stat.idx_tup_read AS tuples_read,
    stat.idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_user_indexes stat
JOIN 
    pg_indexes idx ON stat.indexrelname = idx.indexname
ORDER BY 
    index_scans DESC;

white=# SELECT 
white-#     idx.schemaname,
white-#     idx.tablename,
white-#     idx.indexname,
white-#     stat.idx_scan AS index_scans,
white-#     stat.idx_tup_read AS tuples_read,
white-#     stat.idx_tup_fetch AS tuples_fetched
white-# FROM 
white-#     pg_stat_user_indexes stat
white-# JOIN 
white-#     pg_indexes idx ON stat.indexrelname = idx.indexname
white-# ORDER BY 
white-#     index_scans DESC;
 schemaname |    tablename     |       indexname       | index_scans | tuples_read | tuples_fetched 
------------+------------------+-----------------------+-------------+-------------+----------------
 yewu2      | t4               | idx_t4                |           0 |           0 |              0
 yewu1      | t4               | idx_t4                |           0 |           0 |              0
 public     | pgbench_branches | pgbench_branches_pkey |           0 |           0 |              0
 public     | pgbench_tellers  | pgbench_tellers_pkey  |           0 |           0 |              0
 public     | pgbench_accounts | pgbench_accounts_pkey |           0 |           0 |              0
 yewu1      | t4               | idx_t2                |           0 |           0 |              0
 yewu2      | t4               | idx_t4                |           0 |           0 |              0
 yewu1      | t4               | idx_t4                |           0 |           0 |              0
 yewu1      | t4               | t4_pkey               |           0 |           0 |              0
 yewu1      | t1               | idx_t1                |           0 |           0 |              0
(10 rows)

该查询会列出各个索引的扫描次数、读取的元组数以及获取的元组数，帮助你分析哪些索引被频繁使用。

定位和优化

1. 定位未使用的索引：通过分析 pg_stat_user_indexes，可以确定哪些索引长时间未被使用，从而决定是否要删除这些索引来优化数据操作性能。
2. 索引重建：若发现某些索引在查询计划中经常被使用，但性能不佳，可以考虑重建这些索引以改善性能。

   REINDEX INDEX 索引名;
   

通过 pg_indexes 视图，数据库管理员可以方便地查看和管理索引，提高查询性能和数据库维护的效率。