postgresql - PostgreSQL 内联函数行为

问题描述

我想知道是否有人可以确认 PostgreSQL 内联的预期行为？

在 Microsoft SQL 世界中，任何被确定为内联的函数都将在多行的单次迭代中使用（函数体本质上是注入到调用者语句中，使其基于集合 [One call] 而不是每个输入数据行的探针 [许多调用]）。

我和我的团队正在努力在没有诸如 MSSQL 之类的 Profiler 的情况下证明这一点，但我们最终能够证明这一点，并发现我们认为内联的函数的迭代次数与它所经过的行数成正比运作。

我们通过在函数 (pg_sleep) 中引入有意等待来做到这一点，我们可以看到 N 秒的等待导致总执行时间为 Rows*N，即超过 6 行的输入，等待 1 秒等于 6 秒，等待 2 是 12，依此类推。

所以我们的问题是：

PostgreSQL 中的内联是我们认为的那样吗（相当于 MSSQLs 内联函数 [Type='IF']）？
是否有一个分析工具能够清楚地显示这一点，就像 MSSQL 中的 Profiler 一样？
有没有我们可以查看的元数据标记来确认/否认我们的函数确实是可内联的？

标签： postgresqlquery-optimizationset-based

术语“内联”在 Postgres 中具有不同的含义。这通常是指language sql在另一个查询中使用时完全被包含的查询替换的函数，例如这个函数

create or replace function customers_in_zip(p_zip_code varchar(5))
  returns setof customers
as
$$
  select *
  from customers
  where zip_code = p_zip_code;
$$
language sql;

像这样使用：

select *
from orders o 
  join customers_in_zip('42') c on o.customer_id = c.id;

将由优化器扩展为：

select *
from orders o 
  join customers c on o.customer_id = c.id and c.zip_code = '42';

这种类型的内联可以在使用生成执行计划时看到explain (analyze)。为此，必须将功能标记为immutable或stable

例如，如果函数可以“内联”，则计划看起来像这样：

Nested Loop  (cost=2.39..200.79 rows=79 width=52) (actual time=0.021..0.165 rows=115 loops=1)
  ->  Bitmap Heap Scan on public.customers  (cost=1.97..20.71 rows=13 width=28) (actual time=0.014..0.023 rows=15 loops=1)
        Recheck Cond: ((customers.zip_code)::text = '80807'::text)
        ->  Bitmap Index Scan on customers_zip_code_idx  (cost=0.00..1.96 rows=13 width=0) (actual time=0.010..0.010 rows=15 loops=1)
              Index Cond: ((customers.zip_code)::text = '80807'::text)
  ->  Index Scan using idx_orders_cust_id on public.orders o  (cost=0.42..13.84 rows=8 width=24) (actual time=0.003..0.008 rows=8 loops=15)
        Index Cond: (o.customer_id = customers.id)

如您所见，没有对函数的引用（没有函数的查询计划看起来几乎相同）。

如果函数没有被内联（例如因为它没有被声明stable或者因为它是一个 PL/pgSQL 函数而不是一个 SQL 函数），那么计划看起来像这样：

嵌套循环（成本=0.68..139.94 行=77 宽度=110）（实际时间=0.710..0.862 行=115 循环=1）
  -> public.customers_in_zip c 上的函数扫描（成本=0.25..0.26 行=10 宽度=86）（实际时间=0.696..0.697 行=15 循环=1）
        函数调用：customers_in_zip('42'::character varying)
        缓冲区：共享命中=18
  -> 在 public.orders o 上使用 idx_orders_cust_id 进行索引扫描（成本=0.42..13.96 行=8 宽度=24）（实际时间=0.004..0.009 行=8 循环=15）
        输出：o.id、o.customer_id、o.order_date、o.amount、o.sales_person_id
        指数条件：（o.customer_id = c.id）

根据您的描述，您似乎不是指那种“内联”，而是如果标量函数不依赖于从行中获取的值，是否只调用一次，例如：

select col1, some_function(), col2
from some_table;

如果some_function()声明 immutable它只会被调用一次。

从手册中引用

IMMUTABLE 表示该函数不能修改数据库，并且在给定相同的参数值时总是返回相同的结果；[...] 如果给出此选项，则任何具有全常量参数的函数调用都可以立即替换为函数值。

这不是您可以直接在执行计划中看到的，但下面将演示它：

create function expensive_scalar(p_some_input integer)
  returns integer
as 
$$
begin
  perform pg_sleep(10);
  return p_some_input * 2;
end;  
$$
language plpgsql
IMMUTABLE;

使perform pg_sleep(10);函数需要 10 秒才能执行。以下查询将调用该函数一百次：

select i, expensive_scalar(2)
from generate_series(1,100) i;

但是执行只用了10多秒，这清楚地表明该函数只被调用了一次。

据我所知，Postgres 还将缓存标记为stable在执行单个语句期间的函数的结果（对于相同的输入值）。

不过，这有点难以展示。通常，您可以通过将raise notice语句（Postgres 的等效于print）放入函数中并查看它们的打印频率来做到这一点。

postgresql - PostgreSQL 内联函数行为

问题描述

解决方案

推荐阅读