CODEWITHSUNDEEP
mongodb
Tuz
Tuz

Reputation: 1980

Indexing an aggregation slow query on mongo db

I got a slow query on mongo about around 50k documents in a collection

How can I index it?

I tried to add the following index but it does not solve the issue

db.getCollection("events").createIndex({ "area.area_id": 1, "execute_time": -1 })

"Slow query","attr":{"type":"command","ns":"events.events",
"command":{"aggregate":"events","pipeline":
[
  {"$facet":{"1":[{"$match":{"area.area_id":"1"}},
  {"$sort":{"execute_time":-1}},{"$limit":30}
],


"2":

[
  {"$match":{"area.area_id":"2"}},
  {"$sort":{"execute_time":-1}},{"$limit":30}]}}
]
,"cursor":{},
"lsid":
{"id":{"$uuid":"2be3c461-dfc7-4591-adaf-da9454b9615c"}},"$db":"events"},
"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":37973,"cursorExhausted":true,"numYields":37,"nreturned":1,
"reslen":118011,"locks":{"ReplicationStateTransition":{"acquireCount":{"w":61}},"Global":{"acquireCount":{"r":61}},
"Database":{"acquireCount":{"r":61}},"Collection":{"acquireCount":{"r":61}},"Mutex":{"acquireCount":{"r":24}}},"storage":{},"protocol":"op_msg","durationMillis":262}}

my query:


 this.collection.aggregate([
                {$facet: facetObj }])

each facet obj is something like:

    facet[x] = [
                {$match: {'area.area_id': x}},         
                {$sort: { execution_time: -1 }},
                {$limit: limit}
            ]

Upvotes: 0

Views: 281

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You did not show any input data nor the expected result. However from what I see, one approach could be this one:

db.collection.aggregate([
  { $match: { area_id: { $in: [ 1, 2 ] } } },
  { $sort: { execute_time: -1 } },
  {
    $group: {
      _id: "$area_id",
      execute_time: { $push: "$execute_time" }
    }
  },
  {
    $set: {
      execute_time: { $slice: [ "$execute_time", 30 ] }
    }
  }
])

Mongo playground

Upvotes: 0

Montgomery Watts
Montgomery Watts

Reputation: 4034

You cannot use indexes in the $facet stage.

From the MongoDB documentation:

The $facet stage, and its sub-pipelines, cannot make use of indexes, even if its sub-pipelines use $match or if $facet is the first stage in the pipeline. The $facet stage will always perform a COLLSCAN during execution.

Upvotes: 3

Related Questions