<返回更多

原来 SQL 函数是可以内联的!

2024-04-03  微信公众号  红石PG
加入收藏

介绍

在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器可以进行常量折叠、条件下推等优化。

但是,适用于内联的确切条件有些复杂,并且在源代码以外也没有很好的文档记录。本文试图部分补充这一点。

实际上,存在两种完全独立的内联形式,对于任何给定的函数调用,可能出现的两种形式有:一种对应标量函数调用,另一种对应表函数调用。

标量函数

标量函数调用,是出现在值表达式或谓词上下文中的func(args)的任何实例,即在普通值或条件可以出现的任何位置。例如:

select func(t.foo) from sometable t;
select t.* from sometable t where func(t.foo, 123);

在上面的第二种情况下,如果func()的定义将可用于索引的运算符应用于第一个参数,则内联对于性能至关重要,因为它允许规划器使用索引。此方法被 PostGIS 广泛使用,比如ST_ContAIns和ST_DWithin等函数。

表函数

表函数调用,是在预期出现表的位置的任何func(args)实例。(对于大多数函数,这是 PostgreSQL 对于 SQL 标准的扩展。)例如:

内联表函数调用最重要的一个性能优势是,能够将其他条件下推到函数中:

select * from func(123) as f where f.foo = 456;

在此示例中,在内联func(123)之后,规划器可以将f.foo条件向下移动到函数体中,从而可能允许在函数引用的表上将其用作索引条件。

标量函数的内联条件

如果满足以下所有条件,则标量函数调用会被内联:

• 函数指定了LANGUAGE SQL

• 函数没有指定为SECURITY DEFINER

• 函数不是RETURNS SETOF (或 RETURNS TABLE)

• 函数不是RETURNS RECORD

• 函数的定义中没有SET子句

• 该函数尚未内联;一个递归函数只会将其最外层的调用以内联形式展开

• 没有插件模块在函数调用的入口/出口封装钩子

• 函数体由单个简单的SELECT expression组成

• 函数体定义中不涉及聚合或窗口函数调用,不包含子查询,不包含 CTE 表达式,不包含对任何表或类似表的对象的FROM子句或引用,不包含GROUP BY、HAVING、ORDER BY、DISTINCT、LIMIT、OFFSET、UNION、INTERSECT、EXCEPT

• 函数体查询必须只返回一列

• 函数体表达式的类型必须与函数声明的返回类型匹配

• 表达式不得返回多行(例如,通过调用集合返回函数,如unnest()或generate_series())

• 如果函数被声明为IMMUTABLE,则表达式不得调用任何非不可变的函数或运算符

• 如果函数被声明为STABLE,则表达式不得调用任何易失性的函数或运算符

• 如果函数被声明为STRICT,那么规划器必须能够确定:如果任何参数为 null,函数体表达式必然返回NULL。目前,只有在以下情况下才满足此条件:每个参数至少被引用一次,并且函数体中使用的所有函数、运算符和其他结构本身都是STRICT的。

• 如果传递给函数的一个实际参数是一个易失性表达式,则不得在函数体中多次引用它

• 如果一个实际参数是一个“成本高”的表达式,定义为成本超过 10 次运算符的成本,或者包含了任何子查询,则不得在函数体中多次引用它

表函数的内联条件

如果满足以下所有条件,则表函数调用会被内联:

• 函数调用未指定ORDINALITY,或者是在ROWS FROM中的多个函数

• 任何实际参数都不包含易失性表达式或子查询

• 没有插件模块在函数调用的入口/出口封装钩子

• 函数指定了LANGUAGE SQL

• 函数没有指定为SECURITY DEFINER

• 函数被声明为STABLE或IMMUTABLE

• 函数没有被声明为STRICT

• 函数被声明为RETURNS SETOF或RETURNS TABLE

• 函数的定义中没有SET子句

• 函数体必须由单个SELECT语句组成,即使在应用任何适用的规则重写之后也是如此

• 函数体查询返回的列类型必须与声明的结果列的类型匹配。但是,如果最外层函数体查询中没有集合运算操作 (UNION,INTERSECT,EXCEPT),则允许对删除的列进行调整,并允许二进制级别的隐式类型转换,前提是受影响的列不用于排序

• 如果函数被声明为RETURNS SETOF RECORD且没有OUT参数,则函数体查询结果的类型,必须与调用方提供的列定义列表匹配

请注意,这些条件允许函数体是一个复杂的查询,甚至带有 CTE 表达式(但不包括递归 CTE)的查询。这使得可内联的 SQL 函数比视图更加强大,因为它们可以以视图无法做到的方式获取参数,同时保留了视图的许多优点。

示例

让我们开始设置一个小的测试用例:

CREATE TABLE t (id integer, str text);

INSERT INTO t (id, str)
  SELECT i, 'xxx'
    FROM generate_series(1, 10000) AS s(i);

下面是一个可由优化器内联的函数示例:

CREATE OR REPLACE FUNCTION ld(int)
  RETURNS numeric AS $$
  SELECT log(2, $1);
$$ LANGUAGE 'sql' IMMUTABLE;

这是一个标记为IMMUTABLE的普通 SQL 函数。它是可以被优化器进行优化的。为了简单起见,该函数所做的只是计算一个对数:

SELECT ld(1024);
         ld
---------------------
 10.0000000000000000
(1 row)

如您所见,该函数工作符合预期。

为了演示事情是如何运作的,我们在函数上创建了一个索引:

CREATE INDEX idx_ld ON t (ld(id));

正如预期的那样,该索引会像任何其他索引一样被使用。但是,让我们来仔细看看索引条件:

EXPLAIN SELECT * FROM t WHERE ld(id) = 10;
                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=4.67..30.55 rows=50 width=8)
   Recheck Cond: (log('2'::numeric, (id)::numeric) = '10'::numeric)
   ->  Bitmap Index Scan on idx_ld  (cost=0.00..4.66 rows=50 width=0)
         Index Cond: (log('2'::numeric, (id)::numeric) = '10'::numeric)
(4 rows)

ANALYZE t;

EXPLAIN SELECT * FROM t WHERE ld(id) = 10;
                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using idx_ld on t  (cost=0.29..8.30 rows=1 width=8)
   Index Cond: (log('2'::numeric, (id)::numeric) = '10'::numeric)
(2 rows)

这里重要的观察结果是,索引条件实际上查找的是 log 函数而不是 ld 函数。优化器已完全消除了函数调用。还值得一提的是,更新的优化器统计信息对于生成有效的计划非常重要。

逻辑上讲,这为以下查询打开了优化的大门:

EXPLAIN SELECT * FROM t WHERE log(2, id) = 10;
                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using idx_ld on t  (cost=0.29..8.30 rows=1 width=8)
   Index Cond: (log('2'::numeric, (id)::numeric) = '10'::numeric)
(2 rows)

优化器设法内联了该函数,并为我们提供了一个索引扫描,这远远优于高开销的顺序操作。

关键词:SQL 函数      点击(3)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多SQL 函数相关>>>