Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5a7471c

Browse files
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 estimatedcost of a function call, as well as control of the estimated number of rowsreturned by a set-returning function. We might eventually wish to extend thisto allow function-specific estimation routines, but there seems to beconsensus that we should try a simple constant estimate first. In particularthis provides a relatively simple way to control the order in which differentWHERE clauses are applied in a plan node, which is a Good Thing in view of thefact that the recent EquivalenceClass planner rewrite made that much lesspredictable than before.
1 parenta85e9c6 commit5a7471c

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-
Toupdate the definition of an existing function, use
57+
Toreplace 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
DatumallParameterTypes,
7373
DatumparameterModes,
74-
DatumparameterNames)
74+
DatumparameterNames,
75+
float4procost,
76+
float4prorows)
7577
{
7678
Oidretval;
7779
intparameterCount;
@@ -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)
[8]ページ先頭
©2009-2025 Movatter.jp