知识点滴 -- Immutable 与 Stable 函数态差异比较

原创金仓数据库 2023-04-18

1706

概述

Stable 函数不能修改数据库，单个Query中所有行给定同样的参数确保返回相同的结果。这种稳定级别允许优化器将特定场景下多次函数调用转换为一次。在索引扫描的条件中使用这种函数是可行的，因为索引扫描只计算一次比较值(comparison value),而不是每行都计算一次。
Immutable 函数不能修改数据库，在任何情况下，只要输入参数相同，返回结果就相同。这种级别的函数，优化器可以提前进行计算，在查询过程中作为常量参数。比如：SELECT...WHERE x=2+2 可以简化为SELECT...WHERE x=4。

那二者有什么区别了？以下以例子说明二者的差异。

一、KingbaseES 场景

1、准备数据

create table t1(id1 integer,id2 integer);
insert into t1 select generate_series(1,10000000),generate_series(1,10000000);

test=# \timing on
Timing is on.
test=# select count(*) from t1;
  count
----------
 10000000
(1 row)

Time: 681.445 ms

2、创建三种稳定态函数

create or replace function test_immutable()
    returns bigint
    immutable
    language sql
as
$$ select count(*) from t1 $$ ;

create or replace function test_volatile()
    returns bigint
    volatile
    language sql
as
$$ select count(*) from t1 $$ ;

create or replace function test_stable()
    returns bigint
    stable
    language sql
as
$$ select count(*) from t1 $$ ;

3、单独explain 函数

可以看到对于 immutable 函数，在 explain 时，实际会去执行的；而stable 函数，explain 时则不会实际执行。

test=# explain select test_immutable();
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=8)

时间：679.436 ms

test=# explain select test_stable();
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=8)

时间：1.011 ms

test=# select test_immutable();
 test_immutable
----------------
       10000000

时间：670.702 ms

test=# select test_stable();
 test_stable
-------------
    10000000

时间：694.783 ms

4、函数放在等式右边（作为参数）与执行次数

可以看到 immutable 函数执行时间主要花在planning上，也就是在制定执行计划前，就已经取得函数的值；而 stable 函数，则在语句解析和执行时，都要执行函数，而且，针对语句的访问的每个tuple，都要执行一次函数调用。

test=# explain analyze select * from (select * from t1 limit 10) a where a.id1=test_immutable();
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..0.27 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=1)
   Filter: (a.id1 = '10000000'::bigint)
   Rows Removed by Filter: 10
   ->  Limit  (cost=0.00..0.14 rows=10 width=8) (actual time=0.015..0.016 rows=10 loops=1)
         ->  Seq Scan on t1  (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.014..0.015 rows=10 loops=1)
 Planning Time: 662.422 ms
 Execution Time: 0.088 ms
(7 行记录)

时间：663.628 ms

test=# explain analyze select * from (select * from t1 limit 10) a where a.id1=test_stable();
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..2.77 rows=1 width=8) (actual time=6569.996..6569.997 rows=0 loops=1)
   Filter: (a.id1 = test_stable())
   Rows Removed by Filter: 10
   ->  Limit  (cost=0.00..0.14 rows=10 width=8) (actual time=0.011..0.022 rows=10 loops=1)
         ->  Seq Scan on t1  (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.010..0.015 rows=10 loops=1)
 Planning Time: 680.484 ms
 Execution Time: 6570.059 ms
(7 行记录)

时间：7251.037 ms (00:07.251)

test=# explain analyze select * from (select * from t1 limit 10) a where a.id1=test_volatile();
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..2.77 rows=1 width=8) (actual time=6587.698..6587.699 rows=0 loops=1)
   Filter: (a.id1 = test_volatile())
   Rows Removed by Filter: 10
   ->  Limit  (cost=0.00..0.14 rows=10 width=8) (actual time=0.021..0.032 rows=10 loops=1)
         ->  Seq Scan on t1  (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.020..0.025 rows=10 loops=1)
 Planning Time: 0.306 ms
 Execution Time: 6587.839 ms
