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

Commit7b8a63c

Browse files
committed
Alter the xxx_pattern_ops opclasses to use the regular equality operator of
the associated datatype as their equality member. This means that theseopclasses can now support plain equality comparisons along with LIKE tests,thus avoiding the need for an extra index in some applications. Thisoptimization was not possible when the pattern opclasses were first introduced,because we didn't insist that text equality meant bitwise equality; but wedo now, so there is no semantic difference between regular and patternequality operators.I removed the name_pattern_ops opclass altogether, since it's really useless:name's regular comparisons are just strcmp() and are unlikely to becomesomething different. Instead teach indxpath.c that btree name_ops can beused for LIKE whether or not the locale is C. This might lead to a usefulspeedup in LIKE queries on the system catalogs in non-C locales.The ~=~ and ~<>~ operators are gone altogether. (It would have been nice tokeep them for backward compatibility's sake, but since the pg_amop structuredoesn't allow multiple equality operators per opclass, there's no way.)A not-immediately-obvious incompatibility is that the sort order withinbpchar_pattern_ops indexes changes --- it had been identical to plainstrcmp, but is now trailing-blank-insensitive. This will impactin-place upgrades, if those ever happen.Per discussions a couple months ago.
1 parenta3d9a24 commit7b8a63c

File tree

15 files changed

+168
-213
lines changed

15 files changed

+168
-213
lines changed

‎doc/src/sgml/indices.sgml

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.72 2007/11/13 23:36:26 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.73 2008/05/27 00:13:08 tgl Exp $ -->
22

33
<chapter id="indexes">
44
<title id="indexes-title">Indexes</title>
@@ -906,11 +906,10 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
906906
<listitem>
907907
<para>
908908
The operator classes <literal>text_pattern_ops</literal>,
909-
<literal>varchar_pattern_ops</literal>,
910-
<literal>bpchar_pattern_ops</literal>, and
911-
<literal>name_pattern_ops</literal> support B-tree indexes on
912-
the types <type>text</type>, <type>varchar</type>,
913-
<type>char</type>, and <type>name</type>, respectively. The
909+
<literal>varchar_pattern_ops</literal>, and
910+
<literal>bpchar_pattern_ops</literal> support B-tree indexes on
911+
the types <type>text</type>, <type>varchar</type>, and
912+
<type>char</type> respectively. The
914913
difference from the default operator classes is that the values
915914
are compared strictly character by character rather than
916915
according to the locale-specific collation rules. This makes
@@ -923,10 +922,12 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
923922
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
924923
</programlisting>
925924
Note that you should also create an index with the default operator
926-
class if you want queries involving ordinary comparisons to use an
927-
index. Such queries cannot use the
925+
class if you want queries involving ordinary <literal>&lt;</>,
926+
<literal>&lt;=</>, <literal>&gt;</>, or <literal>&gt;=</> comparisons
927+
to use an index. Such queries cannot use the
928928
<literal><replaceable>xxx</replaceable>_pattern_ops</literal>
929-
operator classes. It is allowed to create multiple
929+
operator classes. (Ordinary equality comparisons can use these
930+
operator classes, however.) It is allowed to create multiple
930931
indexes on the same column with different operator classes.
931932
If you do use the C locale, you do not need the
932933
<literal><replaceable>xxx</replaceable>_pattern_ops</literal>

‎src/backend/access/nbtree/nbtcompare.c

Lines changed: 1 addition & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.55 2008/01/01 19:45:46 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.56 2008/05/27 00:13:08 tgl Exp $
1212
*
1313
* NOTES
1414
*
@@ -237,12 +237,3 @@ btnamecmp(PG_FUNCTION_ARGS)
237237

238238
PG_RETURN_INT32(strncmp(NameStr(*a),NameStr(*b),NAMEDATALEN));
239239
}
240-
241-
Datum
242-
btname_pattern_cmp(PG_FUNCTION_ARGS)
243-
{
244-
Namea=PG_GETARG_NAME(0);
245-
Nameb=PG_GETARG_NAME(1);
246-
247-
PG_RETURN_INT32(memcmp(NameStr(*a),NameStr(*b),NAMEDATALEN));
248-
}

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

