知识点滴 -- 函数稳定态影响分区表分区剪枝

KingbaseES 函数有三种稳定态，具体可以看 https://www.modb.pro/db/624181

函数稳定态影响SQL 索引使用、分区剪枝。本文通过展示稳定态对于分区剪枝功能的影响。

一、构建测试例子

create table test_part1(id integer,part_date date,name text)
partition by range(part_date)
(
  partition part2023 values less than (to_date('20240101000000','YYYY-MM-DD HH24:MI:SS')),
  partition part2024 values less than (to_date('20250101000000','YYYY-MM-DD HH24:MI:SS')),
  partition part2025 values less than (to_date('20260101000000','YYYY-MM-DD HH24:MI:SS')),
  partition part2026 values less than (to_date('20270101000000','YYYY-MM-DD HH24:MI:SS'))
);
insert into test_part1 select generate_series(1,2000000),now() + random()*1000 ,md5(random());

这里构建了一个以时间为分区列的表。以下例子通过传入不同时间函数参数，验证分区剪枝情况。

二、函数稳定态与分区剪枝

先来看需要用到的两个函数的稳定态：

test=# select proname,provolatile from sys_proc where proname in ('now','clock_timestamp');
     proname     | provolatile
-----------------+-------------
 now             | s
 clock_timestamp | v

1、stable 函数可以使用分区剪枝

test=# explain select * from test_part1 where part_date=now();
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Append  (cost=0.00..50835.02 rows=4 width=45)
   Subplans Removed: 3
   ->  Seq Scan on test_part1_part2023  (cost=0.00..7091.88 rows=1 width=45)
         Filter: ((part_date)::timestamp without time zone = now())
(4 行记录)

可以看到 “Subplans Removed: 3”，也就是访问的分区扣除了3个。

2、volatile 函数无法使用分区剪枝功能

test=# explain select * from test_part1 where part_date=clock_timestamp();
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Append  (cost=0.00..50835.02 rows=4 width=45)
   ->  Seq Scan on test_part1_part2023  (cost=0.00..7091.88 rows=1 width=45)
         Filter: ((part_date)::timestamp without time zone = clock_timestamp())
   ->  Seq Scan on test_part1_part2024  (cost=0.00..18612.02 rows=1 width=45)
         Filter: ((part_date)::timestamp without time zone = clock_timestamp())
   ->  Seq Scan on test_part1_part2025  (cost=0.00..18575.51 rows=1 width=45)
         Filter: ((part_date)::timestamp without time zone = clock_timestamp())
   ->  Seq Scan on test_part1_part2026  (cost=0.00..6555.59 rows=1 width=45)
         Filter: ((part_date)::timestamp without time zone = clock_timestamp())
(9 行记录)

可以看到，SQL 访问了所有数据分区。