immutable 与 stable 函数的差异
- 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、创建immutable 和 stable 函数
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 时则不会实际执行。
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 函数在同一事务内多次执行返回的结果都相同,跨事务则每次结果不同。
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
KINGBASE研究院