Lines changed: 3 additions & 5 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.230 2008/05/16 16:31:01 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.231 2008/05/27 00:13:09 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -2178,9 +2178,8 @@ match_special_index_operator(Expr *clause, Oid opfamily,
21782178
caseOID_NAME_ICLIKE_OP:
21792179
caseOID_NAME_REGEXEQ_OP:
21802180
caseOID_NAME_ICREGEXEQ_OP:
2181-
isIndexable=
2182-
(opfamily==NAME_PATTERN_BTREE_FAM_OID)||
2183-
(opfamily==NAME_BTREE_FAM_OID&&lc_collate_is_c());
2181+
/* name uses locale-insensitive sorting */
2182+
isIndexable= (opfamily==NAME_BTREE_FAM_OID);
21842183
break;
21852184

21862185
caseOID_BYTEA_LIKE_OP:
@@ -2700,7 +2699,6 @@ prefix_quals(Node *leftop, Oid opfamily,
27002699
break;
27012700

27022701
caseNAME_BTREE_FAM_OID:
2703-
caseNAME_PATTERN_BTREE_FAM_OID:
27042702
datatype=NAMEOID;
27052703
break;
27062704

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

Lines changed: 1 addition & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/name.c,v 1.61 2008/01/01 19:45:52 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/name.c,v 1.62 2008/05/27 00:13:09 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -181,65 +181,6 @@ namege(PG_FUNCTION_ARGS)
181181
}
182182

183183

184-
/*
185-
* comparison routines for LIKE indexing support
186-
*/
187-
188-
Datum
189-
name_pattern_eq(PG_FUNCTION_ARGS)
190-
{
191-
Namearg1=PG_GETARG_NAME(0);
192-
Namearg2=PG_GETARG_NAME(1);
193-
194-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN)==0);
195-
}
196-
197-
Datum
198-
name_pattern_ne(PG_FUNCTION_ARGS)
199-
{
200-
Namearg1=PG_GETARG_NAME(0);
201-
Namearg2=PG_GETARG_NAME(1);
202-
203-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN)!=0);
204-
}
205-
206-
Datum
207-
name_pattern_lt(PG_FUNCTION_ARGS)
208-
{
209-
Namearg1=PG_GETARG_NAME(0);
210-
Namearg2=PG_GETARG_NAME(1);
211-
212-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN)<0);
213-
}
214-
215-
Datum
216-
name_pattern_le(PG_FUNCTION_ARGS)
217-
{
218-
Namearg1=PG_GETARG_NAME(0);
219-
Namearg2=PG_GETARG_NAME(1);
220-
221-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN) <=0);
222-
}
223-
224-
Datum
225-
name_pattern_gt(PG_FUNCTION_ARGS)
226-
{
227-
Namearg1=PG_GETARG_NAME(0);
228-
Namearg2=PG_GETARG_NAME(1);
229-
230-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN)>0);
231-
}
232-
233-
Datum
234-
name_pattern_ge(PG_FUNCTION_ARGS)
235-
{
236-
Namearg1=PG_GETARG_NAME(0);
237-
Namearg2=PG_GETARG_NAME(1);
238-
239-
PG_RETURN_BOOL(memcmp(NameStr(*arg1),NameStr(*arg2),NAMEDATALEN) >=0);
240-
}
241-
242-
243184
/* (see char.c for comparison/operation routines) */
244185

245186
int

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

Lines changed: 110 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/varchar.c,v 1.128 2008/05/04 16:42:41 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/varchar.c,v 1.129 2008/05/27 00:13:09 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -886,3 +886,112 @@ hashbpchar(PG_FUNCTION_ARGS)
886886

