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

Commitc0f7dcd

Browse files
committed
Fix range-query estimation to not double-exclude NULLs, per gripe from
Ray Ontko 28-June-02. Also, fix prefix_selectivity for NAME lefthandvariables (it was bogusly assuming binary compatibility), and adjustmake_greater_string() to not call pg_mbcliplen() with invalid multibytedata (this last per bug report that I can't find at the moment, but itwas in July '02).
1 parenta3cb874 commitc0f7dcd

File tree

3 files changed

+49
-36
lines changed

3 files changed

+49
-36
lines changed

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

Lines changed: 15 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/optimizer/path/clausesel.c,v 1.51 2002/06/20 20:29:29 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/clausesel.c,v 1.52 2002/10/19 02:56:16 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -102,7 +102,9 @@ restrictlist_selectivity(Query *root,
102102
* see that hisel is the fraction of the range below the high bound, while
103103
* losel is the fraction above the low bound; so hisel can be interpreted
104104
* directly as a 0..1 value but we need to convert losel to 1-losel before
105-
* interpreting it as a value.Then the available range is 1-losel to hisel.)
105+
* interpreting it as a value.Then the available range is 1-losel to hisel.
106+
* However, this calculation double-excludes nulls, so really we need
107+
* hisel + losel + null_frac - 1.)
106108
* If the calculation yields zero or negative, however, we chicken out and
107109
* use a default estimate; that probably means that one or both
108110
* selectivities is a default estimate rather than an actual range value.
@@ -199,6 +201,9 @@ clauselist_selectivity(Query *root,
199201
/* Successfully matched a pair of range clauses */
200202
Selectivitys2=rqlist->hibound+rqlist->lobound-1.0;
201203

204+
/* Adjust for double-exclusion of NULLs */
205+
s2+=nulltestsel(root,IS_NULL,rqlist->var,varRelid);
206+
202207
/*
203208
* A zero or slightly negative s2 should be converted into a
204209
* small positive value; we probably are dealing with a very
@@ -503,12 +508,18 @@ clause_selectivity(Query *root,
503508
elseif (IsA(clause,NullTest))
504509
{
505510
/* Use node specific selectivity calculation function */
506-
s1=nulltestsel(root, (NullTest*)clause,varRelid);
511+
s1=nulltestsel(root,
512+
((NullTest*)clause)->nulltesttype,
513+
((NullTest*)clause)->arg,
514+
varRelid);
507515
}
508516
elseif (IsA(clause,BooleanTest))
509517
{
510518
/* Use node specific selectivity calculation function */
511-
s1=booltestsel(root, (BooleanTest*)clause,varRelid);
519+
s1=booltestsel(root,
520+
((BooleanTest*)clause)->booltesttype,
521+
((BooleanTest*)clause)->arg,
522+
varRelid);
512523
}
513524
elseif (IsA(clause,RelabelType))
514525
{

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

Lines changed: 29 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.118 2002/09/20 03:55:40 momjian Exp $
18+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.119 2002/10/19 02:56:16 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1007,10 +1007,9 @@ icnlikesel(PG_FUNCTION_ARGS)
10071007
*booltestsel- Selectivity of BooleanTest Node.
10081008
*/
10091009
Selectivity
1010-
booltestsel(Query*root,BooleanTest*clause,intvarRelid)
1010+
booltestsel(Query*root,BoolTestTypebooltesttype,Node*arg,intvarRelid)
10111011
{
10121012
Var*var;
1013-
Node*arg;
10141013
Oidrelid;
10151014
HeapTuplestatsTuple;
10161015
Datum*values;
@@ -1019,10 +1018,6 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
10191018
intnnumbers;
10201019
doubleselec;
10211020

1022-
Assert(clause&&IsA(clause,BooleanTest));
1023-
1024-
arg= (Node*)clause->arg;
1025-
10261021
/*
10271022
* Ignore any binary-compatible relabeling (probably unnecessary, but
10281023
* can't hurt)
@@ -1040,7 +1035,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
10401035
* the possibility of a NULL value when using clause_selectivity,
10411036
* and just assume the value is either TRUE or FALSE.
10421037
*/
1043-
switch (clause->booltesttype)
1038+
switch (booltesttype)
10441039
{
10451040
caseIS_UNKNOWN:
10461041
selec=DEFAULT_UNK_SEL;
@@ -1058,7 +1053,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
10581053
break;
10591054
default:
10601055
elog(ERROR,"booltestsel: unexpected booltesttype %d",
1061-
(int)clause->booltesttype);
1056+
(int)booltesttype);
10621057
selec=0.0;/* Keep compiler quiet */
10631058
break;
10641059
}
@@ -1107,7 +1102,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
11071102
*/
11081103
freq_false=1.0-freq_true-freq_null;
11091104

1110-
switch (clause->booltesttype)
1105+
switch (booltesttype)
11111106
{
11121107
caseIS_UNKNOWN:
11131108
/* select only NULL values */
@@ -1135,7 +1130,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
11351130
break;
11361131
default:
11371132
elog(ERROR,"booltestsel: unexpected booltesttype %d",
1138-
(int)clause->booltesttype);
1133+
(int)booltesttype);
11391134
selec=0.0;/* Keep compiler quiet */
11401135
break;
11411136
}
@@ -1151,7 +1146,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
11511146
* Otherwise adjust for null fraction and assume an even split
11521147
* for boolean tests.
11531148
*/
1154-
switch (clause->booltesttype)
1149+
switch (booltesttype)
11551150
{
11561151
caseIS_UNKNOWN:
11571152

@@ -1176,7 +1171,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
11761171
break;
11771172
default:
11781173
elog(ERROR,"booltestsel: unexpected booltesttype %d",
1179-
(int)clause->booltesttype);
1174+
(int)booltesttype);
11801175
selec=0.0;/* Keep compiler quiet */
11811176
break;
11821177
}
@@ -1190,7 +1185,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
11901185
* No VACUUM ANALYZE stats available, so use a default value.
11911186
* (Note: not much point in recursing to clause_selectivity here.)
11921187
*/
1193-
switch (clause->booltesttype)
1188+
switch (booltesttype)
11941189
{
11951190
caseIS_UNKNOWN:
11961191
selec=DEFAULT_UNK_SEL;
@@ -1206,7 +1201,7 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
12061201
break;
12071202
default:
12081203
elog(ERROR,"booltestsel: unexpected booltesttype %d",
1209-
(int)clause->booltesttype);
1204+
(int)booltesttype);
12101205
selec=0.0;/* Keep compiler quiet */
12111206
break;
12121207
}
@@ -1222,19 +1217,16 @@ booltestsel(Query *root, BooleanTest *clause, int varRelid)
12221217
*nulltestsel- Selectivity of NullTest Node.
12231218
*/
12241219
Selectivity
1225-
nulltestsel(Query*root,NullTest*clause,intvarRelid)
1220+
nulltestsel(Query*root,NullTestTypenulltesttype,Node*arg,intvarRelid)
12261221
{
12271222
Var*var;
1228-
Node*arg;
12291223
Oidrelid;
12301224
HeapTuplestatsTuple;
12311225
doubleselec;
12321226
doubledefselec;
12331227
doublefreq_null;
12341228

1235-
Assert(clause&&IsA(clause,NullTest));
1236-
1237-
switch (clause->nulltesttype)
1229+
switch (nulltesttype)
12381230
{
12391231
caseIS_NULL:
12401232
defselec=DEFAULT_UNK_SEL;
@@ -1244,25 +1236,22 @@ nulltestsel(Query *root, NullTest *clause, int varRelid)
12441236
break;
12451237
default:
12461238
elog(ERROR,"nulltestsel: unexpected nulltesttype %d",
1247-
(int)clause->nulltesttype);
1239+
(int)nulltesttype);
12481240
return (Selectivity)0;/* keep compiler quiet */
12491241
}
12501242

