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

Commitb4df57f

Browse files
committed
Improve match_special_index_operator() to recognize that LIKE with an
exact-match pattern (no wildcard) can be index-optimized in some cases where aprefix-match pattern cannot; specifically, since the required index clause issimple equality, it works for regular text/varchar indexes even when thelocale is not C. I'm not sure how often this case really comes up, but sinceit requires hardly any additional work to handle it, we might as well get itright. Motivated by a discussion on the JDBC list.
1 parentdcf3902 commitb4df57f

File tree

1 file changed

+29
-13
lines changed

1 file changed

+29
-13
lines changed

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

Lines changed: 29 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.237 2009/03/05 23:06:45 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.238 2009/03/11 03:32:22 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -2138,6 +2138,7 @@ match_special_index_operator(Expr *clause, Oid opfamily,
21382138
Const*patt;
21392139
Const*prefix=NULL;
21402140
Const*rest=NULL;
2141+
Pattern_Prefix_Statuspstatus=Pattern_Prefix_None;
21412142

21422143
/*
21432144
* Currently, all known special operators require the indexkey on the
@@ -2163,37 +2164,42 @@ match_special_index_operator(Expr *clause, Oid opfamily,
21632164
caseOID_BPCHAR_LIKE_OP:
21642165
caseOID_NAME_LIKE_OP:
21652166
/* the right-hand const is type text for all of these */
2166-
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Like,
2167-
&prefix,&rest)!=Pattern_Prefix_None;
2167+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Like,
2168+
&prefix,&rest);
2169+
isIndexable= (pstatus!=Pattern_Prefix_None);
21682170
break;
21692171

21702172
caseOID_BYTEA_LIKE_OP:
2171-
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Like,
2172-
&prefix,&rest)!=Pattern_Prefix_None;
2173+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Like,
2174+
&prefix,&rest);
2175+
isIndexable= (pstatus!=Pattern_Prefix_None);
21732176
break;
21742177

21752178
caseOID_TEXT_ICLIKE_OP:
21762179
caseOID_BPCHAR_ICLIKE_OP:
21772180
caseOID_NAME_ICLIKE_OP:
21782181
/* the right-hand const is type text for all of these */
2179-
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Like_IC,
2180-
&prefix,&rest)!=Pattern_Prefix_None;
2182+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Like_IC,
2183+
&prefix,&rest);
2184+
isIndexable= (pstatus!=Pattern_Prefix_None);
21812185
break;
21822186

21832187
caseOID_TEXT_REGEXEQ_OP:
21842188
caseOID_BPCHAR_REGEXEQ_OP:
21852189
caseOID_NAME_REGEXEQ_OP:
21862190
/* the right-hand const is type text for all of these */
2187-
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Regex,
2188-
&prefix,&rest)!=Pattern_Prefix_None;
2191+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Regex,
2192+
&prefix,&rest);
2193+
isIndexable= (pstatus!=Pattern_Prefix_None);
21892194
break;
21902195

21912196
caseOID_TEXT_ICREGEXEQ_OP:
21922197
caseOID_BPCHAR_ICREGEXEQ_OP:
21932198
caseOID_NAME_ICREGEXEQ_OP:
21942199
/* the right-hand const is type text for all of these */
2195-
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Regex_IC,
2196-
&prefix,&rest)!=Pattern_Prefix_None;
2200+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Regex_IC,
2201+
&prefix,&rest);
2202+
isIndexable= (pstatus!=Pattern_Prefix_None);
21972203
break;
21982204

21992205
caseOID_INET_SUB_OP:
@@ -2217,9 +2223,17 @@ match_special_index_operator(Expr *clause, Oid opfamily,
22172223
* want to apply. (A hash index, for example, will not support ">=".)
22182224
* Currently, only btree supports the operators we need.
22192225
*
2226+
* Note: actually, in the Pattern_Prefix_Exact case, we only need "="
2227+
* so a hash index would work. Currently it doesn't seem worth checking
2228+
* for that, however.
2229+
*
22202230
* We insist on the opfamily being the specific one we expect, else we'd
22212231
* do the wrong thing if someone were to make a reverse-sort opfamily with
22222232
* the same operators.
2233+
*
2234+
* The non-pattern opclasses will not sort the way we need in most non-C
2235+
* locales. We can use such an index anyway for an exact match (simple
2236+
* equality), but not for prefix-match cases.
22232237
*/
22242238
switch (expr_op)
22252239
{
@@ -2229,7 +2243,8 @@ match_special_index_operator(Expr *clause, Oid opfamily,
22292243
caseOID_TEXT_ICREGEXEQ_OP:
22302244
isIndexable=
22312245
(opfamily==TEXT_PATTERN_BTREE_FAM_OID)||
2232-
(opfamily==TEXT_BTREE_FAM_OID&&lc_collate_is_c());
2246+
(opfamily==TEXT_BTREE_FAM_OID&&
2247+
(pstatus==Pattern_Prefix_Exact||lc_collate_is_c()));
22332248
break;
22342249

22352250
caseOID_BPCHAR_LIKE_OP:
@@ -2238,7 +2253,8 @@ match_special_index_operator(Expr *clause, Oid opfamily,
22382253
caseOID_BPCHAR_ICREGEXEQ_OP:
22392254
isIndexable=
22402255
(opfamily==BPCHAR_PATTERN_BTREE_FAM_OID)||
2241-
(opfamily==BPCHAR_BTREE_FAM_OID&&lc_collate_is_c());
2256+
(opfamily==BPCHAR_BTREE_FAM_OID&&
2257+
(pstatus==Pattern_Prefix_Exact||lc_collate_is_c()));
22422258
break;
22432259

22442260
caseOID_NAME_LIKE_OP:

0 commit comments

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