-
Type: Task
-
Resolution: Fixed
-
Priority: Minor - P4
-
Affects Version/s: 6.0.0-rc0, 5.3.1
-
Component/s: Query Planning
-
None
-
Query Optimization
-
Fully Compatible
-
QO 2024-02-05
Neither the existing pcre library nor the new pcre2 library support ICU collation. Therefore predicates that contain a regex value/s should not be eligible to use indexes with non-simple collations for index scans, as the index will not be selective and all strings in the index will be used:
For example, notice this explain output and the final index bounds used:
replSet:PRIMARY> db.coll.createIndex({x: 1}, {collation: {locale: "en", strength: 1}}) replSet:PRIMARY> db.coll.explain().find({x: {$regex: /^foo$/, $options: "i"}}) { "explainVersion" : "2", "queryPlanner" : { "namespace" : "newer.coll", "indexFilterSet" : false, "parsedQuery" : { "x" : { "$regex" : "^foo$", "$options" : "i" } }, ... "winningPlan" : { "queryPlan" : { "stage" : "FETCH", "planNodeId" : 2, "filter" : { "x" : { "$regex" : "^foo$", "$options" : "i" } }, "inputStage" : { "stage" : "IXSCAN", "planNodeId" : 1, "keyPattern" : { "x" : 1 }, "indexName" : "x_1", "collation" : { "locale" : "en", "caseLevel" : false, "caseFirst" : "off", "strength" : 1, "numericOrdering" : false, "alternate" : "non-ignorable", "maxVariable" : "punct", "normalization" : false, "backwards" : false, "version" : "57.1" }, ... "indexBounds" : { "x" : [ "[CollationKey(0x), {})", "[/^foo$/i, /^foo$/i]" ] } ...
- is fixed by
-
SERVER-91616 Ignore indexes with collation for all kinds of regex predicates. Behind a query knob.
- Closed