🌐 AI搜索 & 代理 主页
Skip to content

Commit ce7565a

Browse files
tglsfdctglsfdc
committed
Instead of having a configure-time DEFAULT_ATTSTATTARGET, store -1 in
attstattarget to indicate 'use the default'. The default is now a GUC variable default_statistics_target, and so may be changed on the fly. Along the way we gain the ability to have pg_dump dump the per-column statistics target when it's not the default. Patch by Neil Conway, with some kibitzing from Tom Lane.
1 parent 8be3cfb commit ce7565a

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 occupied in <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_KEYS and DEFAULT_ATTSTATTARGET from pg_config.h
129+
# Get INDEX_MAX_KEYS from pg_config.h
130130
# (who needs consistency?)
131131
for dir in $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/24 19: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
#define swapInt(a,b) do {int _tmp; _tmp=a; a=b; b=_tmp;} while(0)
108108
#define swapDatum(a,b) do {Datum _tmp; _tmp=a; a=b; b=_tmp;} while(0)
109109

110+
111+
/* Default statistics target (GUC parameter) */
112+
int default_statistics_target = 10;
113+
114+
110115
static int elevel = -1;
111116

112117
static MemoryContext anl_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
return NULL;
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
return stats;

‎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 to a sane 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
else if (newtarget > 1000)
2195+
{
2196+
elog(WARNING, "ALTER TABLE: lowering statistics target to 1000");
21922197
newtarget = 1000;
2198+
}
21932199
}
21942200
else if (*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
* AUTHOR DATE MAJOR 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-
| ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS Iconst
1137+
/* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STATISTICS <IntegerOnly> */
1138+
| ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS IntegerOnly
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> */

0 commit comments

Comments
 (0)