知识点滴 -- 函数稳定态影响索引使用

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());
create index idx_1 on test_part1(part_date);

这里构建了一个以时间的索引。以下例子通过传入不同时间函数参数，验证是否能够使用索引。

二、函数稳定态与索引使用

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

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

1、stable 函数可以用到索引，可以分区剪枝

test=# explain select * from test_part1 where part_date=now();
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.42..33.79 rows=4 width=45)
   Subplans Removed: 3
   ->  Index Scan using test_part1_part2023_part_date_idx on test_part1_part2023  (cost=0.42..8.44 rows=1 width=45)
         Index Cond: ((part_date)::timestamp without time zone = now())
(4 行记录)

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 行记录)

时间：0.882 ms