标签
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。