作者
digoal
日期
2018-04-27
标签
PostgreSQL , 索引 , invalid
背景
某些时候，可能想避免一些索引的影响，特意让优化器不选择使用某些索引。
通常的做法可能有：
1、HINT
《关键时刻HINT出彩 - PG优化器的参数优化、执行计划固化CASE》
《PostgreSQL SQL HINT的使用(pg_hint_plan)》
2、设置开关，（注意它不能只影响某一个索引，会影响一片）
```
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_parallel_append = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
enable_partitionwise_join = off
enable_partitionwise_aggregate = off
enable_parallel_hash = on
enable_partition_pruning = on
```
还有一种做法是，把索引设置为invalid，此时优化器不会使用这个索引，同时数据有更新，写入时依旧会更新这个索引。
实际上在CREATE INDEX CONCURRENTLY时完成第一阶段后，索引实际上就是INVALID的，但是数据的DML依旧会对INVALID的索引产生修改，所以可以保证索引本身的完整性。只是优化器不用它而已。
修改元数据来实现invalid index
1、创建测试表
postgres=# create table ii (id int primary key, info text);
CREATE TABLE
2、创建测试索引
postgres=# create index i_ii on ii(info);
CREATE INDEX
3、写入测试数据几条
postgres=# insert into ii values (1,'test');
INSERT 0 1
4、使用索引扫描，查询到目标数据
```
postgres=# set enable_seqscan=off;
SET
postgres=# set enable_bitmapscan=off;
SET
postgres=# select * from ii where info='test';
id | info
----+------
1 | test
(1 row)
postgres=# explain select * from ii where info='test';
QUERY PLAN
Index Scan using i_ii on ii (cost=0.16..13.81 rows=26 width=36)
Index Cond: (info = 'test'::text)
(2 rows)
```
5、更新元数据，将这个索引设置为INVALID
postgres=# update pg_index set indisvalid=false where indexrelid='i_ii'::regclass;
UPDATE 1
6、重新执行查询，优化器不会再选择索引扫描，而是使用了全表扫描
```
postgres=# explain select * from ii where info='test';
QUERY PLAN
Seq Scan on ii (cost=10000000000.00..10000000073.88 rows=26 width=36)
Filter: (info = 'test'::text)
JIT:
Functions: 2
Inlining: true
Optimization: true
(6 rows)
postgres=# select * from ii where info='test';
id | info
----+------
1 | test
(1 row)
```
7、在将索引设置为invalid后，再次写入若干数据
```
postgres=# insert into ii values (2,'test1');
INSERT 0 1
postgres=# insert into ii values (3,'test3');
INSERT 0 1
postgres=# explain select * from ii where info='test';
QUERY PLAN
Seq Scan on ii (cost=10000000000.00..10000000073.88 rows=26 width=36)
Filter: (info = 'test'::text)
JIT:
Functions: 2
Inlining: true
Optimization: true
(6 rows)
postgres=# select * from ii where info='test1';
id | info
----+-------
2 | test1
(1 row)
postgres=# insert into ii select generate_series(4,100000),md5(random()::Text);
INSERT 0 99997
```
8、更新元数据，将索引恢复为VALID
postgres=# update pg_index set indisvalid=true where indexrelid='i_ii'::regclass;
UPDATE 1
9、查看执行计划，使用了索引扫描
```
postgres=# explain select * from ii where info='test';
QUERY PLAN
Index Scan using i_ii on ii (cost=0.29..2.71 rows=1 width=37)
Index Cond: (info = 'test'::text)
(2 rows)
```
10、使用索引扫描，可以找到在INVALID索引后，写入的数据。
```
postgres=# select * from ii where info='test1';
id | info
----+-------
2 | test1
(1 row)
postgres=# select * from ii where info='test2';
id | info
----+------
(0 rows)
postgres=# select * from ii where info='test3';
id | info
----+-------
3 | test3
(1 row)
postgres=# explain select * from ii where info='test3';
QUERY PLAN
Index Scan using i_ii on ii (cost=0.29..2.71 rows=1 width=37)
Index Cond: (info = 'test3'::text)
(2 rows)
postgres=# select * from ii where id=99999;
id | info
-------+----------------------------------
99999 | 54382fc94aba553b8972ce2657a7bdfb
(1 row)
postgres=# explain select * from ii where info='54382fc94aba553b8972ce2657a7bdfb';
QUERY PLAN
Index Scan using i_ii on ii (cost=0.29..2.71 rows=1 width=37)
Index Cond: (info = '54382fc94aba553b8972ce2657a7bdfb'::text)
(2 rows)
postgres=# select * from ii where info='54382fc94aba553b8972ce2657a7bdfb';
id | info
-------+----------------------------------
99999 | 54382fc94aba553b8972ce2657a7bdfb
(1 row)
```
相信未来PG内核会扩展ALTER INDEX或ALTER TABLE的语法，在语法层面支持INVALID INDEX。
参考
《PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
