PostgreSQL查看索引信息

原创多米爸比 2022-01-19

14916

查询表的索引相关信息，有多种不同的方式可以适合不同场景的场景。

1.查询pg_class中索引的名称

select relname from pg_class where relkind='i' and relowner=...

我们可以根据owner查询，根据relowner是一个oid，比较通用的方式是使用子查询

relowner=(select oid from pg_roles where rolname='test');

这样来查询test用户的索引名称。不过我们也可以使用oid的别名类型（regclass、regtype、regproc、regrole、regnamespace）这样将会很方便。

oid的别名类型也有两种使用形式，一种是直接使用别名类型，还有一种是使用函数(to_regclass、to_regnamespace、to_regrole)，例如将test用户转为regrole类型：

postgres=> select 'test'::regrole;
 regrole 
---------
 test
(1 row)

postgres=> select to_regrole('test');
 to_regrole 
------------
 test
(1 row)

所以上面的查询语句我们可以改写为：

select relname 
  from pg_class 
 where relkind='i' 
   and relowner = 'test'::regrole;

或者是

select relname 
  from pg_class 
 where relkind='i' 
   and relowner = to_regrole('test');

第一种写法简便些，适用于psql或者脚本；第二种写法更标准。

2.查看单个对象上的索引信息

在psql客户端里可以使用元命令\d查看某个对象上的索引信息

postgres=> \d t1
                  Table "test.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Indexes:
    "idx_t1" btree (id)

也可以打开ECHO_HIDDEN查看元命令执行的具体语句

postgres=> \set ECHO_HIDDEN on

3.查看所有的索引信息

在psql客户端里可以使用元命令\di查看索引信息列表

postgres=> \di
              List of relations
 Schema |    Name    | Type  | Owner | Table 
--------+------------+-------+-------+-------
 public | idx_t1_pub | index | test  | t1
 test   | idx_t1     | index | test  | t1
 test   | idx_t2     | index | test  | t2
 test   | idx_t3     | index | test  | t3
 test   | pk_test    | index | test  | test
 test   | ui1        | index | test  | t6
(6 rows)

4.查询pg_indexes系统视图

pg_indexes视图可以查看模式名称、表名称、索引定义等信息

postgres=> select schemaname,tablename,indexname,indexdef 
postgres-> from pg_indexes 
postgres-> where schemaname='test';
 schemaname | tablename | indexname  |                                                      indexdef                                                       
------------+-----------+------------+---------------------------------------------------------------------------------------------------------------------
 test       | test2     | uk_test2   | CREATE UNIQUE INDEX uk_test2 ON test.test2 USING btree (b, c)
 test       | t1        | idx_t1     | CREATE INDEX idx_t1 ON test.t1 USING btree (id)
 test       | t2        | idx_t2     | CREATE INDEX idx_t2 ON test.t2 USING btree (id)
 test       | t3        | idx_t3     | CREATE INDEX idx_t3 ON test.t3 USING btree (id)
 test       | t6        | ui1        | CREATE INDEX ui1 ON test.t6 USING btree (COALESCE(a, ''::character varying), COALESCE(b, ''::character varying), c)
(5 rows)

5.自定义视图查询

下面的查询视图根据可见性规则查询所有schema下的索引信息

CREATE OR REPLACE VIEW view_index AS
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname,t.relname,c.relname;

然后查询view_index视图

postgres=> select * from view_index;
 schema | table |   index    |                                                         def                                                         
--------+-------+------------+---------------------------------------------------------------------------------------------------------------------
 public | t1    | idx_t1_pub | CREATE INDEX idx_t1_pub ON public.t1 USING btree (id)
 test   | t1    | idx_t1     | CREATE INDEX idx_t1 ON test.t1 USING btree (id)
 test   | t2    | idx_t2     | CREATE INDEX idx_t2 ON test.t2 USING btree (id)
 test   | t3    | idx_t3     | CREATE INDEX idx_t3 ON test.t3 USING btree (id)
 test   | t6    | ui1        | CREATE INDEX ui1 ON test.t6 USING btree (COALESCE(a, ''::character varying), COALESCE(b, ''::character varying), c)
 test   | test  | pk_test    | CREATE UNIQUE INDEX pk_test ON test.test USING btree (a, b)
(6 rows)