PostgreSQL17.0中IS NOT NULL和IS NULL查询限制优化功能测试

基础技术研究 2024-07-18

简介

2024 SUMMER

今年5月PostgreSQL17.0 BETA版本已经发布，在优化器上新增了许多优化功能。其中PostgreSQL17.0可以在有NOT NULL约束的列上删除冗余的IS NOT NULL语句，并且不再需要在包含IS NOT NULL子句的列上执行查询；如果指定IS NULL，则取消对非空列的扫描。对于这个功能还是比较感兴趣，因此为了直观地感受新增该功能后执行计划的变化，下面将对PostgreSQL16.3和PostgreSQL17.0两个版本进行对比测试。

查询限制执行计划对比

1）创建测试使用的数据库及表并插入测试验证数据。

1.create database testdb;  
2.  
3.create table t1(id int,name varchar(100),address varchar(100));  
4.insert into t1 values(1,'wang1','beijing');  
5.insert into t1 values(2,'wang2','beijing');  
6.insert into t1 values(3,'wang3','beijing');  
7.insert into t1 values(4,'wang4','beijing');

2）当条件列没有非空约束时，对比两个版本的执行计划是一样。

1.--PostgreSQL16.3  
2.testdb=# explain select * from t1 where name is not null;  
3.                      QUERY PLAN                         
4.-------------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..11.70 rows=169 width=440)  
6.   Filter: (name IS NOT NULL)  
7.(2 rows)  
8.--PostgreSQL17.0  
9.testdb=# explain select * from t1 where name is not null;  
10.                      QUERY PLAN                         
11.-------------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..11.70 rows=169 width=440)  
13.   Filter: (name IS NOT NULL)  
14.(2 rows)

3）在两个版本数据库分别设置非空约束。

1.testdb=# alter table t1 alter column name set not null;  
2.ALTER TABLE

4）当条件列设置非空约束后，观察两个版本的执行计划。

PostgreSQL16.3中的执行计划：

1.--is not null过滤条件  
2.testdb=# explain select * from t1 where name is not null;  
3.                      QUERY PLAN                         
4.-------------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..11.70 rows=169 width=440)  
6.   Filter: (name IS NOT NULL)  
7.(2 rows)  
8.--is null过滤条件  
9.testdb=# explain select * from t1 where name is null;  
10.                     QUERY PLAN                        
11.-----------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..11.70 rows=1 width=440)  
13.   Filter: (name IS NULL)  
14.(2 rows)

PostgreSQL17.0Beta 1中的执行计划：

1.--is not null过滤条件  
2.testdb=# explain select * from t1 where name is not null;  
3.                      QUERY PLAN                         
4.-------------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..11.70 rows=170 width=440)  
6.(1 row)  
7.--is null过滤条件  
8.testdb=# explain select * from t1 where name is null;  
9.                QUERY PLAN                  
10.------------------------------------------  
11. Result  (cost=0.00..0.00 rows=0 width=0)  
12.   One-Time Filter: false  
13.(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中的执行计划：

1.--逻辑操作符AND  
2.testdb=# explain select * from t1 where name is not null and id = 2;  
3.                     QUERY PLAN                       
4.----------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..1.05 rows=1 width=440)  
6.   Filter: ((name IS NOT NULL) AND (id = 2))  
7.(2 rows)  
8.--逻辑操作符OR  
9.testdb=# explain select * from t1 where name is not null or id = 2;  
10.                     QUERY PLAN                       
11.----------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..1.05 rows=4 width=440)  
13.   Filter: ((name IS NOT NULL) OR (id = 2))  
14.(2 rows)

PostgreSQL17.0Beta 1中的执行计划：

1.--逻辑操作符AND  
2.testdb=# explain select * from t1 where name is not null and id = 2;  
3.                     QUERY PLAN                       
4.----------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..1.05 rows=1 width=440)  
6.   Filter: (id = 2)  
7.(2 rows)  
8.--逻辑操作符OR  
9.testdb=# explain select * from t1 where name is not null or id = 2;  
10.                     QUERY PLAN                       
11.----------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..1.04 rows=4 width=440)  
13.(1 row)

