Skip to content

Commit 9072592

Browse files
tglsfdctglsfdc
committed
Add ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
Robert Haas
1 parent 527f0ae commit 9072592

File tree

17 files changed

+562
-305
lines changed

17 files changed

+562
-305
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.203 2009/07/29 20:56:17 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.204 2009/08/02 22:14:51 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -886,6 +886,19 @@
886886
</entry>
887887
</row>
888888

889+
<row>
890+
<entry><structfield>attdistinct</structfield></entry>
891+
<entry><type>float4</type></entry>
892+
<entry></entry>
893+
<entry>
894+
<structfield>attdistinct</structfield>, if nonzero, is a user-specified
895+
number-of-distinct-values figure to be used instead of estimating the
896+
number of distinct values during <command>ANALYZE</>. Nonzero values
897+
have the same meanings as for
898+
<link linkend="catalog-pg-statistic"><structname>pg_statistic</></link>.<structfield>stadistinct</>
899+
</entry>
900+
</row>
901+
889902
<row>
890903
<entry><structfield>attlen</structfield></entry>
891904
<entry><type>int2</type></entry>
@@ -4303,9 +4316,10 @@
43034316
<entry></entry>
43044317
<entry>The number of distinct nonnull data values in the column.
43054318
A value greater than zero is the actual number of distinct values.
4306-
A value less than zero is the negative of a fraction of the number
4307-
of rows in the table (for example, a column in which values appear about
4308-
twice on the average could be represented by <structfield>stadistinct</> = -0.5).
4319+
A value less than zero is the negative of a multiplier for the number
4320+
of rows in the table; for example, a column in which values appear about
4321+
twice on the average could be represented by
4322+
<structfield>stadistinct</> = -0.5.
43094323
A zero value means the number of distinct values is unknown
43104324
</entry>
43114325
</row>

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

Lines changed: 29 additions & 3 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.107 2009/07/20 02:42:27 adunstan Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.108 2009/08/02 22:14:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -39,6 +39,7 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
3939
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
4040
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
4141
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
42+
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS DISTINCT <replaceable class="PARAMETER">number</replaceable>
4243
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
4344
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
4445
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
@@ -90,8 +91,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
9091
dropped as well. You will need to say <literal>CASCADE</> if
9192
anything outside the table depends on the column, for example,
9293
foreign key references or views.
93-
If <literal>IF EXISTS</literal> is specified and the column
94-
does not exist, no error is thrown. In this case a notice
94+
If <literal>IF EXISTS</literal> is specified and the column
95+
does not exist, no error is thrown. In this case a notice
9596
is issued instead.
9697
</para>
9798
</listitem>
@@ -156,6 +157,31 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
156157
</listitem>
157158
</varlistentry>
158159

