PostgreSQL每日一贴-函数三态学习

函数三态：volatile, stable, immutable

VOLATILE
volatile函数没有限制, 可以修改数据(如执行delete, insert , update).
使用同样的参数调用可能返回不同的值.
volatile函数不能被优化器选择作为优化条件.(例如减少调用, 函数索引, 索引扫描不允许使用volatile函数)
在同一个查询中, 同样参数的情况下可能被多次执行(QUERY有多行返回/扫描的情况下).
snapshot为函数内的每个query开始时的snapshot. 因此对在函数执行过程中, 外部已提交的数据可见.(仅仅限于调用函数的事务隔离级别为read committed)
STABLE
stable和immutable函数, 函数内不允许修改数据.(如PGver>=8.0 函数内不可执行非SELECT|PERFORM语句.)
使用同样的参数调用返回同样的结果, 在事务中有这个特性的也归属stable.
优化器可根据实际场景优化stable函数的调用次数, 同样的参数多次调用可减少成单次调用.
stable和immutable函数可用于优化器选择合适的索引扫描, 因为索引扫描仅评估被比较的表达式一次, 后多次与索引值进行比较.
stable和volatile函数都不能用于创建函数索引, 只有immutable函数可以用于创建函数索引.
stable和immutable函数, snapshot为外部调用函数的QUERY的snapshot, 函数内部始终保持这个snapshot, 外部会话带来的的数据变更不被反映到函数执行过程中.
IMMUTABLE
不允许修改数据, 使用同样的参数调用返回同样的结果.
优化器在处理immutable函数时, 先评估函数结果, 将结果替换为常量.
因此使用约束优化查询的场景中也只识别immutable函数.


STABLE和IMMUTABLE的区别
stable函数在select和where子句中不被优化, 仅仅当使用索引扫描时where子句对应的stable函数才会被优化为1次调用.
在prepared statement中的使用区别:
immutable函数在plan时以常量替代, stable函数在execute阶段被执行.
因此immutable函数参数为常量时, 在prepared statement场景只执行一次, 而stable函数被多次执行.
函数稳定性通过查看pg_proc.provolatile得到

性能测试

CREATE OR REPLACE FUNCTION fun_volatile(source varchar) RETURNS varchar AS $$DECLAREBEGIN    return upper(source);END;$$ LANGUAGE plpgsql volatile;CREATE OR REPLACE FUNCTION fun_immutable(source varchar) RETURNS varchar AS $$DECLAREBEGIN    return upper(source);END;$$ LANGUAGE plpgsql immutable;drop table if exists test_table1;create table test_table1 (id varchar(50));insert into test_table1 select generate_series(1, 1000);drop table if exists test_table2;create table test_table2 (id varchar(50));insert into test_table2 select generate_series(1, 100);select count(1) from test_table1 a where exists (select 'x' from test_table2 b where a.id = b.id);select count(1) from test_table1 a where exists (select 'x' from test_table2 b where fun_volatile(a.id) = fun_volatile(b.id));select count(1) from test_table1 a where exists (select 'x' from test_table2 b where fun_immutable(a.id) = fun_immutable(b.id));

测试结果

postgres=> select count(1) from test_table1 a where exists (select 'x' from test_table2 b where a.id = b.id);
 count 
-------
   100
(1 row)


Time: 2.752 ms
postgres=> select count(1) from test_table1 a where exists (select 'x' from test_table2 b where fun_volatile(a.id) = fun_volatile(b.id));
 count 
-------
   100
(1 row)


Time: 765.342 ms
postgres=> select count(1) from test_table1 a where exists (select 'x' from test_table2 b where fun_immutable(a.id) = fun_immutable(b.id));
 count 
-------
   100
(1 row)


Time: 17.253 ms