通过上述执行计划的对比发现，在执行的SQL中指定条件列约束IS NOT NULL时，即使使用了逻辑操作符，也在PostgreSQL17.0生成的执行计划中删除了Filter: (name IS NOT NULL)。

IS NULL

PostgreSQL16.3中的执行计划：

1.--逻辑操作符AND  
2.testdb=# explain select * from t1 where name is null and id = 2;  
3.                     QUERY PLAN                       
4.----------------------------------------------------  
5. Seq Scan on t1  (cost=0.00..1.05 rows=1 width=440)  
6.   Filter: ((name IS NULL) AND (id = 2))  
7.(2 rows)  
8.--逻辑操作符OR  
9.testdb=# explain select * from t1 where (name is null or id = 2);  
10.                     QUERY PLAN                       
11.----------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..1.05 rows=1 width=440)  
13.   Filter: ((name IS NULL) OR (id = 2))  
14.(2 rows)

PostgreSQL17.0Beta 1中的执行计划：

1.--逻辑操作符AND  
2.testdb=# explain select * from t1 where name is null and id = 2;  
3.                QUERY PLAN                  
4.------------------------------------------  
5. Result  (cost=0.00..0.00 rows=0 width=0)  
6.   One-Time Filter: false  
7.(2 rows)  
8.--逻辑操作符OR  
9.testdb=# explain select * from t1 where name is null or id = 2;  
10.                     QUERY PLAN                       
11.----------------------------------------------------  
12. Seq Scan on t1  (cost=0.00..1.05 rows=1 width=440)  
13.   Filter: ((name IS NULL) OR (id = 2))  
14.(2 rows)

通过上述执行计划的对比发现，在执行的SQL中指定条件列约束IS NULL时，当使用逻辑操作符AND时，在PostgreSQL17.0生成的执行计划中会显示一行，显示One-Time Filter: false的信息，取消对非空列的扫描；当使用逻辑操作符OR时,PostgreSQL16.3和PostgreSQL17.0执行计划显示一致，在这里name is null即使不需要扫描列，但是另外一个条件id = 2是需要扫描符合其结果的数据，因此在PostgreSQL17.0执行计划未改变也是符合预期结果的。

查询限制优化性能测试

性能测试使用benchmark5.0压测场景的bmsql_oorder表，数据300w（100仓数据）。

1）当条件列没有非空约束时，对比查询耗时。

1.--PostgreSQL16.3  
2.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is not null;  
3.                                                       QUERY PLAN                                                          
4.-------------------------------------------------------------------------------------------------------------------------  
5. Seq Scan on bmsql_oorder  (cost=0.00..54126.00 rows=3000000 width=36) (actual time=0.010..232.055 rows=3000000 loops=1)  
6.   Filter: (o_c_id IS NOT NULL)  
7. Planning Time: 0.077 ms  
8. Execution Time: 302.339 ms  
9.(4 rows)  
10.  
11.Time: 302.705 ms  
12.  
13.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is null;  
14.                                                         QUERY PLAN                  
15.                                           
16.-----------------------------------------------------------------------------------  
17.-----------------------------------------  
18. Gather  (cost=1000.00..37626.10 rows=1 width=36) (actual time=66.837..67.281 rows=  
19.0 loops=1)  
20.   Workers Planned: 2  
21.   Workers Launched: 2  
22.   ->  Parallel Seq Scan on bmsql_oorder  (cost=0.00..36626.00 rows=1 width=36) (ac  
23.tual time=64.477..64.477 rows=0 loops=3)  
24.         Filter: (o_c_id IS NULL)  
25.         Rows Removed by Filter: 1000000  
26. Planning Time: 0.069 ms  
27. Execution Time: 67.310 ms  
28.(8 rows)  
29.Time: 67.775 ms  
30.  
31.--PostgreSQL17.0  
32.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is not null;  
33.                                                       QUERY PLAN                                                          
34.-------------------------------------------------------------------------------------------------------------------------  
35. Seq Scan on bmsql_oorder  (cost=0.00..54127.00 rows=3000000 width=36) (actual time=0.015..218.919 rows=3000000 loops=1)  
36.   Filter: (o_c_id IS NOT NULL)  
37. Planning Time: 0.086 ms  
38. Execution Time: 289.086 ms  
39.(4 rows)  
40.  
41.Time: 289.488 ms  
42.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is null;  
43.                                                         QUERY PLAN                  
44.                                           
45.-----------------------------------------------------------------------------------  
46.-----------------------------------------  
47. Gather  (cost=1000.00..37627.10 rows=1 width=36) (actual time=63.920..64.751 rows=  
48.0 loops=1)  
49.   Workers Planned: 2  
50.   Workers Launched: 2  
51.   ->  Parallel Seq Scan on bmsql_oorder  (cost=0.00..36627.00 rows=1 width=36) (ac  
52.tual time=57.914..57.914 rows=0 loops=3)  
53.         Filter: (o_c_id IS NULL)  
54.         Rows Removed by Filter: 1000000  
55. Planning Time: 0.083 ms  
56. Execution Time: 64.771 ms  
57.(8 rows)  
58.  
59.Time: 65.295 ms

