sql - plpgsql 函数会带来巨大的性能开销

Question

我在数据库后端使用 Postgres 9.3 的报告中有以下相当简单的查询:

SELECT * FROM source
JOIN sourcelevel USING (source_id)
JOIN level USING (level_id)
WHERE
  CASE WHEN isReportAdmin(1) THEN true
  ELSE source_id in (SELECT source_id FROM sourceemployee WHERE employee = 1)
  END

我是 SQL 优化的新手，我正在尝试理解以下行为:

目前 isReportAdmin 函数只返回“true”

create or replace function isReportAdmin(employee_id integer) RETURNS bool AS $$
        BEGIN
                RETURN 't';
        END;
$$ LANGUAGE plpgsql;

当我运行报告查询时，执行大约需要两分钟。

如果我简单地将函数调用替换为:CASE WHEN true THEN...

需要两秒才能返回。

您能否用中间术语解释一下，为什么函数调用会产生如此大的开销？在查询中是否有处理此类函数的通用策略？

Answer 1

是的，PL/pgSQL 确实会导致 performance overhead .在大多数情况下，您可以通过将函数定义为 language sql 来消除该开销:

create or replace function isreportadmin(employee_id integer) 
  RETURNS bool 
AS $$
  select true;
$$ 
LANGUAGE sql
stable;

如果它被定义为stable Postgres 通常能够inline (SQL) 函数并完全消除开销。

---

您的实际函数很可能会在数据库中进行一些查找。您仍然可以将其保留为 SQL 函数。如果你例如有一个名为 user_roles 的表，您需要在其中查找传递的 employee_id，您可以使用如下内容:

create or replace function isreportadmin(p_employee_id integer) 
  RETURNS bool 
AS $$
  select exists (select *
                 from user_roles ur
                 where ur.employee_id = p_employee_id 
                   and ur.is_admin);
$$ 
LANGUAGE sql
stable;