summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/xfunc-optimization.html
blob: 246d9d11caeadf48b4cc062647e846db77c5d95e (plain)
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 15.6 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</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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.12. User-Defined Aggregates</td></tr></table></div></body></html>