-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Aggregation Framework
-
Query Optimization
-
(copied to CRM)
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
- is related to
-
-
- Open
-
- related to
-
SERVER-45326 Poor Aggregation Framework performance in relational queries
-
- Closed
-