Skip to content

Commit 5a7471c

Browse files
tglsfdctglsfdc
committed
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.
1 parent a85e9c6 commit 5a7471c

File tree

27 files changed

+2562
-2102
lines changed

27 files changed

+2562
-2102
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.142 2007/01/20 23:13:01 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.143 2007/01/22 01:35:19 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -3375,6 +3375,22 @@
33753375
<entry>Implementation language or call interface of this function</entry>
33763376
</row>
33773377

3378+
<row>
3379+
<entry><structfield>procost</structfield></entry>
3380+
<entry><type>float4</type></entry>
3381+
<entry></entry>
3382+
<entry>Estimated execution cost (in units of
3383+
<xref linkend="guc-cpu-operator-cost">); if <structfield>proretset</>,
3384+
this is cost per row returned</entry>
3385+
</row>
3386+
3387+
<row>
3388+
<entry><structfield>prorows</structfield></entry>
3389+
<entry><type>float4</type></entry>
3390+
<entry></entry>
3391+
<entry>Estimated number of result rows (zero if not <structfield>proretset</>)</entry>
3392+
</row>
3393+
33783394
<row>
33793395
<entry><structfield>proisagg</structfield></entry>
33803396
<entry><type>bool</type></entry>

‎doc/src/sgml/ref/alter_function.sgml

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.12 2006/09/16 00:30:16 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.13 2007/01/22 01:35:19 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -34,6 +34,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
3434
CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
3535
IMMUTABLE | STABLE | VOLATILE
3636
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
37+
COST <replaceable class="parameter">execution_cost</replaceable>
38+
ROWS <replaceable class="parameter">result_rows</replaceable>
3739
</synopsis>
3840
</refsynopsisdiv>
3941

@@ -186,6 +188,30 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
186188
</listitem>
187189
</varlistentry>
188190

191+
<varlistentry>
192+
<term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>
193+
194+
<listitem>
195+
<para>
196+
Change the estimated execution cost of the function.
197+
See <xref linkend="sql-createfunction"
198+
endterm="sql-createfunction-title"> for more information.
199+
</para>
200+
</listitem>
201+
</varlistentry>
202+
203+
<varlistentry>
204+
<term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>
205+
206+
<listitem>
207+
<para>
208+
Change the estimated number of rows returned by a set-returning
209+
function. See <xref linkend="sql-createfunction"
210+
endterm="sql-createfunction-title"> for more information.
211+
</para>
212+
</listitem>
213+
</varlistentry>
214+
189215
<varlistentry>
190216
<term><literal>RESTRICT</literal></term>
191217

‎doc/src/sgml/ref/create_function.sgml

Lines changed: 35 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.70 2006/11/10 20:52:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.71 2007/01/22 01:35:19 tgl Exp $
33
-->
44

55
<refentry id="SQL-CREATEFUNCTION">
@@ -26,6 +26,8 @@ CREATE [ OR REPLACE ] FUNCTION
2626
| IMMUTABLE | STABLE | VOLATILE
2727
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
2828
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
29+
| COST <replaceable class="parameter">execution_cost</replaceable>
30+
| ROWS <replaceable class="parameter">result_rows</replaceable>
2931
| AS '<replaceable class="parameter">definition</replaceable>'
3032
| AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
3133
} ...
@@ -52,7 +54,7 @@ CREATE [ OR REPLACE ] FUNCTION
5254
</para>
5355

5456
<para>
55-
To update the definition of an existing function, use
57+
To replace the current definition of an existing function, use
5658
<command>CREATE OR REPLACE FUNCTION</command>. It is not possible
5759
to change the name or argument types of a function this way (if you
5860
tried, you would actually be creating a new, distinct function).
@@ -289,6 +291,35 @@ CREATE [ OR REPLACE ] FUNCTION
289291
</listitem>
290292
</varlistentry>
291293

294+
<varlistentry>
295+
<term><replaceable class="parameter">execution_cost</replaceable></term>
296+
297+
<listitem>
298+
<para>
299+
A positive number giving the estimated execution cost for the function,
300+
in units of <xref linkend="guc-cpu-operator-cost">. If the function
301+
returns a set, this is the cost per returned row. If the cost is
302+
not specified, 1 unit is assumed for C-language and internal functions,
303+
and 100 units for functions in all other languages. Larger values
304+
cause the planner to try to avoid evaluating the function more often
305+
than necessary.
306+
</para>
307+
</listitem>
308+
</varlistentry>
309+
310+
<varlistentry>
311+
<term><replaceable class="parameter">result_rows</replaceable></term>
312+
313+
<listitem>
314+
<para>
315+
A positive number giving the estimated number of rows that the planner
316+
should expect the function to return. This is only allowed when the
317+
function is declared to return a set. The default assumption is
318+
1000 rows.
319+
</para>
320+
</listitem>
321+
</varlistentry>
322+
292323
<varlistentry>
293324
<term><replaceable class="parameter">definition</replaceable></term>
294325

