Details
-
Improvement
-
Status: Backlog
-
Major - P3
-
Resolution: Unresolved
-
None
-
None
-
Query Optimization
Description
Description
Suppose we have the following documents in the emails collection:
[{_id: ObjectId(), subject: 'Hello there', to: ['address1@example.com', 'address2@example.com']}] |
With the index:
{ { "v" : 2, "key" : { "to" : -1 }, "name" : "to_-1", "ns" : "test.emails" } |
And the following documents in the collection users:
[{_id: ObjectId(), name: 'John Doe', address: 'address1@example.com'}] |
Then, if we use the following pipeline on 'users' in order to insert all emails sent to our users into 'sentEmails':
db.users.aggregate([
|
{$lookup: {
|
from: 'emails', |
let: {userAddress: '$address'}, |
as: 'sentEmails', |
pipeline: [
|
{$match: {$expr: {
|
$in: ['$$userAdress', '$to'] |
}}}
|
]
|
}}
|
])
|
Or, even if we extracted the emails sub-pipeline into an equivalent pipeline for our first user (John Doe):
db.emails.aggregate([
|
{$match: {$expr: {
|
$in: ['address1@example.com', '$to'] |
}}}
|
])
|
Â
Its not using the index on the emails collection.
Â
However, if you do the same without using the $expr, the index is used properly:
db.emails.aggregate([{$match: {to: 'address1@example.com'}}], {explain: true}) |
...
|
"winningPlan" : { "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", |
...
|
Observed results
The index is not used when the $in operator is used inside an $expr on a $match
Expected results
The index should be used to perform the $match stage
Attachments
Issue Links
- is related to
-
-
- Backlog
-
- related to
-
SERVER-45326 Poor Aggregation Framework performance in relational queries
-
- Closed
-