[SERVER-34428] $facet aggregation stage does not use index created for the sub pipelines in it and perform COLLSCAN instead. Created: 12/Apr/18  Updated: 23/Jul/18  Resolved: 26/Jun/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.6.3
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: srivani kambhampati Assignee: Nick Brewer
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-30474 leading $facet with each facet beginn... Backlog
Operating System: ALL
Participants:

 Description   

i have query like below to implement full outer join using $facet aggregation. If i run them as separate queries, they perform fine and use index i have but if i put in $facet it does not use the index but perform COLLSCAN.

// Some comments here
db.servicestats.aggregate([ 
{$facet: {output1:[
  {$match:{$and :[ {'scenarioId':0}
  ]}},
{$lookup: { from:'servicestats',
  let: {ocode:'$origin.code',dcode:'$destination.code',stype:'$serviceType'},
  pipeline:[
  {$match: {
    			  $expr: { $and:
                       [
                         { $eq: [ "$scenarioId", 1 ] },
                         { $eq: [ "$origin.code",  "$$ocode" ] },
                         { $eq: [ "$destination.code",  "$$dcode" ] },
                         { $eq: [ "$serviceType",  "$$stype" ] },
                       ]
                    }
    			
  		    }
  },
  {$project: {_id:0, comp :{compavgmiles :'$currentOutput.avgMiles'}}},
  { $replaceRoot: { newRoot: "$comp" } }
  ],
  as : "compoutputs"
}},
{
          $replaceRoot: {
             newRoot: {
                $mergeObjects:[
                   {
                      $arrayElemAt: [
                         "$$ROOT.compoutputs",
                         0
                      ]
                   },
                   {
                      origin: "$$ROOT.origin",
                      destination: "$$ROOT.destination",
                      serviceType: "$$ROOT.serviceType",
                      baseavgmiles: "$$ROOT.currentOutput.avgMiles",
                      output: '$$ROOT'
                   }
                ]
             }
          }
       }
  ],
  output2:[
    {$match:{$and :[ {'scenarioId':1}
  ]}},
{$lookup: { from:'servicestats',
  let: {ocode:'$origin.code',dcode:'$destination.code',stype:'$serviceType'},
  pipeline:[
  {$match: {
    			  $expr: { $and:
                       [
                         { $eq: [ "$scenarioId", 0 ] },
                         { $eq: [ "$origin.code",  "$$ocode" ] },
                         { $eq: [ "$destination.code",  "$$dcode" ] },
                         { $eq: [ "$serviceType",  "$$stype" ] },
                       ]
                    }
    			
  		    }
  },
  {$project: {_id:0, comp :{compavgmiles :'$currentOutput.avgMiles'}}},
  { $replaceRoot: { newRoot: "$comp" } }
  ],
  as : "compoutputs"
}},
//{
//          $replaceRoot: {
//             newRoot: {
//                $mergeObjects:[
//                   {
//                      $arrayElemAt: [
//                         "$$ROOT.compoutputs",
//                         0
//                      ]
//                   },
//                   {
//                      origin: "$$ROOT.origin",
//                      destination: "$$ROOT.destination",
//                      serviceType: "$$ROOT.serviceType",
//                      baseavgmiles: "$$ROOT.currentOutput.avgMiles",
//                      output: '$$ROOT'
//                   }
//                ]
//             }
//          }
//       },
       {$match :{'compoutputs':{$eq:[]}}}
  
  ]
  }
}
])



 Comments   
Comment by Nick Brewer [ 26/Jun/18 ]

Hi,

You're correct - $facet does not use an index. Instead, you could add a $match stage before $facet:

$match:{'scenarioId':{$in:[0,1]}}}

Assuming that in both cases the index is on scenarioId.

Since this appears to be a duplicate of SERVER-30474, I'm closing this ticket in favor of that one. Feel free to watch / vote on that ticket instead.

Regards,
Nick

Generated at Thu Feb 08 04:36:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.