887887
returnresult;
888888
}
889+
890+
891+
/*
892+
* The following operators support character-by-character comparison
893+
* of bpchar datums, to allow building indexes suitable for LIKE clauses.
894+
* Note that the regular bpchareq/bpcharne comparison operators are assumed
895+
* to be compatible with these!
896+
*/
897+
898+
staticint
899+
internal_bpchar_pattern_compare(BpChar*arg1,BpChar*arg2)
900+
{
901+
intresult;
902+
intlen1,
903+
len2;
904+
905+
len1=bcTruelen(arg1);
906+
len2=bcTruelen(arg2);
907+
908+
result=strncmp(VARDATA_ANY(arg1),VARDATA_ANY(arg2),Min(len1,len2));
909+
if (result!=0)
910+
returnresult;
911+
elseif (len1<len2)
912+
return-1;
913+
elseif (len1>len2)
914+
return1;
915+
else
916+
return0;
917+
}
918+
919+
920+
Datum
921+
bpchar_pattern_lt(PG_FUNCTION_ARGS)
922+
{
923+
BpChar*arg1=PG_GETARG_BPCHAR_PP(0);
924+
BpChar*arg2=PG_GETARG_BPCHAR_PP(1);
925+
intresult;
926+
927+
result=internal_bpchar_pattern_compare(arg1,arg2);
928+
929+
PG_FREE_IF_COPY(arg1,0);
930+
PG_FREE_IF_COPY(arg2,1);
931+
932+
PG_RETURN_BOOL(result<0);
933+
}
934+
935+
936+
Datum
937+
bpchar_pattern_le(PG_FUNCTION_ARGS)
938+
{
939+
BpChar*arg1=PG_GETARG_BPCHAR_PP(0);
940+
BpChar*arg2=PG_GETARG_BPCHAR_PP(1);
941+
intresult;
942+
943+
result=internal_bpchar_pattern_compare(arg1,arg2);
944+
945+
PG_FREE_IF_COPY(arg1,0);
946+
PG_FREE_IF_COPY(arg2,1);
947+
948+
PG_RETURN_BOOL(result <=0);
949+
}
950+
951+
952+
Datum
953+
bpchar_pattern_ge(PG_FUNCTION_ARGS)
954+
{
955+
BpChar*arg1=PG_GETARG_BPCHAR_PP(0);
956+
BpChar*arg2=PG_GETARG_BPCHAR_PP(1);
957+
intresult;
958+
959+
result=internal_bpchar_pattern_compare(arg1,arg2);
960+
961+
PG_FREE_IF_COPY(arg1,0);
962+
PG_FREE_IF_COPY(arg2,1);
963+
964+
PG_RETURN_BOOL(result >=0);
965+
}
966+
967+
968+
Datum
969+
bpchar_pattern_gt(PG_FUNCTION_ARGS)
970+
{
971+
BpChar*arg1=PG_GETARG_BPCHAR_PP(0);
972+
BpChar*arg2=PG_GETARG_BPCHAR_PP(1);
973+
intresult;
974+
975+
result=internal_bpchar_pattern_compare(arg1,arg2);
976+
977+
PG_FREE_IF_COPY(arg1,0);
978+
PG_FREE_IF_COPY(arg2,1);
979+
980+
PG_RETURN_BOOL(result>0);
981+
}
982+
983+
984+
Datum
985+
btbpchar_pattern_cmp(PG_FUNCTION_ARGS)
986+
{
987+
BpChar*arg1=PG_GETARG_BPCHAR_PP(0);
988+
BpChar*arg2=PG_GETARG_BPCHAR_PP(1);
989+
intresult;
990+
991+
result=internal_bpchar_pattern_compare(arg1,arg2);
992+
993+
PG_FREE_IF_COPY(arg1,0);
994+
PG_FREE_IF_COPY(arg2,1);
995+
996+
PG_RETURN_INT32(result);
997+
}

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

