I have two collection named as users and user_data, users is a primary collection and the user_data is a secondary collection. I have perform an aggregate query for retriving millions of data but the query is taking too much time for execution. So, i try to use compound indexes on the fields of both the collections for e’g: audience_id: 1, contact.is_bounced: 1, contact.not_valid: 1 but on the time of exection secondary collection indexes are not use. Is there any way to use indexes on joined collection.
Db query :-
db.users.aggregate([{
$match: {
audience_id: {$in: [ObjectId('633eaba80cb7a3b1d910e98b'),ObjectId('63b6a5325bf3f04f18170e84')]
},
status: 'Subscribed'
}
}, {
$group: {
_id: '$contact_id',
contact_meta: {
$first: '$ROOT'
}
}
}, {
$lookup: {
from: 'user_data',
'let': {
id: '$_id'
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$eq: [
'$_id',
'$id'
]
}
},
{
email: {
$exists: true,
$ne: ''
}
},
{
$or: [
{
is_bounced: {
$exists: false
}
},
{
is_bounced: {
$ne: true
}
}
]
},
{
$or: [
{
not_valid: {
$exists: false
}
},
{
not_valid: {
$ne: true
}
}
]
}
]
}
}
],
as: 'contact' }
},
{
$unwind: {
path: '$contact',
preserveNullAndEmptyArrays: false
}
},
{
$addFields: {'contact.audience_id': '$contact_meta.audience_id'
}
},
{
$replaceRoot: {newRoot: '$contact'
}
}])
