indexing - 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?

Answer 1

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