PostgreSQL-查询所有索引

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

---