PostgreSQL17索引优化之允许btree索引更高效地查找一组值
上个月就已经在查看PostgreSQL17版本中索引优化相关的优化点，对于在版本发布中说明的允许btree索引更高效地查找一组值，例如使用常量的IN子句来提供这组值。其实对于官网版本中的这个描述，最初的时候我自己还是没有很清晰的认知。但是通过翻看官网的代码并进行实际操作练习后，我觉得通俗的理解就是在in子句中使用常量查询的时候，可以用到该字段上建的组合索引。下面我们通过具体案例来测试一下，看看哪些场景应用到该功能优化，另外也看看具体执行计划有啥变化。
创建测试用例表
--bmsql_oorder为benchmark5.0压测场景的表，数据都为300w（100仓数据压测的结果） create table bmsql_oorder_test as select * from bmsql_oorder; ANALYZE bmsql_oorder_test; --创建索引 create index idx_bmsql_oorder_test_1 on bmsql_oorder_test (o_d_id); create index idx_bmsql_oorder_test_2 on bmsql_oorder_test (o_d_id,o_c_id); create index idx_bmsql_oorder_test_3 on bmsql_oorder_test (o_id);复制
过滤条件只有使用常量的in子句，有过滤字段的索引
PostgreSQL16.3的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_d_id in (30,45,66); QUERY PLAN ----------------------------------------------------------------- Index Scan using idx_bmsql_oorder_test_1 on bmsql_oorder_test t Index Cond: (o_d_id = ANY ('{30,45,66}'::integer[])) (2 rows)复制
PostgreSQL17.0Beta 1的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_d_id in (30,45,66); QUERY PLAN ----------------------------------------------------------------- Index Scan using idx_bmsql_oorder_test_1 on bmsql_oorder_test t Index Cond: (o_d_id = ANY ('{30,45,66}'::integer[])) (2 rows)复制
通过PostgreSQL16.3和PostgreSQL17.0Beta 1对比可知，在使用常量的in子句中，是可以使用到过滤字段上建的索引。
过滤条件只有使用常量的in子句，索引为含有该字段的组合索引，无单独索引
PostgreSQL16.3的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_c_id in (11,43,101); QUERY PLAN ----------------------------------------------------------- Gather Workers Planned: 2 -> Parallel Seq Scan on bmsql_oorder_test t Filter: (o_c_id = ANY ('{11,43,101}'::integer[])) (4 rows) Time: 0.731 ms复制
PostgreSQL17.0Beta 1的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_c_id in (11,43,101); QUERY PLAN --------------------------------------------------------------- Bitmap Heap Scan on bmsql_oorder_test t Recheck Cond: (o_c_id = ANY ('{11,43,101}'::integer[])) -> Bitmap Index Scan on idx_bmsql_oorder_test_2 Index Cond: (o_c_id = ANY ('{11,43,101}'::integer[])) (4 rows) Time: 50.328 ms复制
通过PostgreSQL16.3和PostgreSQL17.0Beta 1对比可知，如果当过滤条件为当前索引的非前导列，在PostgreSQL17.0版本中也可以用到该索引。
过滤条件有多个时
PostgreSQL16.3的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_d_id <2 and t.o_c_id in (11,43,101); QUERY PLAN ---------------------------------------------------------------------------- Index Scan using idx_bmsql_oorder_test_2 on bmsql_oorder_test t Index Cond: ((o_d_id < 2) AND (o_c_id = ANY ('{11,43,101}'::integer[]))) (2 rows) Time: 1.755 ms复制
PostgreSQL17.0Beta 1的执行计划
testdb=# explain (costs off) select * from bmsql_oorder_test t where t.o_d_id <2 and t.o_c_id in (11,43,101); QUERY PLAN ----------------------------------------------------------------------------------- Bitmap Heap Scan on bmsql_oorder_test t Recheck Cond: ((o_d_id < 10) AND (o_c_id = ANY ('{11,43,101}'::integer[]))) -> Bitmap Index Scan on idx_bmsql_oorder_test_2 Index Cond: ((o_d_id < 10) AND (o_c_id = ANY ('{11,43,101}'::integer[]))) (4 rows) Time: 1.801 ms复制
通过PostgreSQL16.3和PostgreSQL17.0Beta 1对比可知，在PostgreSQL16.3中扫描方式为Index Scan，但是在PostgreSQL17变为了Bitmap Index Scan。
总结
通过上面的测试，我们大概可以有这几个点的收获：
1、在in子句中使用常量查询的时候，当在该过滤条件上存在单列索引时，其实该功能在之前的版本应该是支持的。
2、如果当in子句中使用常量作为过滤条件且为索引的非前导列，在PostgreSQL17.0版本中查询也可以用到该组合索引
3、过滤条件有多个时，in子句中使用常量作为过滤条件且为索引的非前导列，我可以看到扫描方式由Index Scan变为Bitmap Index Scan。
这些就是我通过查看官方文档，然后自己整理测试的一些场景，但是至少让我们知道，“哦，允许btree索引更有效地查找一组值，原来是在in子句中使用常量查询的时候，可以用到该字段上建的组合索引”，我觉得就是我写这边文章的收获吧。
– / END / –
可以通过下面的方式联系我
如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！
