PostgreSQL17优化器改进（3）在使用包含操作符＜@和@＞时优化范围查询

墨竹~

已于 2024-06-12 08:38:28 修改

阅读量725

点赞数 15

分类专栏： PostgreSQL 文章标签：性能优化 postgresql

于 2024-06-12 08:36:07 首次发布

本文链接：https://blog.csdn.net/m0_61738548/article/details/139613292

版权

PostgreSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

PostgreSQL17优化器改进（3）在使用包含操作符<@和@>时优化范围查询

本文将介绍PostgreSQL 17服务端优化器在使用包含操作符<@和@>时优化范围查询。其实在在第一眼看到官网网站的对于该优化点的时候，可能是由于缺乏对于范围类型的认知，因此也不太清楚具体优化的场景，但是通过详细的阅读官网沟通邮件及官网文档，也基本搞明白了PostgreSQL17 的优化场景。下面是整理的功能测试用例及不支持的场景的测试用例。

创建测试用例需要的表

CREATE TABLE rang_integer (num integer);
insert into rang_integer  select generate_series(-100000, 100000);
CREATE index idx_rang_integer ON rang_integer( num );
ANALYZE rang_integer;

PostgreSQL16.3中的执行计划

在PostgreSQL16.3中，测试用例将对比BETWEEN and语句和范围操作符@>、@< 的执行计划和执行用时。

testdb=> EXPLAIN ANALYZE SELECT num FROM rang_integer WHERE num BETWEEN -10 AND 8;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_rang_integer on rang_integer  (cost=0.42..4.80 rows=19 width=4) (actual time=0.470..0.475 rows=19 loops=1)
   Index Cond: ((num >= '-10'::integer) AND (num <= 8))
   Heap Fetches: 0
 Planning Time: 0.191 ms
 Execution Time: 0.502 ms
(5 rows)

Time: 1.721 ms
testdb=> 
testdb=> 
testdb=> EXPLAIN ANALYZE SELECT num FROM rang_integer WHERE num <@ int4range(-10, 8);
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on rang_integer  (cost=0.00..3385.01 rows=1000 width=4) (actual time=17.626..36.529 rows=18 loops=1)
   Filter: (num <@ '[-10,8)'::int4range)
   Rows Removed by Filter: 199983
 Planning Time: 0.113 ms
 Execution Time: 36.552 ms
(5 rows)

Time: 37.285 ms

在PostgreSQL16.3版本中检查一个元素是否包含在一个范围中时，使用@>或<@操作符的性能比BETWEEN AND的查询性能都差且差距比较大；通过执行计划我们也可以看出，在使用范围操作符时，扫描方式使用的是Seq Scan on rang_integer ，未使用到范围过滤条件上的索引。

PostgreSQL17.0Beta 1中的执行计划

在PostgreSQL17中，同样测试用例将对比BETWEEN and语句和范围操作符@>、@< 的执行计划和执行用时。

testdb=> EXPLAIN ANALYZE SELECT num FROM rang_integer WHERE num BETWEEN -10 AND 8;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_rang_integer on rang_integer  (cost=0.42..4.84 rows=21 width=4) (actual time=0.281..0.286 rows=19 loops=1)
   Index Cond: ((num >= '-10'::integer) AND (num <= 8))
   Heap Fetches: 0
 Planning Time: 0.192 ms
 Execution Time: 0.315 ms
(5 rows)

Time: 1.205 ms
testdb=> 
testdb=> ^C
testdb=> EXPLAIN ANALYZE SELECT num FROM rang_integer WHERE num <@ int4range(-10, 8);
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_rang_integer on rang_integer  (cost=0.42..4.82 rows=20 width=4) (actual time=0.034..0.039 rows=18 loops=1)
   Index Cond: ((num >= '-10'::integer) AND (num < 8))
   Heap Fetches: 0
 Planning Time: 0.207 ms
 Execution Time: 0.061 ms
(5 rows)

Time: 0.794 ms

在PostgreSQL17版本中，优化范围值的查询后，通过对比 PostgreSQL16.3和PostgreSQL17的范围操作符查询，执行耗时由原来的37.285ms降低到0.794 ms，执行SQL的耗时大概提升了40倍多。我们再查看PostgreSQL17版本中的执行计划，发现在使用范围操作符时过滤数据时，已经用到之前新建的索引idx_rang_integer，因此查询才得到了提升。

PostgreSQL17.0Beta 1中不支持的场景

检查可变用例是否优化

这些用例是官网文档中提供的，通过下面的测试可知，对于值可变的情况，未进行优化

testdb=> select now();
              now              
-------------------------------
 2024-06-11 17:06:07.074701+08
(1 row)

Time: 0.331 ms
testdb=> explain (verbose, costs off)
testdb-> select now() <@ tstzrange('2024-06-10 00:00', '2024-06-20 00:00');
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Result
   Output: ((now() >= '2024-06-10 00:00:00+08'::timestamp with time zone) AND (now() < '2024-06-20 00:00:00+08'::timestamp with time zone))
(2 rows)

Time: 0.610 ms
testdb=> explain (verbose, costs off)  -- unsafe!
testdb-> select clock_timestamp() <@ tstzrange('2024-06-10 00:00', '2024-06-20 00:00');
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Result
   Output: (clock_timestamp() <@ '["2024-06-10 00:00:00+08","2024-06-20 00:00:00+08")'::tstzrange)
(2 rows)

Time: 0.532 ms
testdb=> explain (verbose, costs off)
testdb-> select clock_timestamp() <@ tstzrange('2024-01-20 00:00', NULL);
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result
   Output: (clock_timestamp() >= '2024-01-20 00:00:00+08'::timestamp with time zone)
(2 rows)

Time: 0.714 ms