diff options
Diffstat (limited to 'doc/src/sgml/html/xfunc-optimization.html')
| -rw-r--r-- | doc/src/sgml/html/xfunc-optimization.html | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/doc/src/sgml/html/xfunc-optimization.html b/doc/src/sgml/html/xfunc-optimization.html new file mode 100644 index 0000000..d468dee --- /dev/null +++ b/doc/src/sgml/html/xfunc-optimization.html @@ -0,0 +1,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.5 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.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.12. User-Defined Aggregates</td></tr></table></div></body></html>
\ No newline at end of file |