Statement Level PLSQL Function Cache Support

Availability

This feature is available since MogDB 5.0.9.

Introduction

This feature supports caching the results of stable/immutable type functions, increase query performance by more than 10 times in scenarios with low base.

Description

When executing a query, if the statement contains a PLSQL function, the execution of the function will take up a lot of time. If the function is stable/immutable and the function parameters are repeated, caching the result can greatly improve the query efficiency.

This feature supports that in the process of SQL execution, for the first time encountered parameters of stable/immutable type function, the result will be put into cache after calculation, and then if the same parameters are encountered, the result can be taken out from cache directly, which can save the calculation process and improve the query performance.

Parameters

• Add USERSET type GUC parameter enable_cache_function_result to control whether caching is enabled or not, default value is off.

  Value range: on/off

  Default value: off

• Add USERSET type GUC parameter function_result_cache_max_mem, control the maximum memory size in kilobytes that can be used by the cache.

  Value range: 1024 ~ INT_MAX

  Default value: 16*1024

Constraints

1. Cacheable function condition:
   • Functions need to be of type stable/immutable.
   • Arguments and results of the function cannot contain the type set.
   • Scenarios with out parameter in plsql are not supported.
   • Supports all function types except for buildin, including c/sql/java/plsql functions etc.
2. To determine whether the results in the cache can be reused, the following conditions need to be met:
   • The function oid is the same.
   • The function needs to have the same number of arguments as well as the same type.
3. For a query of the type select foo(), the results will not be cached as they will only be used once.
4. If a query contains both cacheable and non-cacheable functions, the cacheable function is still cached.
5. Function cache results are only valid at the query level.
6. In a parallel scenario, each worker has its own cache, which is not shared.

Example

CREATE TABLE t_plsql_cache(a int, b varchar);
INSERT INTO t_plsql_cache VALUES (generate_series(1, 10000)/3, (generate_series(1, 10000)/3)::int::varchar);
CREATE OR REPLACE FUNCTION is_even(a int)
RETURNS int
LANGUAGE 'plpgsql'
IMMUTABLE
AS $$
BEGIN
    RETURN a%2 = 0;
END;
$$;
 
MogDB=# explain analyze select sum(is_even(a)) from t_plsql_cache;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2677.00..2677.01 rows=1 width=12) (actual time=873.801..873.801 rows=1 loops=1)
   ->  Seq Scan on t_plsql_cache  (cost=0.00..152.00 rows=10000 width=4) (actual time=0.023..4.404 rows=10000 loops=1)
 Total runtime: 873.917 ms
(3 rows)
 
MogDB=# set enable_cache_function_result= true;
SET
MogDB=#  explain analyze select sum(is_even(a)) from t_plsql_cache;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2677.00..2677.01 rows=1 width=12) (actual time=291.390..291.390 rows=1 loops=1)
   ->  Seq Scan on t_plsql_cache  (cost=0.00..152.00 rows=10000 width=4) (actual time=0.019..2.455 rows=10000 loops=1)
 Total runtime: 291.550 ms
(3 rows)

Related Pages

enable_cache_function_result, function_result_cache_max_mem