160+
<varlistentry>
161+
<term><literal>SET STATISTICS DISTINCT</literal></term>
162+
<listitem>
163+
<para>
164+
This form overrides the number-of-distinct-values estimate made by
165+
subsequent <xref linkend="sql-analyze" endterm="sql-analyze-title">
166+
operations. When set to a positive value, <command>ANALYZE</> will
167+
assume that the column contains exactly the specified number of distinct
168+
nonnull values. When set to a negative value, which must be greater
169+
than or equal to -1, <command>ANALYZE</> will assume that the number of
170+
distinct nonnull values in the column is linear in the size of the
171+
table; the exact count is to be computed by multiplying the estimated
172+
table size by the absolute value of the given number. For example,
173+
a value of -1 implies that all values in the column are distinct, while
174+
a value of -0.5 implies that each value appears twice on the average.
175+
This can be useful when the size of the table changes over time, since
176+
the multiplication by the number of rows in the table is not performed
177+
until query planning time. Specify a value of 0 to revert to estimating
178+
the number of distinct values normally. For more information on the use
179+
of statistics by the <productname>PostgreSQL</productname> query
180+
planner, refer to <xref linkend="planner-stats">.
181+
</para>
182+
</listitem>
183+
</varlistentry>
184+
159185
<varlistentry>
160186
<indexterm>
161187
<primary>TOAST</primary>

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

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.25 2008/12/13 19:13:44 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.26 2009/08/02 22:14:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -165,6 +165,17 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ ( <re
165165
the target causes a proportional increase in the time and space needed
166166
to do <command>ANALYZE</command>.
167167
</para>
168+
169+
<para>
170+
One of the values estimated by <command>ANALYZE</command> is the number of
171+
distinct values that appear in each column. Because only a subset of the
172+
rows are examined, this estimate can sometimes be quite inaccurate, even
173+
with the largest possible statistics target. If this inaccuracy leads to
174+
bad query plans, a more accurate value can be determined manually and then
175+
installed with
176+
<command>ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT</>
177+
(see <xref linkend="sql-altertable" endterm="sql-altertable-title">).
178+
</para>
168179
</refsect1>
169180

170181
<refsect1>

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/common/tupdesc.c,v 1.127 2009/07/16 06:33:42 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/common/tupdesc.c,v 1.128 2009/08/02 22:14:51 tgl Exp $
1212
*
1313
* NOTES
1414
* some of the executor utility code such as "ExecTypeFromTL" should be
@@ -338,6 +338,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
338338
return false;
339339
if (attr1->attstattarget != attr2->attstattarget)
340340
return false;
341+
if (attr1->attdistinct != attr2->attdistinct)
342+
return false;
341343
if (attr1->attlen != attr2->attlen)
342344
return false;
343345
if (attr1->attndims != attr2->attndims)
@@ -465,6 +467,7 @@ TupleDescInitEntry(TupleDesc desc,
465467
MemSet(NameStr(att->attname), 0, NAMEDATALEN);
466468

467469
att->attstattarget = -1;
470+
att->attdistinct = 0;
468471
att->attcacheoff = -1;
469472
att->atttypmod = typmod;
470473

‎src/backend/bootstrap/bootstrap.c

Lines changed: 2 additions & 1 deletion
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.251 2009/07/31 20:26:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/bootstrap/bootstrap.c,v 1.252 2009/08/02 22:14:51 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -757,6 +757,7 @@ DefineAttr(char *name, char *type, int attnum)
757757
}
758758

759759
attrtypes[attnum]->attstattarget = -1;
760+
attrtypes[attnum]->attdistinct = 0;
760761
attrtypes[attnum]->attcacheoff = -1;
761762
attrtypes[attnum]->atttypmod = -1;
762763
attrtypes[attnum]->attislocal = true;

‎src/backend/catalog/heap.c

Lines changed: 10 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.356 2009/07/30 02:45:36 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.357 2009/08/02 22:14:52 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -111,37 +111,37 @@ static List *insert_ordered_unique_oid(List *list, Oid datum);
111111
*/
112112

113113
static FormData_pg_attribute a1 = {
114-
0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData),
114+
0, {"ctid"}, TIDOID, 0, 0, sizeof(ItemPointerData),
115115
SelfItemPointerAttributeNumber, 0, -1, -1,
116116
false, 'p', 's', true, false, false, true, 0, {0}
117117
};
118118

119119
static FormData_pg_attribute a2 = {
120-
0, {"oid"}, OIDOID, 0, sizeof(Oid),
120+
0, {"oid"}, OIDOID, 0, 0, sizeof(Oid),
121121
ObjectIdAttributeNumber, 0, -1, -1,
122122
true, 'p', 'i', true, false, false, true, 0, {0}
123123
};
124124

125125
static FormData_pg_attribute a3 = {
126-
0, {"xmin"}, XIDOID, 0, sizeof(TransactionId),
126+
0, {"xmin"}, XIDOID, 0, 0, sizeof(TransactionId),
127127
MinTransactionIdAttributeNumber, 0, -1, -1,
128128
true, 'p', 'i', true, false, false, true, 0, {0}
129129
};
130130

131131
static FormData_pg_attribute a4 = {
132-
0, {"cmin"}, CIDOID, 0, sizeof(CommandId),
132+
0, {"cmin"}, CIDOID, 0, 0, sizeof(CommandId),
133133
MinCommandIdAttributeNumber, 0, -1, -1,
134134
true, 'p', 'i', true, false, false, true, 0, {0}
135135
};
136136