1251-
arg= (Node*)clause->arg;
1252-
12531243
/*
12541244
* Ignore any binary-compatible relabeling
12551245
*/
12561246
if (IsA(arg,RelabelType))
12571247
arg= ((RelabelType*)arg)->arg;
12581248

1259-
if (IsA(arg,Var)&&(varRelid==0||varRelid== ((Var*)arg)->varno))
1249+
if (IsA(arg,Var)&&
1250+
(varRelid==0||varRelid== ((Var*)arg)->varno))
12601251
var= (Var*)arg;
12611252
else
12621253
{
1263-
/*
1264-
* punt if non-Var argument
1265-
*/
1254+
/* punt if non-Var argument */
12661255
return (Selectivity)defselec;
12671256
}
12681257

@@ -1282,7 +1271,7 @@ nulltestsel(Query *root, NullTest *clause, int varRelid)
12821271
stats= (Form_pg_statistic)GETSTRUCT(statsTuple);
12831272
freq_null=stats->stanullfrac;
12841273

1285-
switch (clause->nulltesttype)
1274+
switch (nulltesttype)
12861275
{
12871276
caseIS_NULL:
12881277

@@ -1301,7 +1290,7 @@ nulltestsel(Query *root, NullTest *clause, int varRelid)
13011290
break;
13021291
default:
13031292
elog(ERROR,"nulltestsel: unexpected nulltesttype %d",
1304-
(int)clause->nulltesttype);
1293+
(int)nulltesttype);
13051294
return (Selectivity)0;/* keep compiler quiet */
13061295
}
13071296

