Index optimization for mongodb aggregation framework

Question

I have a match-unwind-group-sort aggregation pipeline in mongo 2.4.4 and I need to speed up the aggregation.

The match operation consists of range queries on 16 fields. I've used the .explain() method to optimize range queries (i.e. create compound indexes). Is there a similar function for optimizing the aggregation? I'm looking for something like:

db.col.aggregate([]).explain()

Also, am I right to focus on index optimization?

There is in the latest unstable: jira.mongodb.org/browse/SERVER-4504 but until then no, and no there is no indexes being use past the mathc as such index optimsation is not a good path — Sammaye
@Sammaye that's wrong, match most certainly does use indexes as does sort. — Asya Kamsky
@AsyaKamsky That's what I just said, I actually said PAST the match, i.e. in the $group — Sammaye
@AsyaKamsky not if you have already optimised it as he said in his question — Sammaye

Rafa Rafa · Accepted Answer · 2013-10-25T13:52:42

For the first question, yes, you can explain aggregates.

db.collection.runCommand("aggregate", {pipeline: YOUR_PIPELINE, explain: true})

For the second one, the indexes you create to optimize the range queries will also apply to the $match stage of the aggregation pipeline, if they occur at the beginning of the pipeline. So you are right to focus on index optimizations.

See Pipeline Operators and Indexes.

Update 2

More about aggregate and explain: on version 2.4 it is unreliable; on 2.6+ it does not provide query execution data. https://groups.google.com/forum/#!topic/mongodb-user/2LzAkyaNqe0

Update 1

Transcript of an aggregation explain on MongoDB 2.4.5.

$ mongo so
MongoDB shell version: 2.4.5
connecting to: so
> db.q19329239.runCommand("aggregate", {pipeline: [{$group: {_id: '$user.id', hits: {$sum: 1}}}, {$match: {hits: {$gt: 10}}}], explain: true})
{
    "serverPipeline" : [
        {
            "query" : {

            },
            "projection" : {
                "user.id" : 1,
                "_id" : 0
            },
            "cursor" : {
                "cursor" : "BasicCursor",
                "isMultiKey" : false,
                "n" : 1031,
                "nscannedObjects" : 1031,
                "nscanned" : 1031,
                "nscannedObjectsAllPlans" : 1031,
                "nscannedAllPlans" : 1031,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {

                },
                "allPlans" : [
                    {
                        "cursor" : "BasicCursor",
                        "n" : 1031,
                        "nscannedObjects" : 1031,
                        "nscanned" : 1031,
                        "indexBounds" : {

                        }
                    }
                ],
                "server" : "ficrm-rafa.local:27017"
            }
        },
        {
            "$group" : {
                "_id" : "$user.id",
                "hits" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            }
        },
        {
            "$match" : {
                "hits" : {
                    "$gt" : 10
                }
            }
        }
    ],
    "ok" : 1
}

Server version.

$ mongo so
MongoDB shell version: 2.4.5
connecting to: so
> db.version()
2.4.5

Index optimization for mongodb aggregation framework

1 Answers