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

Commit2ddedca

Browse files
committed
Reduce match_pattern_prefix()'s dependencies on index opfamilies.
Historically, the planner's LIKE/regex index optimizations were onlycarried out for specific index opfamilies. That's never been a greatidea from the standpoint of extensibility, but it didn't matter somuch as long as we had no practical way to extend such behaviors anyway.With the addition of planner support functions, and in view of ongoingwork to support additional table and index AMs, it seems like a goodtime to relax this.Hence, recast the decisions in match_pattern_prefix() so that ratherthan decide which operators to generate by looking at what the indexopfamily contains, we decide which operators to generate a-prioriand then see if the opfamily supports them. This is much moredefensible from a semantic standpoint anyway, since we know thesemantics of the chosen operators precisely, and we only need toassume that the opfamily correctly implements operators it claimsto support.The existing "pattern" opfamilies put a crimp in this approach, sincewe need to select the pattern operators if we want those to work.So we still have to special-case those opfamilies. But that seemsall right, since in view of the addition of collations, the patternopfamilies seem like a legacy hack that nobody will be building on.The only immediate effect of this change, so far as the core code isconcerned, is that anchored LIKE/regex patterns can be mapped ontoBRIN index searches, and exact-match patterns can be mapped onto hashindexes, not only btree and spgist indexes as before. That's not aterribly exciting result, but it does fix an omission mentioned inthe ancient comments here.Note: no catversion bump, even though this touches pg_operator.dat,because it's only adding OID macros not changing the contents ofpostgres.bki.Per consideration of a report from Manuel Rigger.Discussion:https://postgr.es/m/CA+u7OA7nnGYy8rY0vdTe811NuA+Frr9nbcBO9u2Z+JxqNaud+g@mail.gmail.com
1 parent86be645 commit2ddedca

File tree

2 files changed

+108
-72
lines changed

2 files changed

+108
-72
lines changed

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

