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

Commit875b0c6

Browse files
committed
When dealing with multiple grouping columns coming from the same table,
clamp the estimated number of groups to table row count over 10, insteadof table row count; this reflects a heuristic that people probably won'tgroup over a near-unique set of columns, and the knowledge that we don'tcurrently have any way to estimate the correlation of the columns betterthan guessing. This change creates a trivial plan change in one of theregression tests.
1 parent0bf2587 commit875b0c6

File tree

2 files changed

+27
-10
lines changed

2 files changed

+27
-10
lines changed

‎src/backend/utils/adt/selfuncs.c‎

Lines changed: 25 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.169 2004/12/31 22:01:22 pgsql Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.170 2005/01/28 20:34:25 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1937,10 +1937,13 @@ add_unique_group_var(Query *root, List *varinfos,
19371937
*if we considered ones of the same rel, we'd be double-counting the
19381938
*restriction selectivity of the equality in the next step.
19391939
*3.For Vars within a single source rel, we multiply together the numbers
1940-
*of values, clamp to the number of rows in the rel, and then multiply
1941-
*by the selectivity of the restriction clauses for that rel. The
1942-
*initial product is probably too high (it's the worst case) but since
1943-
*we can clamp to the rel's rows it won't be hugely bad.Multiplying
1940+
*of values, clamp to the number of rows in the rel (divided by 10 if
1941+
*more than one Var), and then multiply by the selectivity of the
1942+
*restriction clauses for that rel. When there's more than one Var,
1943+
*the initial product is probably too high (it's the worst case) but
1944+
*clamping to a fraction of the rel's rows seems to be a helpful
1945+
*heuristic for not letting the estimate get out of hand. (The factor
1946+
*of 10 is derived from pre-Postgres-7.4 practice.) Multiplying
19441947
*by the restriction selectivity is effectively assuming that the
19451948
*restriction clauses are independent of the grouping, which is a crummy
19461949
*assumption, but it's hard to do better.
@@ -2040,6 +2043,7 @@ estimate_num_groups(Query *root, List *groupExprs, double input_rows)
20402043
GroupVarInfo*varinfo1= (GroupVarInfo*)linitial(varinfos);
20412044
RelOptInfo*rel=varinfo1->rel;
20422045
doublereldistinct=varinfo1->ndistinct;
2046+
intrelvarcount=1;
20432047
List*newvarinfos=NIL;
20442048

20452049
/*
@@ -2051,7 +2055,10 @@ estimate_num_groups(Query *root, List *groupExprs, double input_rows)
20512055
GroupVarInfo*varinfo2= (GroupVarInfo*)lfirst(l);
20522056

20532057
if (varinfo2->rel==varinfo1->rel)
2058+
{
20542059
reldistinct *=varinfo2->ndistinct;
2060+
relvarcount++;
2061+
}
20552062
else
20562063
{
20572064
/* not time to process varinfo2 yet */
@@ -2066,10 +2073,20 @@ estimate_num_groups(Query *root, List *groupExprs, double input_rows)
20662073
if (rel->tuples>0)
20672074
{
20682075
/*
2069-
* Clamp to size of rel, multiply by restriction selectivity.
2076+
* Clamp to size of rel, or size of rel / 10 if multiple Vars.
2077+
* The fudge factor is because the Vars are probably correlated
2078+
* but we don't know by how much.
2079+
*/
2080+
doubleclamp=rel->tuples;
2081+
2082+
if (relvarcount>1)
2083+
clamp *=0.1;
2084+
if (reldistinct>clamp)
2085+
reldistinct=clamp;
2086+
2087+
/*
2088+
* Multiply by restriction selectivity.
20702089
*/
2071-
if (reldistinct>rel->tuples)
2072-
reldistinct=rel->tuples;
20732090
reldistinct *=rel->rows /rel->tuples;
20742091

20752092
/*

‎src/test/regress/expected/subselect.out‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -134,11 +134,11 @@ SELECT '' AS five, f1 AS "Correlated Field"
134134
WHERE f3 IS NOT NULL);
135135
five | Correlated Field
136136
------+------------------
137-
| 2
138137
| 3
139138
| 1
140-
| 2
141139
| 3
140+
| 2
141+
| 2
142142
(5 rows)
143143

144144
--

0 commit comments

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