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

Commit9072592

Browse files
committed
Add ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
Robert Haas
1 parent527f0ae commit9072592

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
staticFormData_pg_attributea1= {
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
staticFormData_pg_attributea2= {
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
staticFormData_pg_attributea3= {
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
staticFormData_pg_attributea4= {
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
staticFormData_pg_attributea5= {
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
staticFormData_pg_attributea6= {
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
staticFormData_pg_attributea7= {
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/1114: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)
[8]ページ先頭
©2009-2025 Movatter.jp