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

Commit547bb4a

Browse files
committed
Use a hopefully-more-reliable method of detecting default selectivity
estimates when combining the estimates for a range query. As pointed outby Miquel van Smoorenburg, the existing check for an impossible combinedresult would quite possibly fail to detect one default and one non-defaultinput. It seems better to use the default range query estimate in suchcases. To do so, add a check for an estimate of exactly DEFAULT_INEQ_SEL.This is a bit ugly because it introduces additional coupling betweenclauselist_selectivity and scalarltsel/scalargtsel, but it's not likethere wasn't plenty already...
1 parente438711 commit547bb4a

File tree

3 files changed

+90
-71
lines changed

3 files changed

+90
-71
lines changed

‎src/backend/optimizer/path/clausesel.c

Lines changed: 45 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.70 2004/08/29 05:06:43 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.71 2004/11/09 00:34:38 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -80,9 +80,10 @@ static void addRangeClause(RangeQueryClause **rqlist, Node *clause,
8080
* interpreting it as a value.Then the available range is 1-losel to hisel.
8181
* However, this calculation double-excludes nulls, so really we need
8282
* hisel + losel + null_frac - 1.)
83-
* If the calculation yields zero or negative, however, we chicken out and
84-
* use a default estimate; that probably means that one or both
85-
* selectivities is a default estimate rather than an actual range value.
83+
*
84+
* If either selectivity is exactly DEFAULT_INEQ_SEL, we forget this equation
85+
* and instead use DEFAULT_RANGE_INEQ_SEL. The same applies if the equation
86+
* yields an impossible (negative) result.
8687
*
8788
* A free side-effect is that we can recognize redundant inequalities such
8889
* as "x < 4 AND x < 5"; only the tighter constraint will be counted.
@@ -194,37 +195,51 @@ clauselist_selectivity(Query *root,
194195
if (rqlist->have_lobound&&rqlist->have_hibound)
195196
{
196197
/* Successfully matched a pair of range clauses */
197-
Selectivitys2=rqlist->hibound+rqlist->lobound-1.0;
198-
199-
/* Adjust for double-exclusion of NULLs */
200-
s2+=nulltestsel(root,IS_NULL,rqlist->var,varRelid);
198+
Selectivitys2;
201199

202200
/*
203-
* A zero or slightly negative s2 should be converted into a
204-
* small positive value; we probably are dealing with a very
205-
* tight range and got a bogus result due to roundoff errors.
206-
* However, if s2 is very negative, then we probably have
207-
* default selectivity estimates on one or both sides of the
208-
* range. In that case, insert a not-so-wildly-optimistic
209-
* default estimate.
201+
* Exact equality to the default value probably means the
202+
* selectivity function punted. This is not airtight but
203+
* should be good enough.
210204
*/
211-
if (s2 <=0.0)
205+
if (rqlist->hibound==DEFAULT_INEQ_SEL||
206+
rqlist->lobound==DEFAULT_INEQ_SEL)
212207
{
213-
if (s2<-0.01)
214-
{
215-
/*
216-
* No data available --- use a default estimate that
217-
* is small, but not real small.
218-
*/
219-
s2=0.005;
220-
}
221-
else
208+
s2=DEFAULT_RANGE_INEQ_SEL;
209+
}
210+
else
211+
{
212+
s2=rqlist->hibound+rqlist->lobound-1.0;
213+
214+
/* Adjust for double-exclusion of NULLs */
215+
s2+=nulltestsel(root,IS_NULL,rqlist->var,varRelid);
216+
217+
/*
218+
* A zero or slightly negative s2 should be converted into a
219+
* small positive value; we probably are dealing with a very
220+
* tight range and got a bogus result due to roundoff errors.
221+
* However, if s2 is very negative, then we probably have
222+
* default selectivity estimates on one or both sides of the
223+
* range that we failed to recognize above for some reason.
224+
*/
225+
if (s2 <=0.0)
222226
{
223-
/*
224-
* It's just roundoff error; use a small positive
225-
* value
226-
*/
227-
s2=1.0e-10;
227+
if (s2<-0.01)
228+
{
229+
/*
230+
* No data available --- use a default estimate that
231+
* is small, but not real small.
232+
*/
233+
s2=DEFAULT_RANGE_INEQ_SEL;
234+
}
235+
else
236+
{
237+
/*
238+
* It's just roundoff error; use a small positive
239+
* value
240+
*/
241+
s2=1.0e-10;
242+
}
228243
}
229244
}
230245
/* Merge in the selectivity of the pair of clauses */

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

Lines changed: 1 addition & 40 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.166 2004/09/18 19:39:50 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.167 2004/11/09 00:34:42 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -111,45 +111,6 @@
111111
#include"utils/syscache.h"
112112

113113

114-
/*
115-
* Note: the default selectivity estimates are not chosen entirely at random.
116-
* We want them to be small enough to ensure that indexscans will be used if
117-
* available, for typical table densities of ~100 tuples/page.Thus, for
118-
* example, 0.01 is not quite small enough, since that makes it appear that
119-
* nearly all pages will be hit anyway. Also, since we sometimes estimate
120-
* eqsel as 1/num_distinct, we probably want DEFAULT_NUM_DISTINCT to equal
121-
* 1/DEFAULT_EQ_SEL.
122-
*/
123-
124-
/* default selectivity estimate for equalities such as "A = b" */
125-
#defineDEFAULT_EQ_SEL0.005
126-
127-
/* default selectivity estimate for inequalities such as "A < b" */
128-
#defineDEFAULT_INEQ_SEL (1.0 / 3.0)
129-
130-
/* default selectivity estimate for pattern-match operators such as LIKE */
131-
#defineDEFAULT_MATCH_SEL0.005
132-
133-
/* default number of distinct values in a table */
134-
#defineDEFAULT_NUM_DISTINCT 200
135-
136-
/* default selectivity estimate for boolean and null test nodes */
137-
#defineDEFAULT_UNK_SEL0.005
138-
#defineDEFAULT_NOT_UNK_SEL(1.0 - DEFAULT_UNK_SEL)
139-
140-
/*
141-
* Clamp a computed probability estimate (which may suffer from roundoff or
142-
* estimation errors) to valid range. Argument must be a float variable.
143-
*/
144-
#defineCLAMP_PROBABILITY(p) \
145-
do { \
146-
if (p < 0.0) \
147-
p = 0.0; \
148-
else if (p > 1.0) \
149-
p = 1.0; \
150-
} while (0)
151-
152-
153114
/* Return data from examine_variable and friends */
154115
typedefstruct
155116
{

‎src/include/utils/selfuncs.h

Lines changed: 44 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.19 2004/08/29 05:06:59 momjian Exp $
11+
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.20 2004/11/09 00:34:46 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -19,6 +19,49 @@
1919
#include"nodes/parsenodes.h"
2020

2121

22+
/*
23+
* Note: the default selectivity estimates are not chosen entirely at random.
24+
* We want them to be small enough to ensure that indexscans will be used if
25+
* available, for typical table densities of ~100 tuples/page.Thus, for
26+
* example, 0.01 is not quite small enough, since that makes it appear that
27+
* nearly all pages will be hit anyway. Also, since we sometimes estimate
28+
* eqsel as 1/num_distinct, we probably want DEFAULT_NUM_DISTINCT to equal
29+
* 1/DEFAULT_EQ_SEL.
30+
*/
31+
32+
/* default selectivity estimate for equalities such as "A = b" */
33+
#defineDEFAULT_EQ_SEL0.005
34+
35+
/* default selectivity estimate for inequalities such as "A < b" */
36+
#defineDEFAULT_INEQ_SEL 0.3333333333333333
37+
38+
/* default selectivity estimate for range inequalities "A > b AND A < c" */
39+
#defineDEFAULT_RANGE_INEQ_SEL 0.005
40+
41+
/* default selectivity estimate for pattern-match operators such as LIKE */
42+
#defineDEFAULT_MATCH_SEL0.005
43+
44+
/* default number of distinct values in a table */
45+
#defineDEFAULT_NUM_DISTINCT 200
46+
47+
/* default selectivity estimate for boolean and null test nodes */
48+
#defineDEFAULT_UNK_SEL0.005
49+
#defineDEFAULT_NOT_UNK_SEL(1.0 - DEFAULT_UNK_SEL)
50+
51+
52+
/*
53+
* Clamp a computed probability estimate (which may suffer from roundoff or
54+
* estimation errors) to valid range. Argument must be a float variable.
55+
*/
56+
#defineCLAMP_PROBABILITY(p) \
57+
do { \
58+
if (p < 0.0) \
59+
p = 0.0; \
60+
else if (p > 1.0) \
61+
p = 1.0; \
62+
} while (0)
63+
64+
2265
typedefenum
2366
{
2467
Pattern_Type_Like,Pattern_Type_Like_IC,

0 commit comments

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