Pages

Wednesday, November 28, 2018

plpgsql_check can detect bad default volatility flag

Common performance problem of plpgsql function when these functions are used from some more complex queries is using default VOLATILE flag. There are not possible to do more aggressive optimization of this function call. plpgsql_check can detect this issue now:

CREATE OR REPLACE FUNCTION public.flag_test1(integer)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
begin
  return $1 + 10;
end;
$function$;

CREATE OR REPLACE FUNCTION public.flag_test2(integer)
 RETURNS integer
 LANGUAGE plpgsql
 VOLATILE
AS $function$
begin
  return (select * from fufu where a = $1 limit 1);
end;
$function$;

postgres=# select * from plpgsql_check_function('flag_test1(int)', performance_warnings => true);
┌────────────────────────────────────────────────────────────────────┐
│                       plpgsql_check_function                       │
╞════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as STABLE, should be IMMUTABLE │
└────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# select * from plpgsql_check_function('flag_test2(int)', performance_warnings => true);
┌──────────────────────────────────────────────────────────────────────┐
│                        plpgsql_check_function                        │
╞══════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as VOLATILE, should be STABLE    │
└──────────────────────────────────────────────────────────────────────┘
(1 row)

2 comments:

  1. Good work, this is a nice performance boost!
    Wouldn't it be cool if select * would output the function name too in a separate result column to write a more complex query to check all functions at once?

    ReplyDelete
  2. It is not a problem with LATERAL JOIN - these queries are prepared - look to README https://github.com/okbob/plpgsql_check#mass-check

    SELECT p.proname, tgrelid::regclass, cf.*
    FROM pg_proc p
    JOIN pg_trigger t ON t.tgfoid = p.oid
    JOIN pg_language l ON p.prolang = l.oid
    JOIN pg_namespace n ON p.pronamespace = n.oid,
    LATERAL plpgsql_check_function(p.oid, t.tgrelid) cf
    WHERE n.nspname = 'public' and l.lanname = 'plpgsql'

    ReplyDelete