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 的原理以及哪些操作可能堵塞索引的建立》