On Fri, Apr 02, 2021 at 02:24:59PM -0700, Dan Lynch wrote: > Does anyone know details of, or where to find more information about the > implications of the optimizer on the quals/checks for the policies being > functions vs inline?
Roughly, the PostgreSQL optimizer treats LANGUAGE SQL functions like a C compiler treats "extern inline" functions. Other PostgreSQL functions behave like C functions in a shared library. Non-SQL functions can do arbitrary things, and the optimizer knows only facts like their volatility and the value given in CREATE FUNCTION ... COST. > I suppose if the > get_group_ids_of_current_user() function is marked as STABLE, would the > optimizer cache this value for every row in a SELECT that returned > multiple rows? While there was a patch to implement caching, it never finished. The optimizer is allowed to, and sometimes does, choose plan shapes that reduce the number of function calls. > Is it possible that if the function is sql vs plpgsql it > makes a difference? Yes; see inline_function() in the PostgreSQL source. The hard part of $SUBJECT is creating the infrastructure to inline across a SECURITY DEFINER boundary. Currently, a single optimizable statement operates under just one user identity. Somehow, the optimizer would need to translate the SECURITY DEFINER call into a list of moments where the executor shall switch user ID, then maintain that list across further optimization steps. security_barrier views are the most-similar thing, but as Joe Conway mentioned, views differ from SECURITY DEFINER in crucial ways.