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

Commit65e3ea7

Browse files
committed
Increase the default value of default_statistics_target from 10 to 100,
and its maximum value from 1000 to 10000. ALTER TABLE SET STATISTICSsimilarly now allows a value up to 10000. Per discussion.
1 parentb69bde7 commit65e3ea7

File tree

9 files changed

+27
-27
lines changed

9 files changed

+27
-27
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.199 2008/12/08 15:11:39 mha Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.200 2008/12/13 19:13:43 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2133,7 +2133,7 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
21332133
not had a column-specific target set via <command>ALTER TABLE
21342134
SET STATISTICS</>. Larger values increase the time needed to
21352135
do <command>ANALYZE</>, but might improve the quality of the
2136-
planner's estimates. The default is10. For more information
2136+
planner's estimates. The default is100. For more information
21372137
on the use of statistics by the <productname>PostgreSQL</>
21382138
query planner, refer to <xref linkend="planner-stats">.
21392139
</para>

‎doc/src/sgml/perform.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.68 2007/12/28 21:03:31 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.69 2008/12/13 19:13:43 tgl Exp $ -->
22

33
<chapter id="performance-tips">
44
<title>Performance Tips</title>
@@ -562,7 +562,7 @@ SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'ro
562562
column-by-column basis using the <command>ALTER TABLE SET STATISTICS</>
563563
command, or globally by setting the
564564
<xref linkend="guc-default-statistics-target"> configuration variable.
565-
The default limit is presently10 entries. Raising the limit
565+
The default limit is presently100 entries. Raising the limit
566566
might allow more accurate planner estimates to be made, particularly for
567567
columns with irregular data distributions, at the price of consuming
568568
more space in <structname>pg_statistic</structname> and slightly more

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.101 2008/11/14 10:22:45 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.102 2008/12/13 19:13:44 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -142,7 +142,7 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
142142
This form
143143
sets the per-column statistics-gathering target for subsequent
144144
<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
145-
The target can be set in the range 0 to1000; alternatively, set it
145+
The target can be set in the range 0 to10000; alternatively, set it
146146
to -1 to revert to using the system default statistics
147147
target (<xref linkend="guc-default-statistics-target">).
148148
For more information on the use of statistics by the

