携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第21天
某些情况下，索引扫描可能并不适合我们当前的需要，但是优化器却选择了索引扫描，这时我们该如何避免使用索引扫描呢？
比较常见的做法有：
1、hint
在oracle中使用hint的方式十分常见，在pg中也支持hint的方法，但是需要安装pg_hint插件，方法见：PostgreSQL hint用法(兼容oracle)
2、优化器开关
我们都知道在pg中有很多优化器开关，例如：
#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
我们可以通过禁用这些优化器开关来避免索引扫描，但是这种方法有一种缺陷：一旦禁用了索引扫描的开关，那么数据库中其它的索引扫描都将无法使用！所以这种方法适用性并不高，只能在测试的时候使用。
3、设置索引为invalid
把索引设置为invalid，此时优化器不会使用这个索引，同时数据有更新，写入时依旧会更新这个索引。这是值得推荐的用法。
–创建测试表：
bill@bill=>create table t1 (id int primary key, info text);
CREATE TABLE
bill@bill=>create index idx_t1 on t1(info);
CREATE INDEX
–插入测试数据：
bill@bill=>insert into t1 values(1,'test');
INSERT 0 1
–使用索引扫描
bill@bill=>set enable_bitmapscan = off;
SET
bill@bill=>explain select * from t1 where info='test';
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_t1 on t1 (cost=0.15..8.06 rows=6 width=36)
Index Cond: (info = 'test'::text)
(2 rows)
–更新元数据，将这个索引设置为INVALID
bill@bill=>update pg_index set indisvalid=false where indexrelid='idx_t1'::regclass;
UPDATE 1
–重新执行查询
可以发现优化器没有选择索引扫描，而是使用全表扫描
bill@bill=>explain select * from t1 where info = 'test1';
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..25.88 rows=1 width=36)
Filter: (info = 'test1'::text)
(2 rows)
–插入其它测试数据
bill@bill=>insert into t1 values(2,'test2');
INSERT 0 1
bill@bill=>insert into t1 values(3,'test3');
INSERT 0 1
bill@bill=>insert into t1 select generate_series(4,10000),md5(random()::text);
INSERT 0 9997
–更新元数据，将索引恢复为VALID
bill@bill=>update pg_index set indisvalid=true where indexrelid='idx_t1'::regclass;
UPDATE 1
–查询新插入数据
可以看到新插入的数据也使用了索引扫描，说明即使在索引被设置为invalid时，当数据有更新、写入时依旧会更新这个索引。
bill@bill=>explain select * from t1 where info ='test2';
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_t1 on t1 (cost=0.29..2.90 rows=1 width=36)
Index Cond: (info = 'test2'::text)
(2 rows)
bill@bill=>select * from t1 where info = 'test2';
id | info
----+-------
2 | test2
(1 row)
