测试函数稳定性参数IMMUTABLE,STABLE,VOLATILE对优化器的影响

测试函数稳定性参数IMMUTABLE,STABLE,VOLATILE对优化器的影响

IMMUTABLE,STABLE,VOLATILE
这些属性告知查询优化器该函数的行为。最多只能指定其中一个。如果这些都不出现，则会默认为VOLATILE。

• IMMUTABLE表示该函数不能修改数据库并且对于给定的参数值总是会返回相同的值。也就是说，它不会做数据库查找或者使用没有在其参数列表中直接出现的信息。如果给定合格选项，任何用全常量参数对该函数的额调用可以立刻用该函数值替换
• STABLE表示该函数不能修改数据库，并且对于相同的参数值，它在一次表扫描中将返回相同的结果。但是这种结果在不同的 SQL 语句执行期间可能会变化。对于那些结果依赖于数据库查找、参数变量（例如当前时区）等的函数来说，这是合适的（对希望查询被当前命令修改的行的AFTER触发器不适合）。还要注意current_timestamp函数族适合被标记为稳定，因为它们的值在一个事务内不会改
• VOLATILE表示该函数的值在一次表扫描中都有可能改变，因此不能做优化。在这种意义上，相对较少的数据库函数是不稳定的，一些例子是random()、currval()、timeofday()。但是注意任何有副作用的函数都必须被分类为不稳定的，即便其结果是可以预测的，这是为了调用被优化掉。一个例子是setval()。

测试表
omm=# \d t
Table “public.t”
Column | Type | Modifiers
--------±-----------------------±----------
id | integer |
c | character varying(100) |
Indexes:
“idx_t_id” btree (id) TABLESPACE pg_default

immutable

create or replace  function public.f1 (c varchar)
returns int
immutable
as $$
begin
return 1;
end;
$$ language plpgsql;


explain  analyze select * from t where id=f1('1');
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_t_id on t  (cost=0.00..8.29 rows=2 width=12) (actual time=0.010..0.010 rows=2 loops=1)
   Index Cond: (id = 1)  <<<<<
 Total runtime: 0.052 ms
(4 rows)
复制

stable

create or replace  function public.f1 (c varchar)
returns int
stable
as $$
begin
return 1;
end;
$$ language plpgsql;

explain  analyze select * from t where id=f1('1');
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_id on t  (cost=0.25..8.54 rows=2 width=12) (actual time=0.018..0.018 rows=2 loops=1)
   Index Cond: (id = f1('1'::character varying)) <<<<<
 Total runtime: 0.050 ms
(3 rows)
复制

volatile

create or replace  function public.f1 (c varchar)
returns int
volatile
as $$
begin
return 1;
end;
$$ language plpgsql;

explain  analyze select * from t where id=f1('1');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..2681.05 rows=1 width=12) (actual time=0.161..27.058 rows=2 loops=1)
   Filter: (id = f1('1'::character varying)) <<<<<
   Rows Removed by Filter: 10002
 Total runtime: 27.098 ms
(4 rows)
复制