pg_indexes 是一个视图，可以通过它获取某个表的索引信息。pg_indexes的定义如下：
SELECT
n.nspname AS schemaname,
c.relname AS tablename,
i.relname AS indexname,
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND i.relkind = 'i'::"char";
例如从 pg_indexes中获取pg系统表pg_index表的索引信息：
select * from pg_indexes where tablename = 'pg_index';
结果如下：
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+---------------------------+------------+-----------------------------------------------------------------------------------------------
pg_catalog | pg_index | pg_index_indrelid_index | | CREATE INDEX pg_index_indrelid_index ON pg_catalog.pg_index USING btree (indrelid)
pg_catalog | pg_index | pg_index_indexrelid_index | | CREATE UNIQUE INDEX pg_index_indexrelid_index ON pg_catalog.pg_index USING btree (indexrelid)
(2 rows)
如果要获取索引的更多属性信息，则需要通过PostgreSQL的系统表 pg_index来获取，pg_index表的定义如下：
Table "pg_catalog.pg_index"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+--------------+-----------+----------+---------+----------+--------------+-------------
indexrelid | oid | | not null | | plain | |
indrelid | oid | | not null | | plain | |
indnatts | smallint | | not null | | plain | |
indisunique | boolean | | not null | | plain | |
indisprimary | boolean | | not null | | plain | |
indisexclusion | boolean | | not null | | plain | |
indimmediate | boolean | | not null | | plain | |
indisclustered | boolean | | not null | | plain | |
indisvalid | boolean | | not null | | plain | |
indcheckxmin | boolean | | not null | | plain | |
indisready | boolean | | not null | | plain | |
indislive | boolean | | not null | | plain | |
indisreplident | boolean | | not null | | plain | |
indkey | int2vector | | not null | | plain | |
indcollation | oidvector | | not null | | plain | |
indclass | oidvector | | not null | | plain | |
indoption | int2vector | | not null | | plain | |
indexprs | pg_node_tree | | | | extended | |
indpred | pg_node_tree | | | | extended | |
Indexes:
"pg_index_indexrelid_index" UNIQUE, btree (indexrelid)
"pg_index_indrelid_index" btree (indrelid)
例如：获取 pg_index 表自身的 index 信息：
select * from pg_index where indrelid in (select oid from pg_class where relname = 'pg_index')
其中 indrelid是 pg_class表中的oid字段。
查询结果如下：
-[ RECORD 1 ]--+-----
indexrelid | 2678
indrelid | 2610
indnatts | 1
indisunique | f
indisprimary | f
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 2
indcollation | 0
indclass | 1981
indoption | 0
indexprs |
indpred |
-[ RECORD 2 ]--+-----
indexrelid | 2679
indrelid | 2610
indnatts | 1
indisunique | t
indisprimary | f
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 1
indcollation | 0
indclass | 1981
indoption | 0
indexprs |
indpred |
其中indexrelid字段就是系统表 pg_class中的 oid字段。
所以查看系统表pg_index自身的索引名字的SQL如下：
select relname from pg_class where oid in
(select indexrelid from pg_index where indrelid in
(select oid from pg_class where relname = 'pg_index')
)
查询结果如下：
relname
---------------------------
pg_index_indexrelid_index
pg_index_indrelid_index
