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

Commitdb147b3

Browse files
committed
Allow the planner's estimate of the fraction of a cursor's rows that will be
retrieved to be controlled through a GUC variable.Robert Hell
1 parentcf9f6c8 commitdb147b3

File tree

5 files changed

+58
-8
lines changed

5 files changed

+58
-8
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.176 2008/05/01 19:55:40 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.177 2008/05/02 21:26:09 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2238,6 +2238,26 @@ SELECT * FROM parent WHERE key = 2400;
22382238
</listitem>
22392239
</varlistentry>
22402240

2241+
<varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
2242+
<term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
2243+
<indexterm>
2244+
<primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
2245+
</indexterm>
2246+
<listitem>
2247+
<para>
2248+
Sets the planner's estimate of the fraction of a cursor's rows that
2249+
will be retrieved. The default is 0.1. Smaller values of this
2250+
setting bias the planner towards using <quote>fast start</> plans
2251+
for cursors, which will retrieve the first few rows quickly while
2252+
perhaps taking a long time to fetch all rows. Larger values
2253+
put more emphasis on the total estimated time. At the maximum
2254+
setting of 1.0, cursors are planned exactly like regular queries,
2255+
considering only the total estimated time and not how soon the
2256+
first rows might be delivered.
2257+
</para>
2258+
</listitem>
2259+
</varlistentry>
2260+
22412261
<varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
22422262
<term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
22432263
<indexterm>

‎src/backend/optimizer/plan/planner.c

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.232 2008/04/17 21:22:14 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.233 2008/05/02 21:26:09 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -42,6 +42,9 @@
4242
#include"utils/syscache.h"
4343

4444

45+
/* GUC parameter */
46+
doublecursor_tuple_fraction=DEFAULT_CURSOR_TUPLE_FRACTION;
47+
4548
/* Hook for plugins to get control in planner() */
4649
planner_hook_typeplanner_hook=NULL;
4750

@@ -142,11 +145,23 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
142145
{
143146
/*
144147
* We have no real idea how many tuples the user will ultimately FETCH
145-
* from a cursor, but it seems a good bet that he doesn't want 'em
146-
* all. Optimize for 10% retrieval (you gotta better number? Should
147-
* this be a SETtable parameter?)
148+
* from a cursor, but it is often the case that he doesn't want 'em
149+
* all, or would prefer a fast-start plan anyway so that he can
150+
* process some of the tuples sooner. Use a GUC parameter to decide
151+
* what fraction to optimize for.
152+
*/
153+
tuple_fraction=cursor_tuple_fraction;
154+
155+
/*
156+
* We document cursor_tuple_fraction as simply being a fraction,
157+
* which means the edge cases 0 and 1 have to be treated specially
158+
* here. We convert 1 to 0 ("all the tuples") and 0 to a very small
159+
* fraction.
148160
*/
149-
tuple_fraction=0.10;
161+
if (tuple_fraction >=1.0)
162+
tuple_fraction=0.0;
163+
elseif (tuple_fraction <=0.0)
164+
tuple_fraction=1e-10;
150165
}
151166
else
152167
{

‎src/backend/utils/misc/guc.c

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.450 2008/05/01 19:55:40 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.451 2008/05/02 21:26:10 tgl Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -1891,6 +1891,16 @@ static struct config_real ConfigureNamesReal[] =
18911891
DEFAULT_CPU_OPERATOR_COST,0,DBL_MAX,NULL,NULL
18921892
},
18931893

1894+
{
1895+
{"cursor_tuple_fraction",PGC_USERSET,QUERY_TUNING_OTHER,
1896+
gettext_noop("Sets the planner's estimate of the fraction of "
1897+
"a cursor's rows that will be retrieved."),
1898+
NULL
1899+
},
1900+
&cursor_tuple_fraction,
1901+
DEFAULT_CURSOR_TUPLE_FRACTION,0.0,1.0,NULL,NULL
1902+
},
1903+
18941904
{
18951905
{"geqo_selection_bias",PGC_USERSET,QUERY_TUNING_GEQO,
18961906
gettext_noop("GEQO: selective pressure within the population."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,7 @@
219219

220220
#default_statistics_target = 10# range 1-1000
221221
#constraint_exclusion = off
222+
#cursor_tuple_fraction = 0.1# range 0.0-1.0
222223
#from_collapse_limit = 8
223224
#join_collapse_limit = 8# 1 disables collapsing of explicit
224225
# JOIN clauses

‎src/include/optimizer/planmain.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.107 2008/04/17 21:22:14 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.108 2008/05/02 21:26:10 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -17,6 +17,10 @@
1717
#include"nodes/plannodes.h"
1818
#include"nodes/relation.h"
1919

20+
/* GUC parameters */
21+
#defineDEFAULT_CURSOR_TUPLE_FRACTION 0.1
22+
externdoublecursor_tuple_fraction;
23+
2024
/*
2125
* prototypes for plan/planmain.c
2226
*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp