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

Commitce7565a

Browse files
committed
Instead of having a configure-time DEFAULT_ATTSTATTARGET, store -1 in
attstattarget to indicate 'use the default'. The default is now a GUCvariable default_statistics_target, and so may be changed on the fly. Alongthe way we gain the ability to have pg_dump dump the per-column statisticstarget when it's not the default. Patch by Neil Conway, with some kibitzingfrom Tom Lane.
1 parent8be3cfb commitce7565a

File tree

18 files changed

+125
-60
lines changed

18 files changed

+125
-60
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.49 2002/07/30 05:24:56 tgl Exp $
3+
$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.50 2002/07/31 17:19:49 tgl Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -672,8 +672,9 @@
672672
of statistics accumulated for this column by
673673
<command>ANALYZE</command>.
674674
A zero value indicates that no statistics should be collected.
675-
The exact meaning of positive values is data type-dependent.
676-
For scalar data types, <structfield>attstattarget</structfield>
675+
A negative value says to use the system default statistics target.
676+
The exact meaning of positive values is datatype-dependent.
677+
For scalar datatypes, <structfield>attstattarget</structfield>
677678
is both the target number of <quote>most common values</quote>
678679
to collect, and the target number of histogram bins to create.
679680
</entry>

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.46 2002/07/12 18:43:12 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.47 2002/07/31 17:19:50 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -218,6 +218,8 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
218218
This form
219219
sets the per-column statistics-gathering target for subsequent
220220
<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
221+
The target can be set in the range 0 to 1000; alternatively, set it
222+
to -1 to revert to using the system default statistics target.
221223
</para>
222224
</listitem>
223225
</varlistentry>

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

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.8 2002/04/23 02:07:15 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.9 2002/07/31 17:19:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -157,16 +157,18 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ (<rep
157157
</para>
158158

159159
<para>
160-
The extent of analysis can be controlled by adjusting the per-column
160+
The extent of analysis can be controlled by adjusting the
161+
<literal>default_statistics_target</> parameter variable, or on a
162+
column-by-column basis by setting the per-column
161163
statistics target with <command>ALTER TABLE ALTER COLUMN SET
162164
STATISTICS</command> (see
163165
<xref linkend="sql-altertable" endterm="sql-altertable-title">). The
164166
target value sets the maximum number of entries in the most-common-value
165167
list and the maximum number of bins in the histogram. The default
166168
target value is 10, but this can be adjusted up or down to trade off
167169
accuracy of planner estimates against the time taken for
168-
<command>ANALYZE</command> and the
169-
amount of space occupiedin <literal>pg_statistic</literal>.
170+
<command>ANALYZE</command> and the amount of space occupied
171+
in <literal>pg_statistic</literal>.
170172
In particular, setting the statistics target to zero disables collection of
171173
statistics for that column. It may be useful to do that for columns that
172174
are never used as part of the WHERE, GROUP BY, or ORDER BY clauses of

‎doc/src/sgml/runtime.sgml

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.121 2002/07/13 01:02:14 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.122 2002/07/31 17:19:50 tgl Exp $
33
-->
44

55
<Chapter Id="runtime">
@@ -592,6 +592,19 @@ env PGOPTIONS='-c geqo=off' psql
592592
</listitem>
593593
</varlistentry>
594594

595+
<varlistentry>
596+
<term><varname>DEFAULT_STATISTICS_TARGET</varname> (<type>integer</type>)</term>
597+
<listitem>
598+
<para>
599+
Sets the default statistics target for table columns that have not
600+
had a column-specific target set via <command>ALTER TABLE SET
601+
STATISTICS</>. Larger values increase the time needed to do
602+
<command>ANALYZE</>, but may improve the quality of the planner's
603+
estimates.
604+
</para>
605+
</listitem>
606+
</varlistentry>
607+
595608
<varlistentry>
596609
<term><varname>EFFECTIVE_CACHE_SIZE</varname> (<type>floating point</type>)</term>
597610
<listitem>

‎src/backend/access/common/tupdesc.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/access/common/tupdesc.c,v 1.81 2002/07/20 05:16:56 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/access/common/tupdesc.c,v 1.82 2002/07/31 17:19:51 tgl Exp $
1212
*
1313
* NOTES
1414
* some of the executor utility code such as "ExecTypeFromTL" should be
@@ -375,7 +375,7 @@ TupleDescInitEntry(TupleDesc desc,
375375
else
376376
MemSet(NameStr(att->attname),0,NAMEDATALEN);
377377

378-
att->attstattarget=0;
378+
att->attstattarget=-1;
379379
att->attcacheoff=-1;
380380
att->atttypmod=typmod;
381381

‎src/backend/catalog/genbki.sh

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
#
1111
#
1212
# IDENTIFICATION
13-
# $Header: /cvsroot/pgsql/src/backend/catalog/Attic/genbki.sh,v 1.27 2002/04/27 21:24:33 tgl Exp $
13+
# $Header: /cvsroot/pgsql/src/backend/catalog/Attic/genbki.sh,v 1.28 2002/07/31 17:19:51 tgl Exp $
1414
#
1515
# NOTES
1616
# non-essential whitespace is removed from the generated file.
@@ -126,12 +126,11 @@ for dir in $INCLUDE_DIRS; do
126126
fi
127127
done
128128

129-
# Get INDEX_MAX_KEYSand DEFAULT_ATTSTATTARGETfrom pg_config.h
129+
# Get INDEX_MAX_KEYS from pg_config.h
130130
# (who needs consistency?)
131131
fordirin$INCLUDE_DIRS;do
132132
if [-f"$dir/pg_config.h" ];then
133133
INDEXMAXKEYS=`grep'^#define[ ]*INDEX_MAX_KEYS'$dir/pg_config.h|$AWK'{ print $3 }'`
134-
DEFAULTATTSTATTARGET=`grep'^#define[ ]*DEFAULT_ATTSTATTARGET'$dir/pg_config.h|$AWK'{ print $3 }'`
135134
break
136135
fi
137136
done
@@ -194,7 +193,6 @@ sed -e "s/;[ ]*$//g" \
194193
-e"s/PGUID/1/g" \
195194
-e"s/NAMEDATALEN/$NAMEDATALEN/g" \
196195
-e"s/PGNSP/$PG_CATALOG_NAMESPACE/g" \
197-
-e"s/DEFAULT_ATTSTATTARGET/$DEFAULTATTSTATTARGET/g" \
198196
-e"s/INDEX_MAX_KEYS\*2/$INDEXMAXKEYS2/g" \
199197
-e"s/INDEX_MAX_KEYS\*4/$INDEXMAXKEYS4/g" \
200198
-e"s/INDEX_MAX_KEYS/$INDEXMAXKEYS/g" \

‎src/backend/catalog/heap.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.213 2002/07/2419:11:07 petere Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.214 2002/07/31 17:19:51 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -441,7 +441,7 @@ AddNewAttributeTuples(Oid new_rel_oid,
441441
/* Fill in the correct relation OID */
442442
(*dpp)->attrelid=new_rel_oid;
443443
/* Make sure these are OK, too */
444-
(*dpp)->attstattarget=DEFAULT_ATTSTATTARGET;
444+
(*dpp)->attstattarget=-1;
445445
(*dpp)->attcacheoff=-1;
446446

447447
tup=heap_addheader(Natts_pg_attribute,

‎src/backend/commands/analyze.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/analyze.c,v 1.38 2002/06/20 20:29:26 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/analyze.c,v 1.39 2002/07/31 17:19:51 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -107,6 +107,11 @@ typedef struct
107107
#defineswapInt(a,b)do {int _tmp; _tmp=a; a=b; b=_tmp;} while(0)
108108
#defineswapDatum(a,b)do {Datum _tmp; _tmp=a; a=b; b=_tmp;} while(0)
109109

110+
111+
/* Default statistics target (GUC parameter) */
112+
intdefault_statistics_target=10;
113+
114+
110115
staticintelevel=-1;
111116

112117
staticMemoryContextanl_context=NULL;
@@ -384,7 +389,7 @@ examine_attribute(Relation onerel, int attnum)
384389
VacAttrStats*stats;
385390

386391
/* Don't analyze column if user has specified not to */
387-
if (attr->attstattarget<=0)
392+
if (attr->attstattarget==0)
388393
returnNULL;
389394

390395
/* If column has no "=" operator, we can't do much of anything */
@@ -425,6 +430,10 @@ examine_attribute(Relation onerel, int attnum)
425430
stats->eqopr=eqopr;
426431
stats->eqfunc=eqfunc;
427432

433+
/* If the attstattarget column is negative, use the default value */
434+
if (stats->attr->attstattarget<0)
435+
stats->attr->attstattarget=default_statistics_target;
436+
428437
/* Is there a "<" operator with suitable semantics? */
429438
func_operator=compatible_oper(makeList1(makeString("<")),
430439
attr->atttypid,
@@ -466,14 +475,14 @@ examine_attribute(Relation onerel, int attnum)
466475
* know it at this point.
467476
*--------------------
468477
*/
469-
stats->minrows=300*attr->attstattarget;
478+
stats->minrows=300*stats->attr->attstattarget;
470479
}
471480
else
472481
{
473482
/* Can't do much but the minimal stuff */
474483
stats->algcode=ALG_MINIMAL;
475484
/* Might as well use the same minrows as above */
476-
stats->minrows=300*attr->attstattarget;
485+
stats->minrows=300*stats->attr->attstattarget;
477486
}
478487

479488
returnstats;

‎src/backend/commands/tablecmds.c

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.24 2002/07/20 05:16:57 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.25 2002/07/31 17:19:51 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1668,7 +1668,7 @@ AlterTableAddColumn(Oid myrelid,
16681668
attribute->attrelid=myrelid;
16691669
namestrcpy(&(attribute->attname),colDef->colname);
16701670
attribute->atttypid=HeapTupleGetOid(typeTuple);
1671-
attribute->attstattarget=DEFAULT_ATTSTATTARGET;
1671+
attribute->attstattarget=-1;
16721672
attribute->attlen=tform->typlen;
16731673
attribute->attcacheoff=-1;
16741674
attribute->atttypmod=colDef->typename->typmod;
@@ -2184,12 +2184,18 @@ AlterTableAlterColumnFlags(Oid myrelid,
21842184
newtarget=intVal(flagValue);
21852185

21862186
/*
2187-
* Limit target to sane range (should we raise an error instead?)
2187+
* Limit target toasane range
21882188
*/
2189-
if (newtarget<0)
2190-
newtarget=0;
2189+
if (newtarget<-1)
2190+
{
2191+
elog(ERROR,"ALTER TABLE: statistics target %d is too low",
2192+
newtarget);
2193+
}
21912194
elseif (newtarget>1000)
2195+
{
2196+
elog(WARNING,"ALTER TABLE: lowering statistics target to 1000");
21922197
newtarget=1000;
2198+
}
21932199
}
21942200
elseif (*flagType=='M')
21952201
{

‎src/backend/parser/gram.y

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.351 2002/07/30 16:55:44 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.352 2002/07/31 17:19:51 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -1134,14 +1134,14 @@ AlterTableStmt:
11341134
n->name =$6;
11351135
$$ = (Node *)n;
11361136
}
1137-
/* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STATISTICS <Iconst>*/
1138-
|ALTERTABLErelation_exprALTERopt_columnColIdSETSTATISTICSIconst
1137+
/* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STATISTICS <IntegerOnly>*/
1138+
|ALTERTABLErelation_exprALTERopt_columnColIdSETSTATISTICSIntegerOnly
11391139
{
11401140
AlterTableStmt *n = makeNode(AlterTableStmt);
11411141
n->subtype ='S';
11421142
n->relation =$3;
11431143
n->name =$6;
1144-
n->def = (Node *)makeInteger($9);
1144+
n->def = (Node *)$9;
11451145
$$ = (Node *)n;
11461146
}
11471147
/* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STORAGE <storagemode>*/

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

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
* command, configuration file, and command line options.
66
* See src/backend/utils/misc/README for more information.
77
*
8-
* $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.76 2002/07/30 16:20:03 momjian Exp $
8+
* $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.77 2002/07/31 17:19:52 tgl Exp $
99
*
1010
* Copyright 2000 by PostgreSQL Global Development Group
1111
* Written by Peter Eisentraut <peter_e@gmx.net>.
@@ -26,6 +26,7 @@
2626
#include"catalog/pg_type.h"
2727
#include"commands/async.h"
2828
#include"commands/variable.h"
29+
#include"commands/vacuum.h"
2930
#include"executor/executor.h"
3031
#include"fmgr.h"
3132
#include"libpq/auth.h"
@@ -491,6 +492,10 @@ static struct config_bool
491492
staticstructconfig_int
492493
ConfigureNamesInt[]=
493494
{
495+
{
496+
{"default_statistics_target",PGC_USERSET },&default_statistics_target,
497+
10,1,1000,NULL,NULL
498+
},
494499
{
495500
{"geqo_threshold",PGC_USERSET },&geqo_rels,
496501
DEFAULT_GEQO_RELS,2,INT_MAX,NULL,NULL

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,7 @@
9595
#cpu_index_tuple_cost = 0.001
9696
#cpu_operator_cost = 0.0025
9797

98+
#default_statistics_target = 10# range 1-1000
9899

99100
#
100101
#GEQO Optimizer Parameters

0 commit comments

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