PostgreSQL维护索引相关查询

PostgreSQL 具有一组丰富的索引功能，人们开发数据库一段时间后，当需要对软件架构进行更改时，他们忘记了对以前的索引进行清理。这种方法会造成混乱，有时会因为索引过多而减慢数据库的速度。

1 ， 表上有多少索引？是否唯一？表与索引容量

SELECT CONCAT(n.nspname,'.', c.relname) AS table,
 i.relname AS index_name
,indisunique is_unique
,pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
          pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
          pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size 
 FROM pg_class c
 JOIN pg_index x ON c.oid = x.indrelid
 JOIN pg_class i ON i.oid = x.indexrelid 
 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'test1';

    table     | index_name | is_unique | table_size | index_size | total_size
--------------+------------+-----------+------------+------------+------------
 public.test1 | idx_id1    | f         | 8192 bytes | 16 kB      | 40 kB
 public.test1 | idx_id2    | f         | 8192 bytes | 16 kB      | 40 kB
(2 行记录)

2, 索引的创建语句

SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index 
WHERE indrelid = 'test1'::regclass;
                      index_query
-------------------------------------------------------
 CREATE INDEX idx_id1 ON public.test1 USING btree (id)
 CREATE INDEX idx_id2 ON public.test1 USING btree (id)
(2 行记录)

3, 获取特定索引支持的数据类型列表
PostgreSQL有许多索引方法，如BTree，Hash，BRIN，GIST和GIN。

weejar=# select distinct amname from pg_am;
 amname
--------
 brin
 gin
 hash
 heap
 spgist
 gist
 btree

SELECT amname,
       opfname
FROM   pg_opfamily,
       pg_am
WHERE  opfmethod = pg_am.oid
AND    amname = 'hash';
 amname |      opfname
--------+--------------------
 hash   | array_ops
 hash   | bpchar_ops
 hash   | char_ops
 hash   | date_ops
 hash   | float_ops
...

4, 查询未使用索引
长时间如果index_scans为 0 或接近 0，可以简单认为该索引未使用unsed,考虑删除

SELECT s.relname AS table_name,
       indexrelname AS index_name,
       i.indisunique,
       idx_scan AS index_scans
FROM   pg_catalog.pg_stat_user_indexes s,
       pg_index i
WHERE  i.indexrelid = s.indexrelid and idx_scan=0;

table_name |  index_name  | indisunique | index_scans
------------+--------------+-------------+-------------
 company    | company_pkey | t           |           0
 brand      | brand_pkey   | t           |           0
 t          | t_pkey       | t           |           0
 test1      | idx_id1      | f           |           0
 test1      | idx_id2      | f           |           0

5, 重复的索引
在postgresql中同一列可以重复创建索引，没有必要在表上有多个具有不同名称的相同索引。

SELECT   indrelid::regclass table_name,
         att.attname column_name,
         amname index_method
FROM     pg_index i,
         pg_class c,
         pg_opclass o,
         pg_am a,
         pg_attribute att
WHERE    o.oid = ALL (indclass) 
AND      att.attnum = ANY(i.indkey)
AND      a.oid = o.opcmethod
AND      att.attrelid = c.oid
AND      c.oid = i.indrelid
GROUP BY table_name, 
         att.attname,
         indclass,
         amname, indkey
HAVING count(*) > 1;

table_name | column_name | index_method
------------+-------------+--------------
 test1      | id          | btree

— over —