17.PG索引核心

关于pg索引，上文提到了创建，删除等一些基础的操作，这里说说核心的东西

1.PG索引的视图和表pg_indexes和pg_index

　　1) pg_indexes  

postgres=# \d pg_indexes
            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

　　查看表contacts的索引

postgres=# select * from pg_indexes where schemaname='public' and tablename='contacts';
-[ RECORD 1 ]---------------------------------------------------------------------
schemaname | public
tablename  | contacts
indexname  | contacts_pkey
tablespace | 
indexdef   | CREATE UNIQUE INDEX contacts_pkey ON public.contacts USING btree (id)
-[ RECORD 2 ]---------------------------------------------------------------------
schemaname | public
tablename  | contacts
indexname  | idx_name_old
tablespace | 
indexdef   | CREATE INDEX idx_name_old ON public.contacts USING btree (name)

　　2）pg_index   -- 查看索引的详细信息

postgres=# \d pg_index
                     Table "pg_catalog.pg_index"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 indexrelid          | oid          |           | not null | 
 indrelid            | oid          |           | not null | 
 indnatts            | smallint     |           | not null | 
 indnkeyatts         | smallint     |           | not null | 
 indisunique         | boolean      |           | not null | 
 indnullsnotdistinct | boolean      |           | not null | 
 indisprimary        | boolean      |           | not null | 
 indisexclusion      | boolean      |           | not null | 
 indimmediate        | boolean      |           | not null | 
 indisclustered      | boolean      |           | not null | 
 indisvalid          | boolean      |           | not null | 
 indcheckxmin        | boolean      |           | not null | 
 indisready          | boolean      |           | not null | 
 indislive           | boolean      |           | not null | 
 indisreplident      | boolean      |           | not null | 
 indkey              | int2vector   |           | not null | 
 indcollation        | oidvector    |           | not null | 
 indclass            | oidvector    |           | not null | 
 indoption           | int2vector   |           | not null | 
 indexprs            | pg_node_tree | C         |          | 
 indpred             | pg_node_tree | C         |          | 
Indexes:
    "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid)
    "pg_index_indrelid_index" btree (indrelid)

　　查看索引的执行计划

postgres=# explain select * from testdb where note='l2';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on testdb  (cost=4.20..13.67 rows=6 width=36)
   Recheck Cond: (note = 'l2'::text)
   ->  Bitmap Index Scan on idx_testdb_note  (cost=0.00..4.20 rows=6 width=0)             --- 这里使用了索引扫描，用的时idx_testdb_note索引
         Index Cond: (note = 'l2'::text)
(4 rows)

postgres=# drop index idx_testdb_note;
DROP INDEX
postgres=# \d testdb;
               Table "public.testdb"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 note   | text    |           |          | 
Indexes:
    "testdb_pkey" PRIMARY KEY, btree (id)

postgres=# explain select * from testdb where note='l2';
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on testdb  (cost=0.00..25.88 rows=6 width=36)                                  --- 这里发现没有使用索引
   Filter: (note = 'l2'::text)

2.索引的维护

　　1） 避免重复索引

-- 查看是否有重复索引
postgres=# select  indrelid::regclass as tablename,array_agg(indexrelid::regclass) as indexes from pg_index group by indrelid,indkey having count(*) >1;
 tablename | indexes 
-----------+---------
(0 rows)

　　2）清除未使用的索引

　         索引长时间未使用，那么这些索引可能是错误地创建的，这些未使用的索引不但会占用空间使得数据的插入、修改和删除的成本增加，以及备份的成本的增加，清理这些未使用的索引对系统整体性能的提升有很大的帮助你。

　　　　可以通过pg_stat_user_indexes统计视图，了解到最近是否使用过某个索引，

postgres=# \d pg_stat_user_indexes
                  View "pg_catalog.pg_stat_user_indexes"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 relid         | oid                      |           |          | 
 indexrelid    | oid                      |           |          | 
 schemaname    | name                     |           |          | 
 relname       | name                     |           |          | 
 indexrelname  | name                     |           |          | 
 idx_scan      | bigint                   |           |          | 
 last_idx_scan | timestamp with time zone |           |          | 
 idx_tup_read  | bigint                   |           |          | 
 idx_tup_fetch | bigint                   |           |          | 

postgres=# select relname,indexrelname,idx_scan from pg_catalog.pg_stat_user_indexes;   
 relname  | indexrelname  | idx_scan 
----------+---------------+----------
 contacts | contacts_pkey |        0
 contacts | idx_name_old  |        0
 testdb   | testdb_pkey   |        1                   -- 这里可以看出这个这个索引是使用过了1次。
(3 rows)

　　检查无效的索引　　

postgres=# select indexrelid,indisvalid from pg_index where indisvalid='f';
 indexrelid | indisvalid 
------------+------------
(0 rows)