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 retrieving 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 execution secondary collection indexes are not use. Is there any way to use indexes on joined collection.
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' } },
]);