首页 » PostgreSQL/GaussDB » Postgresql 、openGauss系function函数Volatile 属性索引无法使用

Postgresql 、openGauss系function函数Volatile 属性索引无法使用

  • 2022/10/14
  • PostgreSQL/GaussDB
  • 100 views
  • 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一样可以修改，不过需要指定”（）”和参数变量确定唯一。

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：