PostgreSQL改元資訊實現invalidindex

標籤

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