Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Bevaka 1
Stjärnmärk 0
Wiki 捐赠
gooderp18绿色标准版
Du kan inte välja fler än 25 ämnen Ämnen måste starta med en bokstav eller siffra, kan innehålla bindestreck ('-') och vara max 35 tecken långa.
Gren: master
Grenar Taggar
${ item.name }
Skapa branchen ${ searchTerm }
från 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/xfunc-optimization.html

94 lines
8.1KB
Rå Permalänk Blame Historik 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!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><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 12.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>

  3.     By default, a function is just a <span class="quote">“<span class="quote">black box</span>”</span> that the

  4.     database system knows very little about the behavior of.  However,

  5.     that means that queries using the function may be executed much less

  6.     efficiently than they could be.  It is possible to supply additional

  7.     knowledge that helps the planner optimize function calls.

  8.    </p><p>

  9.     Some basic facts can be supplied by declarative annotations provided in

  10.     the <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> command.  Most important of

  11.     these is the function's <a class="link" href="xfunc-volatility.html" title="37.7. Function Volatility Categories">volatility

  12.     category</a> (<code class="literal">IMMUTABLE</code>, <code class="literal">STABLE</code>,

  13.     or <code class="literal">VOLATILE</code>); one should always be careful to

  14.     specify this correctly when defining a function.

  15.     The parallel safety property (<code class="literal">PARALLEL

  16.     UNSAFE</code>, <code class="literal">PARALLEL RESTRICTED</code>, or

  17.     <code class="literal">PARALLEL SAFE</code>) must also be specified if you hope

  18.     to use the function in parallelized queries.

  19.     It can also be useful to specify the function's estimated execution

  20.     cost, and/or the number of rows a set-returning function is estimated

  21.     to return.  However, the declarative way of specifying those two

  22.     facts only allows specifying a constant value, which is often

  23.     inadequate.

  24.    </p><p>

  25.     It is also possible to attach a <em class="firstterm">planner support

  26.     function</em> to a SQL-callable function (called

  27.     its <em class="firstterm">target function</em>), and thereby provide

  28.     knowledge about the target function that is too complex to be

  29.     represented declaratively.  Planner support functions have to be

  30.     written in C (although their target functions might not be), so this is

  31.     an advanced feature that relatively few people will use.

  32.    </p><p>

  33.     A planner support function must have the SQL signature

  34. </p><pre class="programlisting">

  35. supportfn(internal) returns internal

  36. </pre><p>

  37.     It is attached to its target function by specifying

  38.     the <code class="literal">SUPPORT</code> clause when creating the target function.

  39.    </p><p>

  40.     The details of the API for planner support functions can be found in

  41.     file <code class="filename">src/include/nodes/supportnodes.h</code> in the

  42.     <span class="productname">PostgreSQL</span> source code.  Here we provide

  43.     just an overview of what planner support functions can do.

  44.     The set of possible requests to a support function is extensible,

  45.     so more things might be possible in future versions.

  46.    </p><p>

  47.     Some function calls can be simplified during planning based on

  48.     properties specific to the function.  For example,

  49.     <code class="literal">int4mul(n, 1)</code> could be simplified to

  50.     just <code class="literal">n</code>.  This type of transformation can be

  51.     performed by a planner support function, by having it implement

  52.     the <code class="literal">SupportRequestSimplify</code> request type.

  53.     The support function will be called for each instance of its target

  54.     function found in a query parse tree.  If it finds that the particular

  55.     call can be simplified into some other form, it can build and return a

  56.     parse tree representing that expression.  This will automatically work

  57.     for operators based on the function, too — in the example just

  58.     given, <code class="literal">n * 1</code> would also be simplified to

  59.     <code class="literal">n</code>.

  60.     (But note that this is just an example; this particular

  61.     optimization is not actually performed by

  62.     standard <span class="productname">PostgreSQL</span>.)

  63.     We make no guarantee that <span class="productname">PostgreSQL</span> will

  64.     never call the target function in cases that the support function could

  65.     simplify.  Ensure rigorous equivalence between the simplified

  66.     expression and an actual execution of the target function.

  67.    </p><p>

  68.     For target functions that return <code class="type">boolean</code>, it is often useful to estimate

  69.     the fraction of rows that will be selected by a <code class="literal">WHERE</code> clause using that

  70.     function.  This can be done by a support function that implements

  71.     the <code class="literal">SupportRequestSelectivity</code> request type.

  72.    </p><p>

  73.     If the target function's run time is highly dependent on its inputs,

  74.     it may be useful to provide a non-constant cost estimate for it.

  75.     This can be done by a support function that implements

  76.     the <code class="literal">SupportRequestCost</code> request type.

  77.    </p><p>

  78.     For target functions that return sets, it is often useful to provide

  79.     a non-constant estimate for the number of rows that will be returned.

  80.     This can be done by a support function that implements

  81.     the <code class="literal">SupportRequestRows</code> request type.

  82.    </p><p>

  83.     For target functions that return <code class="type">boolean</code>, it may be possible to

  84.     convert a function call appearing in <code class="literal">WHERE</code> into an indexable operator

  85.     clause or clauses.  The converted clauses might be exactly equivalent

  86.     to the function's condition, or they could be somewhat weaker (that is,

  87.     they might accept some values that the function condition does not).

  88.     In the latter case the index condition is said to

  89.     be <em class="firstterm">lossy</em>; it can still be used to scan an index,

  90.     but the function call will have to be executed for each row returned by

  91.     the index to see if it really passes the <code class="literal">WHERE</code> condition or not.

  92.     To create such conditions, the support function must implement

  93.     the <code class="literal">SupportRequestIndexCondition</code> request type.

  94.    </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xaggr.html">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">Home</a></td><td width="40%" align="right" valign="top"> 37.12. User-Defined Aggregates</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1