(7 行记录)

时间：6589.390 ms (00:06.589)

可以看到：test_immutable 只在语句解析时执行，test_stable 在解析与执行阶段都要执行，test_volatile 只在执行阶段执行。

5、跨事务与函数执行次数

sysdate 函数为 stable 时：sysdate 函数在同一事务内多次执行返回的结果都相同，跨事务则每次结果不同。

test=# begin
test-#   for i in 1..5 loop
test-#     raise notice '%', sysdate();
test-#     perform sys_sleep(1);
test-#     commit;
test-#   end loop;
test-# end;
test-# /
NOTICE:  2022-06-29 19:53:18
NOTICE:  2022-06-29 19:53:19
NOTICE:  2022-06-29 19:53:20
NOTICE:  2022-06-29 19:53:21
NOTICE:  2022-06-29 19:53:22
ANONYMOUS BLOCK
Time: 5011.031 ms (00:05.011)

test=# begin
test-#   for i in 1..5 loop
test-#     raise notice '%', sysdate();
test-#     perform sys_sleep(1);
test-#   end loop;
test-# end;
test-# /
NOTICE:  2022-06-29 19:54:14
NOTICE:  2022-06-29 19:54:14
NOTICE:  2022-06-29 19:54:14
NOTICE:  2022-06-29 19:54:14
NOTICE:  2022-06-29 19:54:14
ANONYMOUS BLOCK
Time: 5005.724 ms (00:05.006)

sysdate 函数为 immutable 时：不管是否跨事务，sysdate 函数结果都相同。

test=# alter function sysdate immutable;
ALTER FUNCTION
Time: 6.276 ms

test=# begin
test-#   for i in 1..5 loop
test-#     raise notice '%', sysdate();
test-#     perform sys_sleep(1);
test-#     commit;
test-#   end loop;
test-# end;
test-# /
NOTICE:  2022-06-29 19:54:58
NOTICE:  2022-06-29 19:54:58
NOTICE:  2022-06-29 19:54:58
NOTICE:  2022-06-29 19:54:58
NOTICE:  2022-06-29 19:54:58
ANONYMOUS BLOCK
Time: 5007.899 ms (00:05.008)

test=# begin
test-#   for i in 1..5 loop
test-#     raise notice '%', sysdate();
test-#     perform sys_sleep(1);
test-#   end loop;
test-# end;
test-# /
NOTICE:  2022-06-29 19:55:11
NOTICE:  2022-06-29 19:55:11
NOTICE:  2022-06-29 19:55:11
NOTICE:  2022-06-29 19:55:11
NOTICE:  2022-06-29 19:55:11
ANONYMOUS BLOCK
Time: 5007.694 ms (00:05.008)

二、Oracle 场景

1、创建函数

create or replace function f001
    return integer
    deterministic
as
  cnt integer;
begin
  for i in 1..10 loop
    select count(*) into cnt from t1 ;
  end loop;
  return cnt;
end ;


create or replace function f002
    return integer
as
  cnt integer;
begin
  for i in 1..10 loop
    select count(*) into cnt from t1 ;
  end loop;
  return cnt;
end ;

2、单独explain 函数

可以看到两个函数都不会执行，不管是 deterministic，还是 volatile

SQL> explain plan for select f001() from dual;

Explained.

Elapsed: 00:00:00.00
SQL> explain plan for select f002() from dual;

Explained.

Elapsed: 00:00:00.00

3、实际执行

deterministic 只需执行一次，但是在SQL执行时才执行函数调用，而非explain时；volatile 解析时，不需要调用函数，而针对每个tuple 都必须要调用一次，如果没有记录，则无需调用。

SQL> select * from (select * from t1 where rownum<11) where id1=f001();

no rows selected

Elapsed: 00:00:00.48

SQL> select * from (select * from t1 where rownum<11) where id1=f002();

no rows selected

Elapsed: 00:00:05.01

SQL> select * from (select * from t1 where 1=2) where id1=f002();

no rows selected

Elapsed: 00:00:00.00

人大金仓