‎doc/src/sgml/ref/analyze.sgml

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.24 2008/11/14 10:22:45 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.25 2008/12/13 19:13:44 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -132,10 +132,10 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ ( <re
132132
will change slightly each time <command>ANALYZE</command> is run,
133133
even if the actual table contents did not change. This might result
134134
in small changes in the planner's estimated costs shown by
135-
<xref linkend="sql-explain" endterm="sql-explain-title">. In rare situations, this
136-
non-determinism will cause thequery optimizer to choose a
137-
differentqueryplan between runs of <command>ANALYZE</command>. To
138-
avoid this, raise the amount of statistics collected by
135+
<xref linkend="sql-explain" endterm="sql-explain-title">.
136+
In rare situations, thisnon-determinism will cause theplanner's
137+
choices ofqueryplans to change after <command>ANALYZE</command> is run.
138+
Toavoid this, raise the amount of statistics collected by
139139
<command>ANALYZE</command>, as described below.
140140
</para>
141141

@@ -148,7 +148,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ ( <re
148148
endterm="sql-altertable-title">). The target value sets the
149149
maximum number of entries in the most-common-value list and the
150150
maximum number of bins in the histogram. The default target value
151-
is10, but this can be adjusted up or down to trade off accuracy of
151+
is100, but this can be adjusted up or down to trade off accuracy of
152152
planner estimates against the time taken for
153153
<command>ANALYZE</command> and the amount of space occupied in
154154
<literal>pg_statistic</literal>. In particular, setting the

‎src/backend/commands/analyze.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.128 2008/11/10 00:49:37 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.129 2008/12/13 19:13:44 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -67,7 +67,7 @@ typedef struct AnlIndexData
6767

6868

6969
/* Default statistics target (GUC parameter) */
70-
intdefault_statistics_target=10;
70+
intdefault_statistics_target=100;
7171

7272
/* A few variables that don't seem worth passing around as parameters */
7373
staticintelevel=-1;
@@ -1531,10 +1531,10 @@ std_typanalyze(VacAttrStats *stats)
15311531
* error in bin size f, and error probability gamma, the minimum
15321532
* random sample size is
15331533
*r = 4 * k * ln(2*n/gamma) / f^2
1534-
* Taking f = 0.5, gamma = 0.01, n =1 million rows, we obtain
1534+
* Taking f = 0.5, gamma = 0.01, n =10^6 rows, we obtain
15351535
*r = 305.82 * k
15361536
* Note that because of the log function, the dependence on n is
1537-
* quite weak; even at n =1 billion, a 300*k sample gives <= 0.59
1537+
* quite weak; even at n =10^12, a 300*k sample gives <= 0.66
15381538
* bin size error with probability 0.99. So there's no real need to
15391539
* scale for n, which is a good thing because we don't necessarily
15401540
* know it at this point.

‎src/backend/commands/tablecmds.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.272 2008/12/06 23:22:46 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.273 2008/12/13 19:13:44 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3942,9 +3942,9 @@ ATExecSetStatistics(Relation rel, const char *colName, Node *newValue)
39423942
errmsg("statistics target %d is too low",
39433943
newtarget)));
39443944
}
3945-
elseif (newtarget>1000)
3945+
elseif (newtarget>10000)
39463946
{
3947-
newtarget=1000;
3947+
newtarget=10000;
39483948
ereport(WARNING,
39493949
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
39503950
errmsg("lowering statistics target to %d",

‎src/backend/tsearch/ts_typanalyze.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/tsearch/ts_typanalyze.c,v 1.3 2008/11/27 21:17:39 heikki Exp $
10+
* $PostgreSQL: pgsql/src/backend/tsearch/ts_typanalyze.c,v 1.4 2008/12/13 19:13:44 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -63,7 +63,7 @@ ts_typanalyze(PG_FUNCTION_ARGS)
6363
attr->attstattarget=default_statistics_target;
6464

6565
stats->compute_stats=compute_tsvector_stats;
66-
/* see comment about the choice of minrowsfromanalyze.c */
66+
/* see comment about the choice of minrowsin commands/analyze.c */
6767
stats->minrows=300*attr->attstattarget;
6868

6969
PG_RETURN_BOOL(true);
@@ -105,8 +105,8 @@ ts_typanalyze(PG_FUNCTION_ARGS)
105105
*is no more than a few times w.
106106
*
107107
*We use a hashtable for the D structure and a bucket width of
108-
*statistic_target * 100, where 100 is an arbitrarily chosen constant, meant
109-
*to approximate the number of lexemes in a single tsvector.
108+
*statistics_target * 100, where 100 is an arbitrarily chosen constant,
109+
*meantto approximate the number of lexemes in a single tsvector.
110110
*/
111111
staticvoid
112112
compute_tsvector_stats(VacAttrStats*stats,
@@ -130,7 +130,7 @@ compute_tsvector_stats(VacAttrStats *stats,
130130
LexemeHashKeyhash_key;
131131
TrackItem*item;
132132

133-
/* We wantstatistic_target * 100 lexemes in the MCELEM array */
133+
/* We wantstatistics_target * 100 lexemes in the MCELEM array */
134134
num_mcelem=stats->attr->attstattarget*100;
135135

136136
/*

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

Lines changed: 2 additions & 2 deletions
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.482 2008/12/02 02:00:32 alvherre Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.483 2008/12/13 19:13:44 tgl Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -1245,7 +1245,7 @@ static struct config_int ConfigureNamesInt[] =
12451245
"column-specific target set via ALTER TABLE SET STATISTICS.")
12461246
},
12471247
&default_statistics_target,
1248-
10,1,1000,NULL,NULL
1248+
100,1,10000,NULL,NULL
12491249
},
12501250
{
12511251
{"from_collapse_limit",PGC_USERSET,QUERY_TUNING_OTHER,

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,7 @@
210210

211211
# - Other Planner Options -
212212

213-
#default_statistics_target =10# range 1-1000
213+
#default_statistics_target =100# range 1-10000
214214
#constraint_exclusion = off
215215
#cursor_tuple_fraction = 0.1# range 0.0-1.0
216216
#from_collapse_limit = 8

0 commit comments

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