1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>38.11. Function Optimization Information</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="xfunc-c.html" title="38.10. C-Language Functions" /><link rel="next" href="xaggr.html" title="38.12. User-Defined Aggregates" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">38.11. Function Optimization Information</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xfunc-c.html" title="38.10. C-Language Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xaggr.html" title="38.12. User-Defined Aggregates">Next</a></td></tr></table><hr /></div><div class="sect1" id="XFUNC-OPTIMIZATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">38.11. Function Optimization Information <a href="#XFUNC-OPTIMIZATION" class="id_link">#</a></h2></div></div></div><a id="id-1.8.3.14.2" class="indexterm"></a><p>
By default, a function is just a <span class="quote">“<span class="quote">black box</span>”</span> that the
database system knows very little about the behavior of. However,
that means that queries using the function may be executed much less
efficiently than they could be. It is possible to supply additional
knowledge that helps the planner optimize function calls.
</p><p>
Some basic facts can be supplied by declarative annotations provided in
the <a class="link" href="sql-createfunction.html" title="CREATE FUNCTION"><code class="command">CREATE FUNCTION</code></a> command. Most important of
these is the function's <a class="link" href="xfunc-volatility.html" title="38.7. Function Volatility Categories">volatility
category</a> (<code class="literal">IMMUTABLE</code>, <code class="literal">STABLE</code>,
or <code class="literal">VOLATILE</code>); one should always be careful to
specify this correctly when defining a function.
The parallel safety property (<code class="literal">PARALLEL
UNSAFE</code>, <code class="literal">PARALLEL RESTRICTED</code>, or
<code class="literal">PARALLEL SAFE</code>) must also be specified if you hope
to use the function in parallelized queries.
It can also be useful to specify the function's estimated execution
cost, and/or the number of rows a set-returning function is estimated
to return. However, the declarative way of specifying those two
facts only allows specifying a constant value, which is often
inadequate.
</p><p>
It is also possible to attach a <em class="firstterm">planner support
function</em> to an SQL-callable function (called
its <em class="firstterm">target function</em>), and thereby provide
knowledge about the target function that is too complex to be
represented declaratively. Planner support functions have to be
written in C (although their target functions might not be), so this is
an advanced feature that relatively few people will use.
</p><p>
A planner support function must have the SQL signature
</p><pre class="programlisting">
supportfn(internal) returns internal
</pre><p>
It is attached to its target function by specifying
the <code class="literal">SUPPORT</code> clause when creating the target function.
</p><p>
The details of the API for planner support functions can be found in
file <code class="filename">src/include/nodes/supportnodes.h</code> in the
<span class="productname">PostgreSQL</span> source code. Here we provide
just an overview of what planner support functions can do.
The set of possible requests to a support function is extensible,
so more things might be possible in future versions.
</p><p>
Some function calls can be simplified during planning based on
properties specific to the function. For example,
<code class="literal">int4mul(n, 1)</code> could be simplified to
just <code class="literal">n</code>. This type of transformation can be
performed by a planner support function, by having it implement
the <code class="literal">SupportRequestSimplify</code> request type.
The support function will be called for each instance of its target
function found in a query parse tree. If it finds that the particular
call can be simplified into some other form, it can build and return a
parse tree representing that expression. This will automatically work
for operators based on the function, too — in the example just
given, <code class="literal">n * 1</code> would also be simplified to
<code class="literal">n</code>.
(But note that this is just an example; this particular
optimization is not actually performed by
standard <span class="productname">PostgreSQL</span>.)
We make no guarantee that <span class="productname">PostgreSQL</span> will
never call the target function in cases that the support function could
simplify. Ensure rigorous equivalence between the simplified
expression and an actual execution of the target function.
</p><p>
For target functions that return <code class="type">boolean</code>, it is often useful to estimate
the fraction of rows that will be selected by a <code class="literal">WHERE</code> clause using that
function. This can be done by a support function that implements
the <code class="literal">SupportRequestSelectivity</code> request type.
</p><p>
If the target function's run time is highly dependent on its inputs,
it may be useful to provide a non-constant cost estimate for it.
This can be done by a support function that implements
the <code class="literal">SupportRequestCost</code> request type.
</p><p>
For target functions that return sets, it is often useful to provide
a non-constant estimate for the number of rows that will be returned.
This can be done by a support function that implements
the <code class="literal">SupportRequestRows</code> request type.
</p><p>
For target functions that return <code class="type">boolean</code>, it may be possible to
convert a function call appearing in <code class="literal">WHERE</code> into an indexable operator
clause or clauses. The converted clauses might be exactly equivalent
to the function's condition, or they could be somewhat weaker (that is,
they might accept some values that the function condition does not).
In the latter case the index condition is said to
be <em class="firstterm">lossy</em>; it can still be used to scan an index,
but the function call will have to be executed for each row returned by
the index to see if it really passes the <code class="literal">WHERE</code> condition or not.
To create such conditions, the support function must implement
the <code class="literal">SupportRequestIndexCondition</code> request type.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xfunc-c.html" title="38.10. C-Language Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xaggr.html" title="38.12. User-Defined Aggregates">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.10. C-Language Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.12. User-Defined Aggregates</td></tr></table></div></body></html>
|
