In principle, the optimizer has no clue what a function does – the function body is a string that is handled by the call handler of the function's procedural language.
The one exception are functions written in
LANGUAGE sql. If they are simple enough, and inlining them can be proven not to change the semantics of the SQL statement, the query rewriter will inline them.
See the following comment in
* Attempt to "inline" set-returning functions in the FROM clause.
* If an RTE_FUNCTION rtable entry invokes a set-returning function that
* contains just a simple SELECT, we can convert the rtable entry to an
* RTE_SUBQUERY entry exposing the SELECT directly. This is especially
* useful if the subquery can then be "pulled up" for further optimization,
* but we do it even if not, to reduce executor overhead.
* This has to be done before we have started to do any optimization of
* subqueries, else any such steps wouldn't get applied to subqueries
* obtained via inlining. However, we do it after pull_up_sublinks
* so that we can inline any functions used in SubLink subselects.
* Like most of the planner, this feels free to scribble on its input data
There are also two instructive comments in
* Forget it if the function is not SQL-language or has other showstopper
* properties. In particular it mustn't be declared STRICT, since we
* couldn't enforce that. It also mustn't be VOLATILE, because that is
* supposed to cause it to be executed with its own snapshot, rather than
* sharing the snapshot of the calling query. (Rechecking proretset is
* just paranoia.)
* Make sure the function (still) returns what it's declared to. This
* will raise an error if wrong, but that's okay since the function would
* fail at runtime anyway. Note that check_sql_fn_retval will also insert
* RelabelType(s) and/or NULL columns if needed to make the tlist
* expression(s) match the declared type of the function.
* If the function returns a composite type, don't inline unless the check
* shows it's returning a whole tuple result; otherwise what it's
* returning is a single composite column which is not what we need. (Like
* check_sql_fn_retval, we deliberately exclude domains over composite
EXPLAIN to see if your function is inlined.
An example where it works:
CREATE TABLE a (
"date" date NOT NULL,
other_field text NOT NULL
CREATE OR REPLACE FUNCTION a_at_date(date)
RETURNS TABLE ("date" date, other_field text)
LANGUAGE sql STABLE CALLED ON NULL INPUT
AS 'SELECT "date", other_field FROM a WHERE "date" = $1';
EXPLAIN (VERBOSE, COSTS off)
WHERE other_field = 'value';
Seq Scan on laurenz.a
Output: a.date, a.other_field
Filter: ((a.other_field = 'value'::text) AND (a.date = CURRENT_DATE))