企业门户网站实现,网站建设有模板吗,网站开发 商城开发,东莞英文网站建设PostgreSQL17优化器改进#xff08;3#xff09;在使用包含操作符和时优化范围查询
本文将介绍PostgreSQL 17服务端优化器在使用包含操作符和时优化范围查询。其实在在第一眼看到官网网站的对于该优化点的时候#xff0c;可能是由于缺乏对于范围类型的认知…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 (cost0.42..4.80 rows19 width4) (actual time0.470..0.475 rows19 loops1)Index Cond: ((num -10::integer) AND (num 8))Heap Fetches: 0Planning Time: 0.191 msExecution 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 (cost0.00..3385.01 rows1000 width4) (actual time17.626..36.529 rows18 loops1)Filter: (num [-10,8)::int4range)Rows Removed by Filter: 199983Planning Time: 0.113 msExecution 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 (cost0.42..4.84 rows21 width4) (actual time0.281..0.286 rows19 loops1)Index Cond: ((num -10::integer) AND (num 8))Heap Fetches: 0Planning Time: 0.192 msExecution 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 (cost0.42..4.82 rows20 width4) (actual time0.034..0.039 rows18 loops1)Index Cond: ((num -10::integer) AND (num 8))Heap Fetches: 0Planning Time: 0.207 msExecution 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.07470108
(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
--------------------------------------------------------------------------------------------------------------------------------------------ResultOutput: ((now() 2024-06-10 00:00:0008::timestamp with time zone) AND (now() 2024-06-20 00:00:0008::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
---------------------------------------------------------------------------------------------------ResultOutput: (clock_timestamp() [2024-06-10 00:00:0008,2024-06-20 00:00:0008)::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
-------------------------------------------------------------------------------------ResultOutput: (clock_timestamp() 2024-01-20 00:00:0008::timestamp with time zone)
(2 rows)Time: 0.714 ms总结
在PostgreSQL 17中为范围操作符和添加优化器支持函数这些支持函数将优化具有恒定范围值的表达式转换为对范围边界值的直接比较其实就是启用了过滤条件上的索引。但是有些情况也是不支持的如果对可变的或代价高的元素表达式进行双重求值则跳过该转换。
