关于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)