Lines changed: 85 additions & 57 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939
#include"access/htup_details.h"
4040
#include"access/stratnum.h"
4141
#include"catalog/pg_collation.h"
42+
#include"catalog/pg_operator.h"
4243
#include"catalog/pg_opfamily.h"
4344
#include"catalog/pg_statistic.h"
4445
#include"catalog/pg_type.h"
@@ -240,7 +241,10 @@ match_pattern_prefix(Node *leftop,
240241
Pattern_Prefix_Statuspstatus;
241242
Oidldatatype;
242243
Oidrdatatype;
243-
Oidoproid;
244+
Oideqopr;
245+
Oidltopr;
246+
Oidgeopr;
247+
boolcollation_aware;
244248
Expr*expr;
245249
FmgrInfoltproc;
246250
Const*greaterstr;
@@ -284,62 +288,89 @@ match_pattern_prefix(Node *leftop,
284288
returnNIL;
285289

286290
/*
287-
* Must also check that index's opfamily supports the operators we will
288-
* want to apply. (A hash index, for example, will not support ">=".)
289-
* Currently, only btree and spgist support the operators we need.
290-
*
291-
* Note: actually, in the Pattern_Prefix_Exact case, we only need "=" so a
292-
* hash index would work. Currently it doesn't seem worth checking for
293-
* that, however.
294-
*
295-
* We insist on the opfamily being one of the specific ones we expect,
296-
* else we'd do the wrong thing if someone were to make a reverse-sort
297-
* opfamily with the same operators.
298-
*
299-
* The non-pattern opclasses will not sort the way we need in most non-C
300-
* locales. We can use such an index anyway for an exact match (simple
301-
* equality), but not for prefix-match cases. Note that here we are
302-
* looking at the index's collation, not the expression's collation --
303-
* this test is *not* dependent on the LIKE/regex operator's collation.
304-
*
305-
* While we're at it, identify the type the comparison constant(s) should
306-
* have, based on the opfamily.
291+
* Identify the operators we want to use, based on the type of the
292+
* left-hand argument. Usually these are just the type's regular
293+
* comparison operators, but if we are considering one of the semi-legacy
294+
* "pattern" opclasses, use the "pattern" operators instead. Those are
295+
* not collation-sensitive but always use C collation, as we want. The
296+
* selected operators also determine the needed type of the prefix
297+
* constant.
307298
*/
308-
switch (opfamily)
299+
ldatatype=exprType(leftop);
300+
switch (ldatatype)
309301
{
310-
caseTEXT_BTREE_FAM_OID:
311-
if (!(pstatus==Pattern_Prefix_Exact||
312-
lc_collate_is_c(indexcollation)))
313-
returnNIL;
302+
caseTEXTOID:
303+
if (opfamily==TEXT_PATTERN_BTREE_FAM_OID||
304+
opfamily==TEXT_SPGIST_FAM_OID)
305+
{
306+
eqopr=TextEqualOperator;
307+
ltopr=TextPatternLessOperator;
308+
geopr=TextPatternGreaterEqualOperator;
309+
collation_aware= false;
310+
}
311+
else
312+
{
313+
eqopr=TextEqualOperator;
314+
ltopr=TextLessOperator;
315+
geopr=TextGreaterEqualOperator;
316+
collation_aware= true;
317+
}
314318
rdatatype=TEXTOID;
315319
break;
320+
caseNAMEOID:
316321

317-
caseTEXT_PATTERN_BTREE_FAM_OID:
318-
caseTEXT_SPGIST_FAM_OID:
322+
/*
323+
* Note that here, we need the RHS type to be text, so that the
324+
* comparison value isn't improperly truncated to NAMEDATALEN.
325+
*/
326+
eqopr=NameEqualTextOperator;
327+
ltopr=NameLessTextOperator;
328+
geopr=NameGreaterEqualTextOperator;
329+
collation_aware= true;
319330
rdatatype=TEXTOID;
320331
break;
321-
322-
caseBPCHAR_BTREE_FAM_OID:
323-
if (!(pstatus==Pattern_Prefix_Exact||
324-
lc_collate_is_c(indexcollation)))
325-
returnNIL;
326-
rdatatype=BPCHAROID;
327-
break;
328-
329-
caseBPCHAR_PATTERN_BTREE_FAM_OID:
332+
caseBPCHAROID:
333+
if (opfamily==BPCHAR_PATTERN_BTREE_FAM_OID)
334+
{
335+
eqopr=BpcharEqualOperator;
336+
ltopr=BpcharPatternLessOperator;
337+
geopr=BpcharPatternGreaterEqualOperator;
338+
collation_aware= false;
339+
}
340+
else
341+
{
342+
eqopr=BpcharEqualOperator;
343+
ltopr=BpcharLessOperator;
344+
geopr=BpcharGreaterEqualOperator;
345+
collation_aware= true;
346+
}
330347
rdatatype=BPCHAROID;
331348
break;
332-
333-
caseBYTEA_BTREE_FAM_OID:
349+
caseBYTEAOID:
350+
eqopr=ByteaEqualOperator;
351+
ltopr=ByteaLessOperator;
352+
geopr=ByteaGreaterEqualOperator;
353+
collation_aware= false;
334354
rdatatype=BYTEAOID;
335355
break;
336-
337356
default:
357+
/* Can't get here unless we're attached to the wrong operator */
338358
returnNIL;
339359
}
340360

341-
/* OK, prepare to create the indexqual(s) */
342-
ldatatype=exprType(leftop);
361+
/*
362+
* If necessary, verify that the index's collation behavior is compatible.
363+
* For an exact-match case, we don't have to be picky. Otherwise, insist
364+
* that the index collation be "C". Note that here we are looking at the
365+
* index's collation, not the expression's collation -- this test is *not*
366+
* dependent on the LIKE/regex operator's collation.
367+
*/
368+
if (collation_aware)
369+
{
370+
if (!(pstatus==Pattern_Prefix_Exact||
371+
lc_collate_is_c(indexcollation)))
372+
returnNIL;
373+
}
343374

344375
/*
345376
* If necessary, coerce the prefix constant to the right type. The given
@@ -358,16 +389,17 @@ match_pattern_prefix(Node *leftop,
358389
/*
359390
* If we found an exact-match pattern, generate an "=" indexqual.
360391
*
361-
* (Despite the checks above, we might fail to find a suitable operator in
362-
* some cases with binary-compatible opclasses. Just punt if so.)
392+
* Here and below, check to see whether the desired operator is actually
393+
* supported by the index opclass, and fail quietly if not. This allows
394+
* us to not be concerned with specific opclasses (except for the legacy
395+
* "pattern" cases); any index that correctly implements the operators
396+
* will work.
363397
*/
364398
if (pstatus==Pattern_Prefix_Exact)
365399
{
366-
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
367-
BTEqualStrategyNumber);
368-
if (oproid==InvalidOid)
400+
if (!op_in_opfamily(eqopr,opfamily))
369401
returnNIL;
370-
expr=make_opclause(oproid,BOOLOID, false,
402+
expr=make_opclause(eqopr,BOOLOID, false,
371403
(Expr*)leftop, (Expr*)prefix,
372404
InvalidOid,indexcollation);
373405
result=list_make1(expr);
@@ -379,11 +411,9 @@ match_pattern_prefix(Node *leftop,
379411
*
380412
* We can always say "x >= prefix".
381413
*/
382-
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
383-
BTGreaterEqualStrategyNumber);
384-
if (oproid==InvalidOid)
414+
if (!op_in_opfamily(geopr,opfamily))
385415
returnNIL;
386-
expr=make_opclause(oproid,BOOLOID, false,
416+
expr=make_opclause(geopr,BOOLOID, false,
387417
(Expr*)leftop, (Expr*)prefix,
388418
InvalidOid,indexcollation);
389419
result=list_make1(expr);
@@ -396,15 +426,13 @@ match_pattern_prefix(Node *leftop,
396426
* using a C-locale index collation.
397427
*-------
398428
*/
399-
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
400-
BTLessStrategyNumber);
401-
if (oproid==InvalidOid)
429+
if (!op_in_opfamily(ltopr,opfamily))
402430
returnresult;
403-
fmgr_info(get_opcode(oproid),&ltproc);
431+
fmgr_info(get_opcode(ltopr),&ltproc);
404432
greaterstr=make_greater_string(prefix,&ltproc,indexcollation);
405433
if (greaterstr)
406434
{
407-
expr=make_opclause(oproid,BOOLOID, false,
435+
expr=make_opclause(ltopr,BOOLOID, false,
408436
(Expr*)leftop, (Expr*)greaterstr,
409437
InvalidOid,indexcollation);
410438
result=lappend(result,expr);

‎src/include/catalog/pg_operator.dat

Lines changed: 23 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -107,12 +107,12 @@
107107
oprcode => 'starts_with', oprrest => 'prefixsel',
108108
oprjoin => 'prefixjoinsel' },
109109

110-
{ oid => '254', descr => 'equal',
110+
{ oid => '254',oid_symbol => 'NameEqualTextOperator',descr => 'equal',
111111
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'name',
112112
oprright => 'text', oprresult => 'bool', oprcom => '=(text,name)',
113113
oprnegate => '<>(name,text)', oprcode => 'nameeqtext', oprrest => 'eqsel',
114114
oprjoin => 'eqjoinsel' },
115-
{ oid => '255', descr => 'less than',
115+
{ oid => '255',oid_symbol => 'NameLessTextOperator',descr => 'less than',
116116
oprname => '<', oprleft => 'name', oprright => 'text', oprresult => 'bool',
117117
oprcom => '>(text,name)', oprnegate => '>=(name,text)',
118118
oprcode => 'namelttext', oprrest => 'scalarltsel',
@@ -122,7 +122,8 @@
122122
oprcom => '>=(text,name)', oprnegate => '>(name,text)',
123123
oprcode => 'nameletext', oprrest => 'scalarlesel',
124124
oprjoin => 'scalarlejoinsel' },
125-
{ oid => '257', descr => 'greater than or equal',
125+
{ oid => '257', oid_symbol => 'NameGreaterEqualTextOperator',
126+
descr => 'greater than or equal',
126127
oprname => '>=', oprleft => 'name', oprright => 'text', oprresult => 'bool',
127128
oprcom => '<=(text,name)', oprnegate => '<(name,text)',
128129
oprcode => 'namegetext', oprrest => 'scalargesel',
@@ -789,7 +790,7 @@
789790
oprname => '>=', oprleft => 'name', oprright => 'name', oprresult => 'bool',
790791
oprcom => '<=(name,name)', oprnegate => '<(name,name)', oprcode => 'namege',
791792
oprrest => 'scalargesel', oprjoin => 'scalargejoinsel' },
792-
{ oid => '664', descr => 'less than',
793+
{ oid => '664',oid_symbol => 'TextLessOperator',descr => 'less than',
793794
oprname => '<', oprleft => 'text', oprright => 'text', oprresult => 'bool',
794795
oprcom => '>(text,text)', oprnegate => '>=(text,text)', oprcode => 'text_lt',
795796
oprrest => 'scalarltsel', oprjoin => 'scalarltjoinsel' },
@@ -801,7 +802,8 @@
801802
oprname => '>', oprleft => 'text', oprright => 'text', oprresult => 'bool',
802803
oprcom => '<(text,text)', oprnegate => '<=(text,text)', oprcode => 'text_gt',
803804
oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' },
804-
{ oid => '667', descr => 'greater than or equal',
805+
{ oid => '667', oid_symbol => 'TextGreaterEqualOperator',
806+
descr => 'greater than or equal',
805807
oprname => '>=', oprleft => 'text', oprright => 'text', oprresult => 'bool',
806808
oprcom => '<=(text,text)', oprnegate => '<(text,text)', oprcode => 'text_ge',
807809
oprrest => 'scalargesel', oprjoin => 'scalargejoinsel' },
@@ -1160,7 +1162,7 @@
11601162
oprname => '@@', oprkind => 'l', oprleft => '0', oprright => 'polygon',
11611163
oprresult => 'point', oprcode => 'poly_center' },
11621164

1163-
{ oid => '1054', descr => 'equal',
1165+
{ oid => '1054',oid_symbol => 'BpcharEqualOperator',descr => 'equal',
11641166
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'bpchar',
11651167
oprright => 'bpchar', oprresult => 'bool', oprcom => '=(bpchar,bpchar)',
11661168
oprnegate => '<>(bpchar,bpchar)', oprcode => 'bpchareq', oprrest => 'eqsel',
@@ -1180,7 +1182,7 @@
11801182
oprresult => 'bool', oprcom => '<>(bpchar,bpchar)',
11811183
oprnegate => '=(bpchar,bpchar)', oprcode => 'bpcharne', oprrest => 'neqsel',
11821184
oprjoin => 'neqjoinsel' },
1183-
{ oid => '1058', descr => 'less than',
1185+
{ oid => '1058',oid_symbol => 'BpcharLessOperator',descr => 'less than',
11841186
oprname => '<', oprleft => 'bpchar', oprright => 'bpchar',
11851187
oprresult => 'bool', oprcom => '>(bpchar,bpchar)',
11861188
oprnegate => '>=(bpchar,bpchar)', oprcode => 'bpcharlt',
@@ -1195,7 +1197,8 @@
11951197
oprresult => 'bool', oprcom => '<(bpchar,bpchar)',
11961198
oprnegate => '<=(bpchar,bpchar)', oprcode => 'bpchargt',
11971199
oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' },
1198-
{ oid => '1061', descr => 'greater than or equal',
1200+
{ oid => '1061', oid_symbol => 'BpcharGreaterEqualOperator',
1201+
descr => 'greater than or equal',
11991202
oprname => '>=', oprleft => 'bpchar', oprright => 'bpchar',
12001203
oprresult => 'bool', oprcom => '<=(bpchar,bpchar)',
12011204
oprnegate => '<(bpchar,bpchar)', oprcode => 'bpcharge',
@@ -2330,7 +2333,7 @@
23302333
oprresult => 'numeric', oprcode => 'numeric_uplus' },
23312334

23322335
# bytea operators
2333-
{ oid => '1955', descr => 'equal',
2336+
{ oid => '1955',oid_symbol => 'ByteaEqualOperator',descr => 'equal',
23342337
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'bytea',
23352338
oprright => 'bytea', oprresult => 'bool', oprcom => '=(bytea,bytea)',
23362339
oprnegate => '<>(bytea,bytea)', oprcode => 'byteaeq', oprrest => 'eqsel',
@@ -2339,7 +2342,7 @@
23392342
oprname => '<>', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool',
23402343
oprcom => '<>(bytea,bytea)', oprnegate => '=(bytea,bytea)',
23412344
oprcode => 'byteane', oprrest => 'neqsel', oprjoin => 'neqjoinsel' },
2342-
{ oid => '1957', descr => 'less than',
2345+
{ oid => '1957',oid_symbol => 'ByteaLessOperator',descr => 'less than',
23432346
oprname => '<', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool',
23442347
oprcom => '>(bytea,bytea)', oprnegate => '>=(bytea,bytea)',
23452348
oprcode => 'bytealt', oprrest => 'scalarltsel',
@@ -2354,7 +2357,8 @@
23542357
oprcom => '<(bytea,bytea)', oprnegate => '<=(bytea,bytea)',
23552358
oprcode => 'byteagt', oprrest => 'scalargtsel',
23562359
oprjoin => 'scalargtjoinsel' },
2357-
{ oid => '1960', descr => 'greater than or equal',
2360+
{ oid => '1960', oid_symbol => 'ByteaGreaterEqualOperator',
2361+
descr => 'greater than or equal',
23582362
oprname => '>=', oprleft => 'bytea', oprright => 'bytea', oprresult => 'bool',
23592363
oprcom => '<=(bytea,bytea)', oprnegate => '<(bytea,bytea)',
23602364
oprcode => 'byteage', oprrest => 'scalargesel',
@@ -2416,7 +2420,8 @@
24162420
oprresult => 'timestamp', oprcode => 'timestamp_mi_interval' },
24172421

24182422
# character-by-character (not collation order) comparison operators for character types
2419-
{ oid => '2314', descr => 'less than',
2423+
{ oid => '2314', oid_symbol => 'TextPatternLessOperator',
2424+
descr => 'less than',
24202425
oprname => '~<~', oprleft => 'text', oprright => 'text', oprresult => 'bool',
24212426
oprcom => '~>~(text,text)', oprnegate => '~>=~(text,text)',
24222427
oprcode => 'text_pattern_lt', oprrest => 'scalarltsel',
@@ -2426,7 +2431,8 @@
24262431
oprcom => '~>=~(text,text)', oprnegate => '~>~(text,text)',
24272432
oprcode => 'text_pattern_le', oprrest => 'scalarlesel',
24282433
oprjoin => 'scalarlejoinsel' },
2429-
{ oid => '2317', descr => 'greater than or equal',
2434+
{ oid => '2317', oid_symbol => 'TextPatternGreaterEqualOperator',
2435+
descr => 'greater than or equal',
24302436
oprname => '~>=~', oprleft => 'text', oprright => 'text', oprresult => 'bool',
24312437
oprcom => '~<=~(text,text)', oprnegate => '~<~(text,text)',
24322438
oprcode => 'text_pattern_ge', oprrest => 'scalargesel',
@@ -2437,7 +2443,8 @@
24372443
oprcode => 'text_pattern_gt', oprrest => 'scalargtsel',
24382444
oprjoin => 'scalargtjoinsel' },
24392445

2440-
{ oid => '2326', descr => 'less than',
2446+
{ oid => '2326', oid_symbol => 'BpcharPatternLessOperator',
2447+
descr => 'less than',
24412448
oprname => '~<~', oprleft => 'bpchar', oprright => 'bpchar',
24422449
oprresult => 'bool', oprcom => '~>~(bpchar,bpchar)',
24432450
oprnegate => '~>=~(bpchar,bpchar)', oprcode => 'bpchar_pattern_lt',
@@ -2447,7 +2454,8 @@
24472454
oprresult => 'bool', oprcom => '~>=~(bpchar,bpchar)',
24482455
oprnegate => '~>~(bpchar,bpchar)', oprcode => 'bpchar_pattern_le',
24492456
oprrest => 'scalarlesel', oprjoin => 'scalarlejoinsel' },
2450-
{ oid => '2329', descr => 'greater than or equal',
2457+
{ oid => '2329', oid_symbol => 'BpcharPatternGreaterEqualOperator',
2458+
descr => 'greater than or equal',
24512459
oprname => '~>=~', oprleft => 'bpchar', oprright => 'bpchar',
24522460
oprresult => 'bool', oprcom => '~<=~(bpchar,bpchar)',
24532461
oprnegate => '~<~(bpchar,bpchar)', oprcode => 'bpchar_pattern_ge',

0 commit comments

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