Add COST and ROWS options to CREATE/ALTER FUNCTION, plus underlying pg_proc

columns procost and prorows, to allow simple user adjustment of the estimated
cost of a function call, as well as control of the estimated number of rows
returned by a set-returning function.  We might eventually wish to extend this
to allow function-specific estimation routines, but there seems to be
consensus that we should try a simple constant estimate first.  In particular
this provides a relatively simple way to control the order in which different
WHERE clauses are applied in a plan node, which is a Good Thing in view of the
fact that the recent EquivalenceClass planner rewrite made that much less
predictable than before.

3 files changed, 79 insertions, 5 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 0911ae15b46..8d64dd3ec83 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.142 2007/01/20 23:13:01 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.143 2007/01/22 01:35:19 tgl Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -3376,6 +3376,22 @@
      </row>
 
      <row>
+      <entry><structfield>procost</structfield></entry>
+      <entry><type>float4</type></entry>
+      <entry></entry>
+      <entry>Estimated execution cost (in units of
+       <xref linkend="guc-cpu-operator-cost">); if <structfield>proretset</>,
+       this is cost per row returned</entry>
+     </row>
+
+     <row>
+      <entry><structfield>prorows</structfield></entry>
+      <entry><type>float4</type></entry>
+      <entry></entry>
+      <entry>Estimated number of result rows (zero if not <structfield>proretset</>)</entry>
+     </row>
+
+     <row>
       <entry><structfield>proisagg</structfield></entry>
       <entry><type>bool</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml
index d56437538e9..150ca3d418f 100644
--- a/doc/src/sgml/ref/alter_function.sgml
+++ b/doc/src/sgml/ref/alter_function.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.12 2006/09/16 00:30:16 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.13 2007/01/22 01:35:19 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -34,6 +34,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
     CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
     IMMUTABLE | STABLE | VOLATILE
     [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
+    COST <replaceable class="parameter">execution_cost</replaceable>
+    ROWS <replaceable class="parameter">result_rows</replaceable>
 </synopsis>
  </refsynopsisdiv>
   
@@ -186,6 +188,30 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
     </listitem>
    </varlistentry>
 
+    <varlistentry>
+     <term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>
+
+     <listitem>
+      <para>
+       Change the estimated execution cost of the function.
+       See <xref linkend="sql-createfunction"
+       endterm="sql-createfunction-title"> for more information.
+      </para>
+     </listitem>
+   </varlistentry>
+
+    <varlistentry>
+     <term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>
+
+     <listitem>
+      <para>
+       Change the estimated number of rows returned by a set-returning
+       function.  See <xref linkend="sql-createfunction"
+       endterm="sql-createfunction-title"> for more information.
+      </para>
+     </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>RESTRICT</literal></term>
 
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3381c1db996..5309059de30 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.70 2006/11/10 20:52:18 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.71 2007/01/22 01:35:19 tgl Exp $
 -->
 
 <refentry id="SQL-CREATEFUNCTION">
@@ -26,6 +26,8 @@ CREATE [ OR REPLACE ] FUNCTION
     | IMMUTABLE | STABLE | VOLATILE
     | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
     | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
+    | COST <replaceable class="parameter">execution_cost</replaceable>
+    | ROWS <replaceable class="parameter">result_rows</replaceable>
     | AS '<replaceable class="parameter">definition</replaceable>'
     | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
   } ...
@@ -52,7 +54,7 @@ CREATE [ OR REPLACE ] FUNCTION
   </para>
 
   <para>
-   To update the definition of an existing function, use
+   To replace the current definition of an existing function, use
    <command>CREATE OR REPLACE FUNCTION</command>.  It is not possible
    to change the name or argument types of a function this way (if you
    tried, you would actually be creating a new, distinct function).
@@ -290,6 +292,35 @@ CREATE [ OR REPLACE ] FUNCTION
    </varlistentry>
 
     <varlistentry>
+     <term><replaceable class="parameter">execution_cost</replaceable></term>
+
+     <listitem>
+      <para>
+       A positive number giving the estimated execution cost for the function,
+       in units of <xref linkend="guc-cpu-operator-cost">.  If the function
+       returns a set, this is the cost per returned row.  If the cost is
+       not specified, 1 unit is assumed for C-language and internal functions,
+       and 100 units for functions in all other languages.  Larger values
+       cause the planner to try to avoid evaluating the function more often
+       than necessary.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><replaceable class="parameter">result_rows</replaceable></term>
+
+     <listitem>
+      <para>
+       A positive number giving the estimated number of rows that the planner
+       should expect the function to return.  This is only allowed when the
+       function is declared to return a set.  The default assumption is
+       1000 rows.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
      <term><replaceable class="parameter">definition</replaceable></term>
 
      <listitem>
@@ -400,7 +431,8 @@ CREATE FUNCTION foo(int, out text) ...
 
    <para>
     When repeated <command>CREATE FUNCTION</command> calls refer to
-    the same object file, the file is only loaded once.  To unload and
+    the same object file, the file is only loaded once per session.
+    To unload and
     reload the file (perhaps during development), use the <xref
     linkend="sql-load" endterm="sql-load-title"> command.
    </para>