PostgreSQL索引维护看完这篇就够了

1.创建索引

1. CREATE TABLE test1 (id integer, content varchar);
   
2. CREATE INDEX test1_id_index ON test1 (id);
   

2. 查看某表的索引

1. \d test1
   

3.查看表索引的数量

test1指需要查看的表名

1. SELECT CONCAT(n.nspname,'.', c.relname) AS table,
   
2.    i.relname AS index_name FROM pg_class c
   
3.     JOIN pg_index x ON c.oid = x.indrelid
   
4.     JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   
5.  WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'test1';
   

4.查看索引统计信息

通过 pg_stat_user_indexes 视图可以查看索引的使用情况和性能统计信息

1. SELECT * FROM pg_stat_user_indexes WHERE relname = 'test1';
   

5.检查索引唯一性

索引是一项解决数据库性能功能，但与此同时，它也可用于确保唯一性。但是，为了确保唯一性，我们需要一种称为唯一索引的单独索引类型。为了检查索引是否唯一，pg_index有一个名为indisunique的列来标识索引的唯一性。

1. SELECT    i.relname AS index_name,
   
2.          indisunique is_unique
   
3. FROM      pg_class c
   
4. JOIN      pg_index x ON c.oid = x.indrelid
   
5. JOIN      pg_class i ON i.oid = x.indexrelid
   
6. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   
7. WHERE     c.relkind = ANY (ARRAY['r', 't'])
   
8. AND       c.relname LIKE 'test1';
   

6.查看索引的大小

这是一种非常简单的方法来获取PostgreSQL索引的大小，test1_id_index指建的索引名。

1. SELECT pg_size_pretty(pg_relation_size('test1_id_index'));
   

7.查看索引、表、两者总大小

1. SELECT    CONCAT(n.nspname,'.', c.relname) AS table,
   
2.          i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
   
3.          pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
   
4.          pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c
   
5. JOIN      pg_index x ON c.oid = x.indrelid
   
6. JOIN      pg_class i ON i.oid = x.indexrelid
   
7. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   
8. WHERE     c.relkind = ANY (ARRAY['r', 't'])
   
9. AND       n.oid NOT IN (99, 11, 12375);
   

8.获取索引创建语句

该查询将显示索引创建语句

1. SELECT pg_get_indexdef(indexrelid) AS index_query
   
2. FROM   pg_index WHERE  indrelid = 'test1'::regclass;
   

9.重新构建索引

如果索引损坏或过大，则需要再次构建该索引。同时，不想阻塞表上的操作，可以选择使用REINDEX CONCURRENTLY命令。

1. REINDEX INDEX CONCURRENTLY test1_id_index;
   

10.索引支持的数据类型

1. SELECT amname,opfname FROM pg_opfamily,pg_am WHERE opfmethod = pg_am.oid AND amname = 'btree';
   

11.查找从未使用过的索引

如果index_scans为0或接近0，则可以删除这些索引。

1. SELECT s.relname AS table_name,
   
2.       indexrelname AS index_name,
   
3.       i.indisunique,
   
4.       idx_scan AS index_scans
   
5. FROM   pg_catalog.pg_stat_user_indexes s,
   
6.       pg_index i
   
7. WHERE  i.indexrelid = s.indexrelid;
   

12.查找重复索引

1. SELECT   indrelid::regclass table_name,
   
2.         att.attname column_name,
   
3.         amname index_method
   
4. FROM     pg_index i,
   
5.         pg_class c,
   
6.         pg_opclass o,
   
7.         pg_am a,
   
8.         pg_attribute att
   
9. WHERE    o.oid = ALL (indclass)
   
10. AND      att.attnum = ANY(i.indkey)
    
11. AND      a.oid = o.opcmethod
    
12. AND      att.attrelid = c.oid
    
13. AND      c.oid = i.indrelid
    
14. GROUP BY table_name,
    
15.         att.attname,
    
16.         indclass,
    
17.         amname, indkey
    
18. HAVING count(*) > 1;