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

Commit022cd0b

Browse files
committed
Use query collation, not column's collation, while examining statistics.
Commit5e09280 changed the planner so that, instead of blindly usingDEFAULT_COLLATION_OID when invoking operators for selectivity estimation,it would use the collation of the column whose statistics we'reconsidering. This was recognized as still being not quite the rightthing, but it seemed like a good incremental improvement. However,shortly thereafter we introduced nondeterministic collations, and thatcreates cases where operators can fail if they're passed the wrongcollation. We don't want planning to fail in cases where the query itselfwould work, so this means that we *must* use the query's collation wheninvoking operators for estimation purposes.The only real problem this creates is in ineq_histogram_selectivity, wherethe binary search might produce a garbage answer if we perform comparisonsusing a different collation than the column's histogram is ordered with.However, when the query's collation is significantly different from thecolumn's default collation, the estimate we previously generated would bepretty irrelevant anyway; so it's not clear that this will result innoticeably worse estimates in practice. (A follow-on patch will improvethis situation in HEAD, but it seems too invasive for back-patch.)The patch requires changing the signatures of mcv_selectivity and alliedfunctions, which are exported and very possibly are used by extensions.In HEAD, I just did that, but an API/ABI break of this sort isn'tacceptable in stable branches. Therefore, in v12 the patch introduces"mcv_selectivity_ext" and so on, with signatures matching HEAD, and makesthe old functions into wrappers that assume DEFAULT_COLLATION_OID shouldbe used. That does not match the prior behavior, but it should avoid riskof failure in most cases. (In practice, I think most extension datatypesaren't collation-aware, so the change probably doesn't matter to them.)Per report from James Lucas. Back-patch to v12 where the problem wasintroduced.Discussion:https://postgr.es/m/CAAFmbbOvfi=wMM=3qRsPunBSLb8BFREno2oOzSBS=mzfLPKABw@mail.gmail.com
1 parent75f1479 commit022cd0b

File tree

4 files changed

+167
-90
lines changed

4 files changed

+167
-90
lines changed

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