Lines changed: 12 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.166 2008/05/12 00:00:51 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/varlena.c,v 1.167 2008/05/27 00:13:09 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1309,22 +1309,27 @@ text_smaller(PG_FUNCTION_ARGS)
13091309

13101310
/*
13111311
* The following operators support character-by-character comparison
1312-
* of text data types, to allow building indexes suitable for LIKE
1313-
* clauses.
1312+
* of text datums, to allow building indexes suitable for LIKE clauses.
1313+
* Note that the regular texteq/textne comparison operators are assumed
1314+
* to be compatible with these!
13141315
*/
13151316

13161317
staticint
13171318
internal_text_pattern_compare(text*arg1,text*arg2)
13181319
{
13191320
intresult;
1321+
intlen1,
1322+
len2;
1323+
1324+
len1=VARSIZE_ANY_EXHDR(arg1);
1325+
len2=VARSIZE_ANY_EXHDR(arg2);
13201326

1321-
result=memcmp(VARDATA_ANY(arg1),VARDATA_ANY(arg2),
1322-
Min(VARSIZE_ANY_EXHDR(arg1),VARSIZE_ANY_EXHDR(arg2)));
1327+
result=strncmp(VARDATA_ANY(arg1),VARDATA_ANY(arg2),Min(len1,len2));
13231328
if (result!=0)
13241329
returnresult;
1325-
elseif (VARSIZE_ANY_EXHDR(arg1)<VARSIZE_ANY_EXHDR(arg2))
1330+
elseif (len1<len2)
13261331
return-1;
1327-
elseif (VARSIZE_ANY_EXHDR(arg1)>VARSIZE_ANY_EXHDR(arg2))
1332+
elseif (len1>len2)
13281333
return1;
13291334
else
13301335
return0;
@@ -1363,25 +1368,6 @@ text_pattern_le(PG_FUNCTION_ARGS)
13631368
}
13641369

13651370

1366-
Datum
1367-
text_pattern_eq(PG_FUNCTION_ARGS)
1368-
{
1369-
text*arg1=PG_GETARG_TEXT_PP(0);
1370-
text*arg2=PG_GETARG_TEXT_PP(1);
1371-
intresult;
1372-
1373-
if (VARSIZE_ANY_EXHDR(arg1)!=VARSIZE_ANY_EXHDR(arg2))
1374-
result=1;
1375-
else
1376-
result=internal_text_pattern_compare(arg1,arg2);
1377-
1378-
PG_FREE_IF_COPY(arg1,0);
1379-
PG_FREE_IF_COPY(arg2,1);
1380-
1381-
PG_RETURN_BOOL(result==0);
1382-
}
1383-
1384-
13851371
Datum
13861372
text_pattern_ge(PG_FUNCTION_ARGS)
13871373
{
@@ -1414,25 +1400,6 @@ text_pattern_gt(PG_FUNCTION_ARGS)
14141400
}
14151401

14161402

1417-
Datum
1418-
text_pattern_ne(PG_FUNCTION_ARGS)
1419-
{
1420-
text*arg1=PG_GETARG_TEXT_PP(0);
1421-
text*arg2=PG_GETARG_TEXT_PP(1);
1422-
intresult;
1423-
1424-
if (VARSIZE_ANY_EXHDR(arg1)!=VARSIZE_ANY_EXHDR(arg2))
1425-
result=1;
1426-
else
1427-
result=internal_text_pattern_compare(arg1,arg2);
1428-
1429-
PG_FREE_IF_COPY(arg1,0);
1430-
PG_FREE_IF_COPY(arg2,1);
1431-
1432-
PG_RETURN_BOOL(result!=0);
1433-
}
1434-
1435-
14361403
Datum
14371404
bttext_pattern_cmp(PG_FUNCTION_ARGS)
14381405
{

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.461 2008/05/16 23:36:05 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.462 2008/05/27 00:13:09 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200805162
56+
#defineCATALOG_VERSION_NO200805261
5757

5858
#endif

0 commit comments

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