@@ -400,7 +431,8 @@ CREATE FUNCTION foo(int, out text) ...
400431

401432
<para>
402433
When repeated <command>CREATE FUNCTION</command> calls refer to
403-
the same object file, the file is only loaded once. To unload and
434+
the same object file, the file is only loaded once per session.
435+
To unload and
404436
reload the file (perhaps during development), use the <xref
405437
linkend="sql-load" endterm="sql-load-title"> command.
406438
</para>

‎src/backend/bootstrap/bootstrap.c

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/bootstrap/bootstrap.c,v 1.228 2007/01/05 22:19:24 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/bootstrap/bootstrap.c,v 1.229 2007/01/22 01:35:19 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -115,16 +115,18 @@ static const struct typinfo TypInfo[] = {
115115
F_BYTEAIN, F_BYTEAOUT},
116116
{"char", CHAROID, 0, 1, true, 'c', 'p',
117117
F_CHARIN, F_CHAROUT},
118-
{"name", NAMEOID, CHAROID, NAMEDATALEN, false, 'i', 'p',
119-
F_NAMEIN, F_NAMEOUT},
120118
{"int2", INT2OID, 0, 2, true, 's', 'p',
121119
F_INT2IN, F_INT2OUT},
122120
{"int4", INT4OID, 0, 4, true, 'i', 'p',
123121
F_INT4IN, F_INT4OUT},
124-
{"regproc", REGPROCOID, 0, 4, true, 'i', 'p',
125-
F_REGPROCIN, F_REGPROCOUT},
122+
{"float4", FLOAT4OID, 0, 4, false, 'i', 'p',
123+
F_FLOAT4IN, F_FLOAT4OUT},
124+
{"name", NAMEOID, CHAROID, NAMEDATALEN, false, 'i', 'p',
125+
F_NAMEIN, F_NAMEOUT},
126126
{"regclass", REGCLASSOID, 0, 4, true, 'i', 'p',
127127
F_REGCLASSIN, F_REGCLASSOUT},
128+
{"regproc", REGPROCOID, 0, 4, true, 'i', 'p',
129+
F_REGPROCIN, F_REGPROCOUT},
128130
{"regtype", REGTYPEOID, 0, 4, true, 'i', 'p',
129131
F_REGTYPEIN, F_REGTYPEOUT},
130132
{"text", TEXTOID, 0, -1, false, 'i', 'x',

‎src/backend/catalog/pg_aggregate.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.84 2007/01/05 22:19:25 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.85 2007/01/22 01:35:20 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -214,7 +214,9 @@ AggregateCreate(const char *aggName,
214214
numArgs), /* paramTypes */
215215
PointerGetDatum(NULL), /* allParamTypes */
216216
PointerGetDatum(NULL), /* parameterModes */
217-
PointerGetDatum(NULL)); /* parameterNames */
217+
PointerGetDatum(NULL), /* parameterNames */
218+
1, /* procost */
219+
0); /* prorows */
218220

219221
/*
220222
* Okay to create the pg_aggregate entry.

‎src/backend/catalog/pg_proc.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.142 2007/01/05 22:19:25 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.143 2007/01/22 01:35:20 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -71,7 +71,9 @@ ProcedureCreate(const char *procedureName,
7171
oidvector *parameterTypes,
7272
Datum allParameterTypes,
7373
Datum parameterModes,
74-
Datum parameterNames)
74+
Datum parameterNames,
75+
float4 procost,
76+
float4 prorows)
7577
{
7678
Oid retval;
7779
int parameterCount;
@@ -219,6 +221,8 @@ ProcedureCreate(const char *procedureName,
219221
values[Anum_pg_proc_pronamespace - 1] = ObjectIdGetDatum(procNamespace);
220222
values[Anum_pg_proc_proowner - 1] = ObjectIdGetDatum(GetUserId());
221223
values[Anum_pg_proc_prolang - 1] = ObjectIdGetDatum(languageObjectId);
224+
values[Anum_pg_proc_procost - 1] = Float4GetDatum(procost);
225+
values[Anum_pg_proc_prorows - 1] = Float4GetDatum(prorows);
222226
values[Anum_pg_proc_proisagg - 1] = BoolGetDatum(isAgg);
223227
values[Anum_pg_proc_prosecdef - 1] = BoolGetDatum(security_definer);
224228
values[Anum_pg_proc_proisstrict - 1] = BoolGetDatum(isStrict);

0 commit comments

Comments
 (0)