@@ -2978,6 +2967,10 @@ prefix_selectivity(Query *root, Var *var, Const *prefixcon)
29782967
else
29792968
prefix=DatumGetCString(DirectFunctionCall1(byteaout,prefixcon->constvalue));
29802969

2970+
/* If var is type NAME, must adjust type of comparison constant */
2971+
if (var->vartype==NAMEOID)
2972+
prefixcon=string_to_const(prefix,NAMEOID);
2973+
29812974
cmpargs=makeList2(var,prefixcon);
29822975
/* Assume scalargtsel is appropriate for all supported types */
29832976
prefixsel=DatumGetFloat8(DirectFunctionCall4(scalargtsel,
@@ -3014,6 +3007,9 @@ prefix_selectivity(Query *root, Var *var, Const *prefixcon)
30143007
*/
30153008
prefixsel=topsel+prefixsel-1.0;
30163009

3010+
/* Adjust for double-exclusion of NULLs */
3011+
prefixsel+=nulltestsel(root,IS_NULL, (Node*)var,var->varno);
3012+
30173013
/*
30183014
* A zero or slightly negative prefixsel should be converted into
30193015
* a small positive value; we probably are dealing with a very
@@ -3351,6 +3347,7 @@ make_greater_string(const Const *str_const)
33513347
while (len>0)
33523348
{
33533349
unsignedchar*lastchar= (unsignedchar*) (workstr+len-1);
3350+
unsignedcharsavelastchar=*lastchar;
33543351

33553352
/*
33563353
* Try to generate a larger string by incrementing the last byte.
@@ -3369,6 +3366,9 @@ make_greater_string(const Const *str_const)
33693366
}
33703367
}
33713368

3369+
/* restore last byte so we don't confuse pg_mbcliplen */
3370+
*lastchar=savelastchar;
3371+
33723372
/*
33733373
* Truncate off the last character, which might be more than 1
33743374
* byte, depending on the character encoding.

‎src/include/utils/selfuncs.h‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $Id: selfuncs.h,v 1.8 2002/09/04 20:31:46 momjian Exp $
11+
* $Id: selfuncs.h,v 1.9 2002/10/19 02:56:16 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -66,8 +66,10 @@ extern Datum icregexnejoinsel(PG_FUNCTION_ARGS);
6666
externDatumnlikejoinsel(PG_FUNCTION_ARGS);
6767
externDatumicnlikejoinsel(PG_FUNCTION_ARGS);
6868

69-
externSelectivitybooltestsel(Query*root,BooleanTest*clause,intvarRelid);
70-
externSelectivitynulltestsel(Query*root,NullTest*clause,intvarRelid);
69+
externSelectivitybooltestsel(Query*root,BoolTestTypebooltesttype,
70+
Node*arg,intvarRelid);
71+
externSelectivitynulltestsel(Query*root,NullTestTypenulltesttype,
72+
Node*arg,intvarRelid);
7173

7274
externvoidmergejoinscansel(Query*root,Node*clause,
7375
Selectivity*leftscan,

0 commit comments

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