首页 » PostgreSQL/GaussDB » Postgresql 、openGauss系function函数Volatile 属性索引无法使用
Postgresql 、openGauss系function函数Volatile 属性索引无法使用
最近的一个从oracle到Openguass的项目迁移时，应用反馈SQL在使用列等值右侧function返回值无法使用索引的问题，当把function换成字面量时可以正常使用索引对象，这个问题对PostgreSQL开发者很重要，因为许多数据库开发人员不知道PostgreSQL函数的Volatility（不稳定性）。每个函数都有一个Volatility分类，可能是VOLATILE、STABLE或IMMUTABLE。VOLATILE是默认值。该问题同样适用于openguass系的package中的function.
关于Function Volatility Categories描述可以参考官方文档。
Volatile 函数在调用中，输入同样的参数可能会返回不同的结果，每一行都会重新计算该函数。
Stable 函数单个Query中所有行给定同样的参数确保返回相同的结果。在索引扫描的条件中使用这种函数是可行的，因为索引扫描只计算一次比较值(comparison value),而不是每行都计算一次。
Immutable 函数在任何情况下，只要输入参数相同，返回结果就相同。这种级别的函数，优化器可以提前进行计算，在查询过程中作为常量参数。
这个问题在Oracle中的函数同样存在限制，如创建函数索引时要求必须是稳定的，要求的function带关键字”Deterministic” , 有些国产库为了兼容oracle也增加了这个函数的稳定性参数，如KingbaseES, 等同于pg系的immutable， 而oracle中的immutable关键字在19c(19.11)和21C 一个新类型的Immutable Tables 新特性时使用， Immutable Tables 是一个防篡改、只插入的表，具有相关联的表级和行级保留期。它们类似于区块链表，但是没有使用加密散列将行链接起来。no drop和no delete2个选项可以指定时间决定了表多久可以被drop或delete .
下面测试一下该现象, — env postgresql 13.2
[local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f1(id integer) anbob-# returns bigint anbob-# language 'sql' anbob-# as anbob-# $$ anbob$# select id+1; anbob$# $$ ; CREATE FUNCTION [local]:5432 postgres@anbob=# [local]:5432 postgres@anbob=# select f1(2); f1 ---- 3 (1 row) [local]:5432 postgres@anbob=# \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id | integer | | not null | name | character varying(10) | | | Indexes: "cons_1234567890_1234567890_1234567890_1234567890_1234567890_123" PRIMARY KEY, btree (id) [local]:5432 postgres@anbob=# select * from t1; id | name ----+------ (0 rows) [local]:5432 postgres@anbob=# insert into t1 select x,'anbob' from generate_series(1,1000) as x; INSERT 0 1000 [local]:5432 postgres@anbob=# select * from t1; id | name ------+------- 1 | anbob 2 | anbob 3 | anbob 4 | anbob 5 | anbob 6 | anbob ... [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=11; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Index Scan using cons_1234567890_1234567890_1234567890_1234567890_1234567890_123 on t1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.014..0.016 rows=1 loops=1) Index Cond: (id = 11) Planning Time: 0.057 ms Execution Time: 0.030 ms (4 rows) [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f1(10); QUERY PLAN ------------------------------------------------------------------------------------------------------ Index Scan using cons_1234567890_1234567890_1234567890_1234567890_1234567890_123 on t1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.017..0.018 rows=1 loops=1) Index Cond: (id = '11'::bigint) Planning Time: 0.121 ms Execution Time: 0.038 ms (4 rows)
使用FUNCTION language SQL时，查询和字面常量一样，可以正常使用索引。
SQL VS PLPGSQL
[local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f2(id integer) anbob-# returns bigint anbob-# language 'sql' anbob-# as anbob-# $$ anbob$# select case when id>10 then 1 else 2 end; anbob$# $$ ; CREATE FUNCTION [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f2(10); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using cons_1234567890_1234567890_1234567890_1234567890_1234567890_123 on t1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (id = '2'::bigint) Planning Time: 0.100 ms Execution Time: 0.031 ms (4 rows) [local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f2(id integer) anbob-# returns bigint anbob-# language 'plpgsql' anbob-# as anbob-# $$ anbob$# begin anbob$# return case when id>10 then 1 else 2 end; anbob$# end; anbob$# $$ ; CREATE FUNCTION [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f2(10); QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=0.00..268.50 rows=1 width=10) (actual time=0.074..0.754 rows=1 loops=1) Filter: (id = f2(10)) Rows Removed by Filter: 999 Planning Time: 0.044 ms Execution Time: 0.768 ms (5 rows)
NOTE:
SQL VS PLPGSQL 不同， 函数中的逻辑一样，但是不同的language结果不同，language SQL优化器在执行时如果子查询返回给了主查询，可以正常使用索引。但是使用plpgsql时，没有使用索引而是全表扫seq scan on t1, 这就是本文开始时的应用当前面临的问题，当然应用中的逻辑比这里复杂，所以无法使用language SQL。那是否有方法可以使用索引还依赖Volatility属性。
Function Volatility
--DEFAULT VOLATILE [local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f_bigest(id1 integer, i d2 integer) anbob-# RETURNS bigint anbob-# AS anbob-# $$ anbob$# BEGIN anbob$# RETURN anbob$# CASE WHEN id1>=id2 THEN id1 ELSE id2 END; anbob$# END; anbob$# $$ LANGUAGE 'plpgsql'; CREATE FUNCTION [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f_bigest(10 , 11); QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=0.00..268.50 rows=1 width=10) (actual time=0.036..0.734 rows=1 loops=1) Filter: (id = f_bigest(10, 11)) Rows Removed by Filter: 999 Planning Time: 0.045 ms Execution Time: 0.749 ms (5 rows) [local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f_bigest_stable(id1 integer, id2 integer) anbob-# RETURNS bigint anbob-# stable anbob-# AS anbob-# $$ anbob$# BEGIN anbob$# RETURN anbob$# CASE WHEN id1>=id2 THEN id1 ELSE id2 END; anbob$# END; anbob$# $$ LANGUAGE 'plpgsql'; CREATE FUNCTION [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f_bigest_stable(10 , 11); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Index Scan using cons_1234567890_1234567890_1234567890_1234567890_1234567890_123 on t1 (cost=0.53..8.54 rows=1 width=10) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: (id = f_bigest_stable(10, 11)) Planning Time: 0.141 ms Execution Time: 0.027 ms (4 rows) [local]:5432 postgres@anbob=# CREATE OR REPLACE FUNCTION f_bigest_immutable(id1 integer, id2 integer) anbob-# RETURNS bigint anbob-# immutable anbob-# AS anbob-# $$ anbob$# BEGIN anbob$# RETURN anbob$# CASE WHEN id1>=id2 THEN id1 ELSE id2 END; anbob$# END; anbob$# $$ LANGUAGE 'plpgsql'; CREATE FUNCTION [local]:5432 postgres@anbob=# explain analyze select * from t1 where id=f_bigest_immutable(10 , 11); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Index Scan using cons_1234567890_1234567890_1234567890_1234567890_1234567890_123 on t1 (cost=0.28..8.29 rows=1 width=10) (actual time=0.008..0.009 rows=1 loops=1) Index Cond: (id = '11'::bigint) Planning Time: 0.119 ms Execution Time: 0.020 ms (4 rows)
NOTE:
可见除了VOLATILE(未指定，默认) function 无法使用索引外，stable和immutable的 函数都可以正常使用index， 但是在index 谓词上immutable使用的是函数转换后的常量值，而stable使用的是function .
属性修改
[local]:5432 postgres@anbob=# select proname,pronamespace,proowner,provolatile
anbob-# --propackage,propackageid,
anbob-# from pg_proc where proname like 'f_bigest%';
proname | pronamespace | proowner | provolatile
--------------------+--------------+----------+-------------
f_bigest | 2200 | 10 | v
f_bigest_stable | 2200 | 10 | s
f_bigest_immutable | 2200 | 10 | i
(3 rows)
[local]:5432 postgres@anbob=# alter function f_bigest stable;
ALTER FUNCTION
[local]:5432 postgres@anbob=# select proname,pronamespace,proowner,provolatile
--propackage,propackageid,
from pg_proc where proname like 'f_bigest%';
proname | pronamespace | proowner | provolatile
--------------------+--------------+----------+-------------
f_bigest | 2200 | 10 | s
f_bigest_stable | 2200 | 10 | s
f_bigest_immutable | 2200 | 10 | i
(3 rows)
Note:
function的Volatility除了重建时指定，也可以使用alter function修改，通过pg_proc.provolatile字典可以查询， 在opengauss中增加了package, package中的function创建时同样可以指定Volatility, 但是package中的function目前没有语法直接修改，而裸function一样可以修改，不过需要指定”（）”和参数变量确定唯一。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。