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

最新推荐文章于 2025-06-05 21:15:00 发布

三余_1986

最新推荐文章于 2025-06-05 21:15:00 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库 PostgreSQL

本文链接：https://blog.csdn.net/enzesheng/article/details/43116785

PostgreSQL 同时被 2 个专栏收录

17 篇文章

订阅专栏

数据库

16 篇文章

订阅专栏

本文通过查询示例对比了在PostgreSQL中使用volatile、immutable函数对查询性能的影响。测试显示，使用volatile函数导致查询耗时765.342 ms，immutable函数则仅需17.253 ms，揭示了函数稳定性对数据库查询效率的显著差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

函数三态：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 $$
DECLARE
BEGIN
    return upper(source);
END;
$$ LANGUAGE plpgsql volatile;

CREATE OR REPLACE FUNCTION fun_immutable(source varchar) RETURNS varchar AS $$
DECLARE
BEGIN
    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