diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
| commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
| tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/xfunc-optimization.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-13-upstream.tar.xz postgresql-13-upstream.zip | |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
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..cb1fe7b --- /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>37.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 V1.79.1" /><link rel="prev" href="xfunc-c.html" title="37.10. C-Language Functions" /><link rel="next" href="xaggr.html" title="37.12. User-Defined Aggregates" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.11. Function Optimization Information</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xfunc-c.html" title="37.10. C-Language Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 37. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xaggr.html" title="37.12. User-Defined Aggregates">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="XFUNC-OPTIMIZATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.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="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> command. Most important of + these is the function's <a class="link" href="xfunc-volatility.html" title="37.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 a 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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xfunc-c.html" title="37.10. C-Language Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xaggr.html" title="37.12. User-Defined Aggregates">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.10. C-Language Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 37.12. User-Defined Aggregates</td></tr></table></div></body></html>
\ No newline at end of file |