PostgreSQL17.0中IS NOT NULL和IS NULL查询限制优化功能测试
PostgreSQL17.0中IS NOT NULL和IS NULL查询限制优化功能测试
上个月PostgreSQL17.0 BETA版本已经发布，在优化器上新增了许多优化功能。其中PostgreSQL17.0可以在有NOT NULL约束的列上删除冗余的IS NOT NULL语句，并且不再需要在包含IS NOT NULL子句的列上执行查询；如果指定IS NULL，则取消对非空列的扫描。对于这个功能还是比较感兴趣，因此为了直观的感受，新增该功能后执行计划的变化。下面将通过对PostgreSQL16.3和PostgreSQL17.0两个版本进行对比测试。
一、IS NOT NULL和IS NULL查询限制执行计划对比
1、创建测试使用的数据库及表并插入测试验证数据
create database testdb;
create table t1(id int,name varchar(100),address varchar(100));
insert into t1 values(1,'wang1','beijing');
insert into t1 values(2,'wang2','beijing');
insert into t1 values(3,'wang3','beijing');
insert into t1 values(4,'wang4','beijing');
2、当条件列没有非空约束时，对比两个版本的执行计划是一样
--PostgreSQL16.3
testdb=# explain select * from t1 where name is not null;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..11.70 rows=169 width=440)
Filter: (name IS NOT NULL)
(2 rows)
--PostgreSQL17.0
testdb=# explain select * from t1 where name is not null;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..11.70 rows=169 width=440)
Filter: (name IS NOT NULL)
(2 rows)
3、在两个版本数据库分别设置非空约束
testdb=# alter table t1 alter column name set not null;
ALTER TABLE
4、当条件列设置非空约束后，观察两个版本的执行计划
PostgreSQL16.3中的执行计划
--is not null过滤条件
testdb=# explain select * from t1 where name is not null;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..11.70 rows=169 width=440)
Filter: (name IS NOT NULL)
(2 rows)
--is null过滤条件
testdb=# explain select * from t1 where name is null;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..11.70 rows=1 width=440)
Filter: (name IS NULL)
(2 rows)
PostgreSQL17.0Beta 1中的执行计划
--is not null过滤条件
testdb=# explain select * from t1 where name is not null;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..11.70 rows=170 width=440)
(1 row)
--is null过滤条件
testdb=# explain select * from t1 where name is null;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
通过上述测试执行计划的对比，当条件列设置为空约束后，在执行的SQL中有该条件的IS NOT NULL，在PostgreSQL17.0生成的执行计划中删除了Filter: (name IS NOT NULL)即优化器删除了多余IS NOT NULL条件，避免一些不必要的查询，提升了查询的性能；另外当查询条件指定条件为IS NULL，在PostgreSQL17.0生成的执行计划中会显示一行，One-Time Filter: false的信息，猜测应该是取消对非空列的扫描的描述。
5、当条件列设置非空约束后，使用逻辑操作符AND和OR，观察执行计划的变化
IS NOT NULL
PostgreSQL16.3中的执行计划
--逻辑操作符AND
testdb=# explain select * from t1 where name is not null and id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=1 width=440)
Filter: ((name IS NOT NULL) AND (id = 2))
(2 rows)
--逻辑操作符OR
testdb=# explain select * from t1 where name is not null or id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=4 width=440)
Filter: ((name IS NOT NULL) OR (id = 2))
(2 rows)
PostgreSQL17.0Beta 1中的执行计划
--逻辑操作符AND
testdb=# explain select * from t1 where name is not null and id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=1 width=440)
Filter: (id = 2)
(2 rows)
--逻辑操作符OR
testdb=# explain select * from t1 where name is not null or id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.04 rows=4 width=440)
(1 row)
通过上述执行计划的对比发现，在执行的SQL中指定条件列约束IS NOT NULL时，即使使用了逻辑操作符，也在PostgreSQL17.0生成的执行计划中删除了Filter: (name IS NOT NULL)。
IS NULL
PostgreSQL16.3中的执行计划
--逻辑操作符AND
testdb=# explain select * from t1 where name is null and id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=1 width=440)
Filter: ((name IS NULL) AND (id = 2))
(2 rows)
--逻辑操作符OR
testdb=# explain select * from t1 where (name is null or id = 2);
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=1 width=440)
Filter: ((name IS NULL) OR (id = 2))
(2 rows)
PostgreSQL17.0Beta 1中的执行计划
--逻辑操作符AND
testdb=# explain select * from t1 where name is null and id = 2;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
--逻辑操作符OR
testdb=# explain select * from t1 where name is null or id = 2;
QUERY PLAN
----------------------------------------------------
Seq Scan on t1 (cost=0.00..1.05 rows=1 width=440)
Filter: ((name IS NULL) OR (id = 2))
(2 rows)
通过上述执行计划的对比发现，在执行的SQL中指定条件列约束IS NULL时，当使用逻辑操作符AND时，在PostgreSQL17.0生成的执行计划中会显示一行，显示One-Time Filter: false的信息，取消对非空列的扫描；当使用逻辑操作符OR时,PostgreSQL16.3和PostgreSQL17.0执行计划显示一致，在这里name is null即使不需要扫描列，但是另外一个条件id = 2是需要扫描符合其结果的数据，因此也在PostgreSQL17.0执行计划未改变也是符合预期结果的。
二、IS NOT NULL和IS NULL查询限制优化性能测试
性能测试使用benchmark5.0压测场景的bmsql_oorder表，数据300w（100仓数据）
1、当条件列没有非空约束时，对比查询耗时
--PostgreSQL16.3
testdb=# explain analyze select * from bmsql_oorder where o_c_id is not null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on bmsql_oorder (cost=0.00..54126.00 rows=3000000 width=36) (actual time=0.010..232.055 rows=3000000 loops=1)
Filter: (o_c_id IS NOT NULL)
Planning Time: 0.077 ms
Execution Time: 302.339 ms
(4 rows)
Time: 302.705 ms
testdb=# explain analyze select * from bmsql_oorder where o_c_id is null;
QUERY PLAN
-----------------------------------------------------------------------------------
-----------------------------------------
Gather (cost=1000.00..37626.10 rows=1 width=36) (actual time=66.837..67.281 rows=
0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on bmsql_oorder (cost=0.00..36626.00 rows=1 width=36) (ac
tual time=64.477..64.477 rows=0 loops=3)
Filter: (o_c_id IS NULL)
Rows Removed by Filter: 1000000
Planning Time: 0.069 ms
Execution Time: 67.310 ms
(8 rows)
Time: 67.775 ms
--PostgreSQL17.0
testdb=# explain analyze select * from bmsql_oorder where o_c_id is not null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on bmsql_oorder (cost=0.00..54127.00 rows=3000000 width=36) (actual time=0.015..218.919 rows=3000000 loops=1)
Filter: (o_c_id IS NOT NULL)
Planning Time: 0.086 ms
Execution Time: 289.086 ms
(4 rows)
Time: 289.488 ms
testdb=# explain analyze select * from bmsql_oorder where o_c_id is null;
QUERY PLAN
-----------------------------------------------------------------------------------
-----------------------------------------
Gather (cost=1000.00..37627.10 rows=1 width=36) (actual time=63.920..64.751 rows=
0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on bmsql_oorder (cost=0.00..36627.00 rows=1 width=36) (ac
tual time=57.914..57.914 rows=0 loops=3)
Filter: (o_c_id IS NULL)
Rows Removed by Filter: 1000000
Planning Time: 0.083 ms
Execution Time: 64.771 ms
(8 rows)
Time: 65.295 ms
2、在两个版本数据库分别设置非空约束
alter table bmsql_oorder alter column o_c_id set not null;
3、当条件列设置非空约束后，
--PostgreSQL16.3
testdb=# explain analyze select * from bmsql_oorder where o_c_id is not null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on bmsql_oorder (cost=0.00..54126.00 rows=3000000 width=36) (actual time=0.013..235.300 rows=3000000 loops=1)
Filter: (o_c_id IS NOT NULL)
Planning Time: 0.250 ms
Execution Time: 305.230 ms
(4 rows)
Time: 306.442 ms
testdb=# explain analyze select * from bmsql_oorder where o_c_id is null;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..37626.10 rows=1 width=36) (actual time=64.702..65.208 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on bmsql_oorder (cost=0.00..36626.00 rows=1 width=36) (actual time=61.943..61.943 rows=0 loops=3)
Filter: (o_c_id IS NULL)
Rows Removed by Filter: 1000000
Planning Time: 0.084 ms
Execution Time: 65.224 ms
(8 rows)
Time: 65.570 ms
--PostgreSQL17.0
testdb=# explain analyze select * from bmsql_oorder where o_c_id is not null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on bmsql_oorder (cost=0.00..54127.00 rows=3000000 width=36) (actual time=0.007..160.696 rows=3000000 loops=1)
Planning Time: 0.095 ms
Execution Time: 231.332 ms
(3 rows)
Time: 231.908 ms
testdb=# explain analyze select * from bmsql_oorder where o_c_id is null;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.003..0.003 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.047 ms
Execution Time: 0.014 ms
(4 rows)
Time: 0.299 ms
| 场景\版本 | PostgreSQL16.3 | PostgreSQL17.0 | 17.0性能提升 |
|---|---|---|---|
| 未设置非空时is not null | 302.705 ms | 289.488 ms | |
| 未设置非空时is null | 67.775 ms | 65.295 ms | |
| 设置非空时is not null | 306.442 ms | 231.908 ms | 19.89% |
| 设置非空时is null | 65.570 ms | 0.299 ms | 99.54% |
总结
在通过上面对IS NOT NULL和IS NULL查询限制优化功能的执行计划对比和性能测试后，一方面能够很明显感受到PostgreSQL17.0版本优化后，查询性能提升还是非常可观的；另外一方面，我们通过对比IS NOT NULL和IS NULL查询限制场景的执行计划，也了解了两个版本执行计划的差异，同时也更深入的了解了性能提升的根本原因。
