开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 改元信息 实现 invalid index

简介:
+关注继续查看

标签

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 数据库初体验
  10月15日,由山东华鲁科技咨询顾问高强老师在“DBA+济南群”进行了一次关于PostgreSQL数据库初体验的线上主题分享。小编特别整理出其中精华内容,供大家学习交流。   嘉宾简介    高强,“DBA+济南群”联合发起人。
2515 0
MySQL与PostgreSQL比较 哪个数据库更好
最后结论说的好,通常由团队成员的熟悉度来决定;  PostgreSQL 的名字很少听到,最近试装发现不是很友好;官方文档写的对新手来说有点坑; 有数据库工作经验的直接看最后一句就可以。 如果打算为项目选择一款免费、开源的数据库,那么你可能会在MySQL与PostgreSQL之间犹豫不定。
1462 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
PolarDB for PostgreSQL三节点功能介绍
立即下载
认识PostgreSQL中与众不同的索引
立即下载