Mongo aggregate query taking a long time

Question

I have aggregate query with the collection having 40k documents.

{ aggregate: "REV_TABLE", pipeline: [ { $match: { r: { $lte: 1486066601 }, ISDI_ID: "17" } }, { $group: { _id: "$l", maxVer: { $max: "$r" } } } ] } 

I have two indexes: {"r":1} and {"r":1 , "ISDI":1} and background indexing is on. Initially when the documents were small it used to take < 100ms. Now since it's around 35k+ its taking more than 300ms.

Is this expected and I'm guessing 35k is not that large but why is the query taking so long to come back.

Answer 1

I suggest taking a look at the docs for Analyzing Query Performance in MongoDB.

You can run you query with .explain("executionStats") on the end of it (e.g. db.collection.aggregate(yourQuery).explain('executionStats')), and you'll get an output analyzing the performance of each part of the query, including times for index scans, which indexes are being used, etc. Then you can take steps to optimize your query. Or, if there's nothing more you can do to optimize it, then you'll know that it simply is a consequence of having a huge amount of docs that you're trying to aggregate.

Keep in mind, aggregation is also typically for "admin backends" and data analytics and isn't as frequently used in consumer-facing applications since aggregation is heavy. It simply takes a long time to aggregate collections.

You can also have MongoDB automatically dump the query plans for slow queries into a collection for you (read Database Profiling in MongoDB).

For example, running db.setProfilingLevel(1,20) will dump the query plan of any ops taking over 20ms into a collection called system.profile.