2) 在两个版本数据库分别设置非空约束。

1.alter table bmsql_oorder alter column o_c_id set not null;

3) 当条件列设置非空约束后。

1.--PostgreSQL16.3  
2.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is not null;  
3.                                                       QUERY PLAN                                                          
4.-------------------------------------------------------------------------------------------------------------------------  
5. Seq Scan on bmsql_oorder  (cost=0.00..54126.00 rows=3000000 width=36) (actual time=0.013..235.300 rows=3000000 loops=1)  
6.   Filter: (o_c_id IS NOT NULL)  
7. Planning Time: 0.250 ms  
8. Execution Time: 305.230 ms  
9.(4 rows)  
10.  
11.Time: 306.442 ms  
12.  
13.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is null;  
14.                                                         QUERY PLAN                                                           
15.----------------------------------------------------------------------------------------------------------------------------  
16. Gather  (cost=1000.00..37626.10 rows=1 width=36) (actual time=64.702..65.208 rows=0 loops=1)  
17.   Workers Planned: 2  
18.   Workers Launched: 2  
19.   ->  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)  
20.         Filter: (o_c_id IS NULL)  
21.         Rows Removed by Filter: 1000000  
22. Planning Time: 0.084 ms  
23. Execution Time: 65.224 ms  
24.(8 rows)  
25.  
26.Time: 65.570 ms  
27.  
28.  
29.--PostgreSQL17.0  
30.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is not null;  
31.                                                       QUERY PLAN                                                          
32.-------------------------------------------------------------------------------------------------------------------------  
33. Seq Scan on bmsql_oorder  (cost=0.00..54127.00 rows=3000000 width=36) (actual time=0.007..160.696 rows=3000000 loops=1)  
34. Planning Time: 0.095 ms  
35. Execution Time: 231.332 ms  
36.(3 rows)  
37.  
38.Time: 231.908 ms  
39.  
40.testdb=# explain analyze  select * from bmsql_oorder where o_c_id is null;  
41.                                     QUERY PLAN                                       
42.------------------------------------------------------------------------------------  
43. Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.003..0.003 rows=0 loops=1)  
44.   One-Time Filter: false  
45. Planning Time: 0.047 ms  
46. Execution Time: 0.014 ms  
47.(4 rows)  
48.  
49.Time: 0.299 ms

4) 汇总性能测试结果。

总结

在通过上面对IS NOT NULL和IS NULL查询限制优化功能的执行计划对比和性能测试后，一方面能够很明显感受到PostgreSQL17.0版本优化后，查询性能提升还是非常可观的；另外一方面，我们通过对比IS NOT NULL和IS NULL查询限制场景的执行计划，也了解了两个版本执行计划的差异，同时也更深入地了解了性能提升的根本原因。

文章转载自基础技术研究，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL17.0中IS NOT NULL和IS NULL查询限制优化功能测试

评论