plpgsql function incurs huge performance overhead

Question

I have the following fairly simple query in a Report using Postgres 9.3 in the DB back-end:

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

I'm very new to SQL optimization, and I'm trying to understand the following behaviour:

At the moment the isReportAdmin function simply returns 'true'

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

When I run the report query, it takes approximately two minutes to execute.

If I replace the function call with simply: CASE WHEN true THEN...

it takes two seconds to return.

Could you explain in intermediate terms, why would the function call incur so much overhead? Is there a common strategy for dealing with this kind of function in a query?

Answer 1

Yes, PL/pgSQL does incur a performance overhead. You can remove that overhead in most cases by defining your function as language sql:

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

If it is defined as stable Postgres is very often able to inline the (SQL) function and get rid of the overhead completely.

---

Most probably your actual function does some lookup in the database. You can still keep that as a SQL function. If you e.g. have a table named user_roles where you need to do the lookup for the passed employee_id, you could use something like this:

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;