PostgreSQL 改元信息 实现 invalid index

标签

PostgreSQL , 索引 , invalid

https://github.com/digoal/blog/blob/master/201804/20180427_02.md#%E8%83%8C%E6%99%AF 背景

某些时候，可能想避免一些索引的影响，特意让优化器不选择使用某些索引。

通常的做法可能有：

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的索引产生修改，所以可以保证索引本身的完整性。只是优化器不用它而已。

https://github.com/digoal/blog/blob/master/201804/20180427_02.md#%E4%BF%AE%E6%94%B9%E5%85%83%E6%95%B0%E6%8D%AE%E6%9D%A5%E5%AE%9E%E7%8E%B0invalid-index 修改元数据来实现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。

https://github.com/digoal/blog/blob/master/201804/20180427_02.md#%E5%8F%82%E8%80%83 参考

《PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建》