PostgreSQL长事务与失效的索引查询浅析介绍
（福利推荐：你还在原价购买阿里云服务器？现在阿里云0.8折限时抢购活动来啦！4核8G企业云服务器仅2998元/3年，立即抢购>>>：9i0i.cn/aliyun）
最近刚写了一篇文章介绍了下长事务，以及一些长事务常见的危害，如无法及时的垃圾回收导致表膨胀之类的问题，最近刚好又碰到一个问题也是长事务所导致的。
上周六早上接到同事电话，说某个库CPU一直很高，看了下全是某张大表的全表扫描导致，但是奇怪的是相关的查询都有用到索引列，不知道为啥查询全部都没走索引。
当我连上去查看时发现确实如此，如果只是某个查询不走索引那可能是SQL本身写的有问题，但是这张表相关的所有SQL都不走索引，那自然会想到是索引本身的原因了。那是不是索引失效了呢？经过检查发现这张表上的索引状态均正常，并且我还将索引重建了，可仍然没起作用。
正当我迷茫的时候，偶然间再去执行相关SQL的时候发现竟然又都走索引了，这又是啥情况。。
我啥都没做你就自己恢复了，那不是显得我很呆？不行，必须得搞清楚啥原因。
当我再去查看相关索引的时候发现，该索引的pg_index中的indcheckxmin列均为true，这个字段我之前有写过一篇索引失效的文章里介绍过。那么什么情况下索引的该属性会被设置为true呢？两种情况：
- 当前事务中表上存在broken HOT chains；
- 当old_snapshot_threshold被设置时。
之前我们也介绍过，如果索引的该属性为true那么在创建索引的事务中该索引是不可用的，不过这种场景我们基本不太会遇到，因为在实际应用中我们基本不会在事务中创建完索引然后不提交该事务直接去使用。
而关于indcheckxmin的详细解释是：直到此pg_index行的xmin低于查询的TransactionXmin之前，查询都不能使用此索引。那么什么情况下会出现这种问题呢？长事务！
当我们创建索引的时候如果索引的indcheckxmin被设置为true，且数据库中此时存在长事务，那么直到该长事务提交前，该索引会一直不可用。
下面我们来模拟这种情况：
--会话一：打开一个长事务
bill=# begin; BEGIN bill=*# delete from t; DELETE 1000 bill=*#
--会话二：创建索引
由于old_snapshot_threshold参数被设置，所以创建的索引indcheckxmin被设置为true了。
bill=# show old_snapshot_threshold ; old_snapshot_threshold ------------------------ 1h (1 row) bill=# create index idx_t1 on t1(id); CREATE INDEX bill=# select indisvalid,indcheckxmin,indisready,indislive from pg_index where indexrelid = 'idx_t1'::regclass; indisvalid | indcheckxmin | indisready | indislive ------------+--------------+------------+----------- t | t | t | t (1 row)
使用该索引列进行查询：
索引的确无法使用。
bill=# explain analyze select * from t1 where id = 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..209.00 rows=51 width=37) (actual time=0.010..0.692 rows=51 loops=1)
Filter: (id = 100)
Rows Removed by Filter: 9949
Planning Time: 0.150 ms
Execution Time: 0.706 ms
(5 rows)
bill=# set enable_seqscan = off;
SET
bill=# explain analyze select * from t1 where id = 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=10000000000.00..10000000209.00 rows=51 width=37) (actual time=0.063..0.732 rows=51 loops=1)
Filter: (id = 100)
Rows Removed by Filter: 9949
Planning Time: 0.089 ms
Execution Time: 0.796 ms
(5 rows)
提交该长事务后再次查询：
索引变得可用了。
bill=# explain analyze select * from t1 where id = 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Scan using idx_t1 on t1 (cost=0.29..54.48 rows=51 width=37) (actual time=0.013..0.052 rows=51 loops=1)
Index Cond: (id = 100)
Planning Time: 0.061 ms
Execution Time: 0.067 ms
(4 rows)
果然是长事务的坑啊！
由于我们的库中基本都打开了old_snapshot_threshold参数，导致创建的索引的indcheckxmin一定是true。但这其实并不会有什么太大影响，问题在于在创建索引的同时数据库中存在长事务，这就导致了索引在创建完之后第一时间变得不可用了，需要直到该长事务被提交后才可用。
后来和同事求证发现他们之前也经常碰到这种CPU变高然后又自己降下来的情况，之前并没有注意是这张表的全表扫描导致的。由于该表是由pg_pathman创建的自动分区，每天都会自己去创建一个新的分区，因此如果每天自动创建分区的时候存在长事务，那么创建完之后相关的分区上的索引均是不可用的，这也是为什么查询不走索引然后又自己恢复的原因了。
这个案例其实我们在实际生产中遇到的可能性还是很大的，如果你的数据库打开了old_snapshot_threshold参数，同时没有做好长事务的监控，那么创建的索引就会出现这种不可用的情况。长事务危害不浅啊！
到此这篇关于PostgreSQL长事务与失效的索引查询浅析介绍的文章就介绍到这了,更多相关PostgreSQL长事务内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！
相关文章
PostgreSQL提供了一些帮助提升性能的功能。主要有一些几个方面。2009-09-09
这篇文章主要介绍了PostgreSQL教程(十四)：数据库维护,本文讲解了恢复磁盘空间、更新规划器统计、VACUUM和ANALYZE的示例、定期重建索引等内容,需要的朋友可以参考下2015-05-05
这篇文章主要介绍了PostgreSQL利用递归优化求稀疏列唯一值的方法,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2021-01-01
这篇文章主要介绍了PostgreSQL完成按月累加的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-01-01
这篇文章主要介绍了基于postgresql行级锁for update测试，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2020-12-12
这篇文章主要给大家介绍了关于PostgreSQL配置远程连接的相关资料,PostgreSQL是一个功能非常强大的关系型数据库管理系统(RDBMS),文中通过代码介绍的非常详细,需要的朋友可以参考下2023-12-12
这篇文章主要介绍了postgresql运维之远程迁移操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-01-01
这篇文章主要介绍了postgresql如何查询重复计数及去重查询问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教2023-11-11
在PostgreSQL中使用ltree处理层次结构数据的方法
这篇文章主要介绍了在PostgreSQL中使用ltree处理层次结构数据,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2021-03-03
这篇文章主要介绍了PostgreSQL 并行计算算法，参数，强制并行度设置,本文通过示例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2022-04-04
最新评论