Lines changed: 30 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,9 @@ static Pattern_Prefix_Status pattern_fixed_prefix(Const *patt,
9090
Selectivity*rest_selec);
9191
staticSelectivityprefix_selectivity(PlannerInfo*root,
9292
VariableStatData*vardata,
93-
Oidvartype,Oidopfamily,Const*prefixcon);
93+
Oidvartype,Oidopfamily,
94+
Oidcollation,
95+
Const*prefixcon);
9496
staticSelectivitylike_selectivity(constchar*patt,intpattlen,
9597
boolcase_insensitive);
9698
staticSelectivityregex_selectivity(constchar*patt,intpattlen,
@@ -586,8 +588,8 @@ patternsel_common(PlannerInfo *root,
586588

587589
if (eqopr==InvalidOid)
588590
elog(ERROR,"no = operator for opfamily %u",opfamily);
589-
result=var_eq_const(&vardata,eqopr,prefix->constvalue,
590-
false, true, false);
591+
result=var_eq_const_ext(&vardata,eqopr,collation,
592+
prefix->constvalue, false, true, false);
591593
}
592594
else
593595
{
@@ -618,8 +620,9 @@ patternsel_common(PlannerInfo *root,
618620
opfuncid=get_opcode(oprid);
619621
fmgr_info(opfuncid,&opproc);
620622

621-
selec=histogram_selectivity(&vardata,&opproc,constval, true,
622-
10,1,&hist_size);
623+
selec=histogram_selectivity_ext(&vardata,&opproc,collation,
624+
constval, true,
625+
10,1,&hist_size);
623626

624627
/* If not at least 100 entries, use the heuristic method */
625628
if (hist_size<100)
@@ -629,7 +632,7 @@ patternsel_common(PlannerInfo *root,
629632

630633
if (pstatus==Pattern_Prefix_Partial)
631634
prefixsel=prefix_selectivity(root,&vardata,vartype,
632-
opfamily,prefix);
635+
opfamily,collation,prefix);
633636
else
634637
prefixsel=1.0;
635638
heursel=prefixsel*rest_selec;
@@ -661,8 +664,9 @@ patternsel_common(PlannerInfo *root,
661664
* directly to the result selectivity. Also add up the total fraction
662665
* represented by MCV entries.
663666
*/
664-
mcv_selec=mcv_selectivity(&vardata,&opproc,constval, true,
665-
&sumcommon);
667+
mcv_selec=mcv_selectivity_ext(&vardata,&opproc,collation,
668+
constval, true,
669+
&sumcommon);
666670

667671
/*
668672
* Now merge the results from the MCV and histogram calculations,
@@ -1170,12 +1174,13 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, Oid collation,
11701174
*/
11711175
staticSelectivity
11721176
prefix_selectivity(PlannerInfo*root,VariableStatData*vardata,
1173-
Oidvartype,Oidopfamily,Const*prefixcon)
1177+
Oidvartype,Oidopfamily,
1178+
Oidcollation,
1179+
Const*prefixcon)
11741180
{
11751181
Selectivityprefixsel;
11761182
Oidcmpopr;
11771183
FmgrInfoopproc;
1178-
AttStatsSlotsslot;
11791184
Const*greaterstrcon;
11801185
Selectivityeq_sel;
11811186

@@ -1185,44 +1190,36 @@ prefix_selectivity(PlannerInfo *root, VariableStatData *vardata,
11851190
elog(ERROR,"no >= operator for opfamily %u",opfamily);
11861191
fmgr_info(get_opcode(cmpopr),&opproc);
11871192

1188-
prefixsel=ineq_histogram_selectivity(root,vardata,
1189-
&opproc, true, true,
1190-
prefixcon->constvalue,
1191-
prefixcon->consttype);
1193+
prefixsel=ineq_histogram_selectivity_ext(root,vardata,
1194+
&opproc, true, true,
1195+
collation,
1196+
prefixcon->constvalue,
1197+
prefixcon->consttype);
11921198

11931199
if (prefixsel<0.0)
11941200
{
11951201
/* No histogram is present ... return a suitable default estimate */
11961202
returnDEFAULT_MATCH_SEL;
11971203
}
11981204

1199-
/*-------
1200-
* If we can create a string larger than the prefix, say
1201-
* "x < greaterstr". We try to generate the string referencing the
1202-
* collation of the var's statistics, but if that's not available,
1203-
* use DEFAULT_COLLATION_OID.
1204-
*-------
1205+
/*
1206+
* If we can create a string larger than the prefix, say "x < greaterstr".
12051207
*/
1206-
if (HeapTupleIsValid(vardata->statsTuple)&&
1207-
get_attstatsslot(&sslot,vardata->statsTuple,
1208-
STATISTIC_KIND_HISTOGRAM,InvalidOid,0))
1209-
/* sslot.stacoll is set up */ ;
1210-
else
1211-
sslot.stacoll=DEFAULT_COLLATION_OID;
12121208
cmpopr=get_opfamily_member(opfamily,vartype,vartype,
12131209
BTLessStrategyNumber);
12141210
if (cmpopr==InvalidOid)
12151211
elog(ERROR,"no < operator for opfamily %u",opfamily);
12161212
fmgr_info(get_opcode(cmpopr),&opproc);
1217-
greaterstrcon=make_greater_string(prefixcon,&opproc,sslot.stacoll);
1213+
greaterstrcon=make_greater_string(prefixcon,&opproc,collation);
12181214
if (greaterstrcon)
12191215
{
12201216
Selectivitytopsel;
12211217

1222-
topsel=ineq_histogram_selectivity(root,vardata,
1223-
&opproc, false, false,
1224-
greaterstrcon->constvalue,
1225-
greaterstrcon->consttype);
1218+
topsel=ineq_histogram_selectivity_ext(root,vardata,
1219+
&opproc, false, false,
1220+
collation,
1221+
greaterstrcon->constvalue,
1222+
greaterstrcon->consttype);
12261223

12271224
/* ineq_histogram_selectivity worked before, it shouldn't fail now */
12281225
Assert(topsel >=0.0);
@@ -1253,8 +1250,8 @@ prefix_selectivity(PlannerInfo *root, VariableStatData *vardata,
12531250
BTEqualStrategyNumber);
12541251
if (cmpopr==InvalidOid)
12551252
elog(ERROR,"no = operator for opfamily %u",opfamily);
1256-
eq_sel=var_eq_const(vardata,cmpopr,prefixcon->constvalue,
1257-
false, true, false);
1253+
eq_sel=var_eq_const_ext(vardata,cmpopr,collation,prefixcon->constvalue,
1254+
false, true, false);
12581255

12591256
prefixsel=Max(prefixsel,eq_sel);
12601257

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -137,8 +137,9 @@ networksel(PG_FUNCTION_ARGS)
137137
* by MCV entries.
138138
*/
139139
fmgr_info(get_opcode(operator),&proc);
140-
mcv_selec=mcv_selectivity(&vardata,&proc,constvalue,varonleft,
141-
&sumcommon);
140+
mcv_selec=mcv_selectivity_ext(&vardata,&proc,InvalidOid,
141+
constvalue,varonleft,
142+
&sumcommon);
142143

143144
/*
144145
* If we have a histogram, use it to estimate the proportion of the

0 commit comments

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