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)
Good work, this is a nice performance boost!
ReplyDeleteWouldn'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?
It is not a problem with LATERAL JOIN - these queries are prepared - look to README https://github.com/okbob/plpgsql_check#mass-check
ReplyDeleteSELECT 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'