Details
-
Improvement
-
Resolution: Duplicate
-
Minor - P4
-
None
-
4.0.11
-
None
Description
When selecting an index, and the index is sparse (or partialFilterExpression is used), the documentation describes that the query predicate must include the partialFilteredExpression. See https://docs.mongodb.com/manual/core/index-partial/#query-coverage
However, if an index is defined as sparse like so:
db.test.createIndex({sparseField: 1}, { |
background: true, |
partialFilterExpression: {sparseField: {$exists: true}} |
});
|
Then queries against test do not always select this index when using the $eq operator. Example:
use test;
|
db.test.drop();
|
for (var i = 1; i <= 10000; i++) { |
db.test.insert( { name: "name"+i } ); |
}
|
db.test.insert({name: 'test record', sparseField: 'sparseTest'}); |
db.test.createIndex({sparseField: 1}, { |
background: true, |
partialFilterExpression: {sparseField: {$exists: true}} |
});
|
|
|
# uses index
|
db.test.find({
|
sparseField: 'sparseTest' |
}).explain();
|
|
|
# DOES NOT USE INDEX!!!
|
db.test.find({
|
$expr: {$eq: ['$sparseField', 'sparseTest']} |
}).explain();
|
|
|
# uses index
|
db.test.find({
|
sparseField: {$exists: true}, |
$expr: {$eq: ['$sparseField', 'sparseTest']} |
}).explain();
|
I would think that the $eq operator should be considered a subset of $exists for purposes of index selection, right?
FYI, I originally filed my findings on this google user group:
https://groups.google.com/forum/?nomobile=true#!topic/mongodb-user/KxvqiQX17UY
Attachments
Issue Links
- duplicates
-
SERVER-38799 $expr does not use sparse or partial indexes
-
- Backlog
-