immutable 与 stable 函数的差异

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

以下以例子说明二者的差异。

一、KingbaseES

1、准备数据

?
1
2
3
4
5
6
7
8
9
10
11
12
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、创建immutable 和 stable 函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace function f001()
    returns bigint
    immutable
    language sql
as
$$ select count(*) from t1 $$ ;
 
 
create or replace function f002()
    returns bigint
    stable
    language sql
as
$$ select count(*) from t1 $$ ;

3、单独explain 函数

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
test=# explain select f001();
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=8)
(1 row)
 
Time: 450.572 ms
test=# explain select f002();
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=8)
(1 row)
 
Time: 0.641 ms
 
test=# select f001();
   f001
----------
 10000000
(1 row)
 
Time: 448.720 ms
test=# select f002();
   f002
----------
 10000000
(1 row)
 
Time: 426.745 ms

　　

4、例子一

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

复制代码
test=# explain analyze select * from (select * from t1 limit 10) a where a.id1=f001();
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..0.27 rows=1 width=8) (actual time=0.012..0.013 rows=0 loops=1)
   Filter: (a.id1 = '10000000'::bigint)
   Rows Removed by Filter: 10
   ->  Limit  (cost=0.00..0.15 rows=10 width=8) (actual time=0.009..0.010 rows=10 loops=1)
         ->  Seq Scan on t1  (cost=0.00..148609.21 rows=10007621 width=8) (actual time=0.008..0.009 rows=10 loops=1)
 Planning Time: 413.963 ms
 Execution Time: 0.026 ms
(7 rows)

test=# explain analyze select * from (select * from t1 limit 10) a where a.id1=f002();
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..2.77 rows=1 width=8) (actual time=3691.788..3691.788 rows=0 loops=1)
   Filter: (a.id1 = f002())
   Rows Removed by Filter: 10
   ->  Limit  (cost=0.00..0.15 rows=10 width=8) (actual time=0.012..0.028 rows=10 loops=1)
         ->  Seq Scan on t1  (cost=0.00..148609.21 rows=10007621 width=8) (actual time=0.011..0.021 rows=10 loops=1)
 Planning Time: 364.233 ms
 Execution Time: 3691.807 ms
(7 rows)

Time: 4056.907 ms (00:04.057)

test=# explain analyze select * from (select * from t1 where 1=2) a where a.id1=f002();
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Result  (cost=0.00..2675533.51 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
   One-Time Filter: false
   ->  Seq Scan on t1  (cost=0.00..2675533.51 rows=1 width=8) (never executed)
         Filter: (id1 = f002())
 Planning Time: 490.720 ms
 Execution Time: 0.017 ms
(6 rows)

test=# explain analyze select * from (select * from t1 limit 1) a where a.id1=f002();
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=0.00..0.28 rows=1 width=8) (actual time=390.833..390.834 rows=0 loops=1)
   Filter: (a.id1 = f002())
   Rows Removed by Filter: 1
   ->  Limit  (cost=0.00..0.01 rows=1 width=8) (actual time=0.024..0.053 rows=1 loops=1)
         ->  Seq Scan on t1  (cost=0.00..148609.21 rows=10007621 width=8) (actual time=0.023..0.023 rows=1 loops=1)
 Planning Time: 426.693 ms
 Execution Time: 390.852 ms
(7 rows)
复制代码

 

5、例子二

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 函数结果都相同。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
test=# alter function sysdate immutable;
ALTER FUNCTION
Time: 6.276 ms<br>
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)<br>
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、创建函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

?
1
2
3
4
5
6
7
8
9
10
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 都必须要调用一次，如果没有记录，则无需调用。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

　　

posted @   KINGBASE研究院  阅读(146)  评论(0)  编辑  收藏  举报
相关博文：
· KingbaseES函数三态
· KingbaseES 函数稳定性与子查询提升
· KingbaseES函数三态
· 函数式编程中的 Immutable 数据结构
· 第二十五节：再探IQueryable和IEnumerable、EFCore调用SQL语句、增删改翻译后的SQL剖析(是否批处理)
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示