本文将介绍PostgreSQL 17服务端优化器相关的改进。

IS NOT NULL或者NOT NULL查询优化

针对查询限制条件IS NOT NULL和IS NULL，当表字段上有NOT NULL约束时，做了如下优化：

使用IS NOT NULL查询限制条件时，planner能感知检测NOT NULL约束，并删除冗余的IS NOT NULL限制条件
使用IS NULL查询限制条件时，planner能消除对NOT NULL字段的扫描，直接生成Result节点。

下面进行演示，先创建表结构：

CREATE TABLE t1(id int,name varchar(100));
INSERT INTO t1 values(1,'data1'),(2,'data2');

当查询条件列没有NOT NULL约束时，IS NOT NULL和IS NULL的执行计划与以前版本行为相同：

EXPLAIN (costs off) SELECT * FROM t1 WHERE name IS NULL;
        QUERY PLAN        
--------------------------
 Seq Scan on t1
   Filter: (name IS NULL)
(2 rows)

上面是IS NULL的执行计划，下面是IS NOT NULL的执行计划。

EXPLAIN (costs off) SELECT * FROM t1 WHERE name IS NOT NULL;
          QUERY PLAN          
------------------------------
 Seq Scan on t1
   Filter: (name IS NOT NULL)
(2 rows)

给字段添加NOT NULL约束

ALTER TABLE t1 ALTER COLUMN name SET NOT NULL;

当条件列设置非空约束后，17版本的执行计划发生变化：

EXPLAIN (costs off) SELECT * FROM t1 WHERE name IS NULL;
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

从上面的执行计划可以看出无需对查询列进行扫描，直接生成Result节点。

EXPLAIN (costs off) SELECT * FROM t1 WHERE name IS NOT NULL;
   QUERY PLAN   
----------------
 Seq Scan on t1
(1 row)

从上面的执行计划可以看出冗余的IS NOT NULL条件被消除。

有网友使用benchmark5.0对16和17进行性能对比测试，100仓下bmsql_oorder表对o_c_id字段设置not null约束后，IS NOT NULL相比16提升19.89%，IS NULL相比16提升99.54%。

GROUP BY排序优化

新版本里优化器允许根据索引或者Order By列对Group By列进行重排，并通过新增的enable_group_by_reordering参数进行控制，默认值为on。

postgres=# show enable_group_by_reordering;
 enable_group_by_reordering 
----------------------------
 on
(1 row)

测试准备如下：

SELECT
	i as id,
	i/2 as p,
format('%60s', i%2) as v
INTO tab
FROM
	generate_series(1, 1_000_000) i;

CREATE INDEX ON tab(p, v);

VACUUM tab;
ANALYZE tab;

对比下面两个语句的执行计划

postgres=# EXPLAIN (costs off)
SELECT count(*) FROM tab group by v, p;
                   QUERY PLAN                   
------------------------------------------------
 GroupAggregate
   Group Key: p, v
   ->  Index Only Scan using tab_p_v_idx on tab
(3 rows)

postgres=# EXPLAIN (costs off)
SELECT count(*) FROM tab GROUP BY v, p ORDER BY p, v;
                   QUERY PLAN                   
------------------------------------------------
 GroupAggregate
   Group Key: p, v
   ->  Index Only Scan using tab_p_v_idx on tab
(3 rows)

虽然Group By列的顺序与索引的顺序或者ORDER BY条件的顺序不一致，但17版本里优化器能根据索引或者Order By进行重排。

CTE优化

物化条件下的CTE添加了相关列统计能让优化器预估行更准确。
物化条件下的CTE能利用相关列排序避免外层排序。

两个典型场景如下：

with x as MATERIALIZED (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);

with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);

LIMIT优化

LIMIT对分区表、继承表、UNION ALL等场景做了优化，参考场景语句如下：

SELECT .. FROM v1
UNION ALL
SELECT .. FROM v2
LIMIT 3;

SELECT * FROM partitioned .. LIMIT 3;

SELECT * FROM partitioned JOIN q using (partkey) LIMIT 3;

SELECT * FROM foo
WHERE EXISTS
(SELECT 1 FROM bar WHERE xxx);

还有一些其他的优化器改进，比如IN相关子查询转化为Join、分区裁剪优化、范围值查询优化等。

与我联系

微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。

ITPub博客

PostgreSQL 17优化器相关改进

IS NOT NULL或者NOT NULL查询优化

GROUP BY排序优化

CTE优化

LIMIT优化

推荐阅读

与我联系

最新文章