137137
static FormData_pg_attribute a5 = {
138-
0, {"xmax"}, XIDOID, 0, sizeof(TransactionId),
138+
0, {"xmax"}, XIDOID, 0, 0, sizeof(TransactionId),
139139
MaxTransactionIdAttributeNumber, 0, -1, -1,
140140
true, 'p', 'i', true, false, false, true, 0, {0}
141141
};
142142

143143
static FormData_pg_attribute a6 = {
144-
0, {"cmax"}, CIDOID, 0, sizeof(CommandId),
144+
0, {"cmax"}, CIDOID, 0, 0, sizeof(CommandId),
145145
MaxCommandIdAttributeNumber, 0, -1, -1,
146146
true, 'p', 'i', true, false, false, true, 0, {0}
147147
};
@@ -153,7 +153,7 @@ static FormData_pg_attribute a6 = {
153153
* used in SQL.
154154
*/
155155
static FormData_pg_attribute a7 = {
156-
0, {"tableoid"}, OIDOID, 0, sizeof(Oid),
156+
0, {"tableoid"}, OIDOID, 0, 0, sizeof(Oid),
157157
TableOidAttributeNumber, 0, -1, -1,
158158
true, 'p', 'i', true, false, false, true, 0, {0}
159159
};
@@ -501,6 +501,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
501501
values[Anum_pg_attribute_attname - 1] = NameGetDatum(&new_attribute->attname);
502502
values[Anum_pg_attribute_atttypid - 1] = ObjectIdGetDatum(new_attribute->atttypid);
503503
values[Anum_pg_attribute_attstattarget - 1] = Int32GetDatum(new_attribute->attstattarget);
504+
values[Anum_pg_attribute_attdistinct - 1] = Float4GetDatum(new_attribute->attdistinct);
504505
values[Anum_pg_attribute_attlen - 1] = Int16GetDatum(new_attribute->attlen);
505506
values[Anum_pg_attribute_attnum - 1] = Int16GetDatum(new_attribute->attnum);
506507
values[Anum_pg_attribute_attndims - 1] = Int32GetDatum(new_attribute->attndims);
@@ -571,6 +572,7 @@ AddNewAttributeTuples(Oid new_rel_oid,
571572
attr->attrelid = new_rel_oid;
572573
/* Make sure these are OK, too */
573574
attr->attstattarget = -1;
575+
attr->attdistinct = 0;
574576
attr->attcacheoff = -1;
575577

576578
InsertPgAttributeTuple(rel, attr, indstate);

‎src/backend/catalog/index.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.320 2009/07/29 20:56:18 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.321 2009/08/02 22:14:52 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -192,6 +192,7 @@ ConstructTupleDescriptor(Relation heapRelation,
192192
to->attnum = i + 1;
193193

194194
to->attstattarget = -1;
195+
to->attdistinct = 0;
195196
to->attcacheoff = -1;
196197
to->attnotnull = false;
197198
to->atthasdef = false;

‎src/backend/commands/analyze.c

Lines changed: 9 additions & 1 deletion
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.139 2009/06/11 14:48:55 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.140 2009/08/02 22:14:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -425,6 +425,11 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt,
425425
std_fetch_func,
426426
numrows,
427427
totalrows);
428+
429+
/* If attdistinct is set, override with that value */
430+
if (stats->attr->attdistinct != 0)
431+
stats->stadistinct = stats->attr->attdistinct;
432+
428433
MemoryContextResetAndDeleteChildren(col_context);
429434
}
430435

@@ -679,6 +684,9 @@ compute_index_stats(Relation onerel, double totalrows,
679684
ind_fetch_func,
680685
numindexrows,
681686
totalindexrows);
687+
/* If attdistinct is set, override with that value */
688+
if (stats->attr->attdistinct != 0)
689+
stats->stadistinct = stats->attr->attdistinct;
682690
MemoryContextResetAndDeleteChildren(col_context);
683691
}
684692
}

0 commit comments

Comments
 (0)