CODEWITHSUNDEEP
sqlpostgresql
Barguast
Barguast

Reputation: 6186

Filtering set returning function results

I'm hoping to clarify my understanding of how set returning functions behave behind the scenes in PostgreSQL.

Let's set I have a set returning function called 'a_at_date' which returns:

 SELECT * FROM a WHERE date = a_date

Where a_date is a function parameter.

If I use this like so:

SELECT *
FROM a_at_date(a_date) 
WHERE other_field = 123

Then, for example, can this ever take advantage of an index on [date, other_field] in the same way that this can:

SELECT *
FROM a
WHERE a = a_date AND other_field = 123

In other words does a set returning function run in isolation of any outer query and therefore limiting the indexing options?

Upvotes: 1

Views: 732

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

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 backend/optimizer/prep/prepjointree.c:

/*
 * inline_set_returning_functions
 *              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
 * structure.
 */

There are also two instructive comments in inline_set_returning_function in backend/optimizer/util/clauses.c:

/*
 * 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.)
 */

and

/*
 * 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
 * here.)
 */

Use 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)
SELECT *
FROM a_at_date(current_date)
WHERE other_field = 'value';

                               QUERY PLAN                                
-------------------------------------------------------------------------
 Seq Scan on laurenz.a
   Output: a.date, a.other_field
   Filter: ((a.other_field = 'value'::text) AND (a.date = CURRENT_DATE))
(3 rows)

Upvotes: 2

Related Questions