MongoDB $project and index usage
mongodb
Solution
If you `$project` first then you are doing a collection scan, outputting ALL documents in that collection with that form. It is the same as saying:
"Give me all documents in the collection with only the `country` field and `_id`"
This result is then passed onto the next pipeline which happens to be a `$match` which causes a full collection scan. You will, of course, have two full collection scans here since the `$match` can no longer use an index either.
You might be able to perform an index scan instead of a collection but as you said the only real way is to actually switch the order of the two around so that you limit your documents and then project.
Problem
I use aggregation framework to group some data. It was observed that when using $project pipeline stage, it somehow prevents following $match from using the index. I have an index on field 'timestamp', collection contains 500 000 records. If I use following command and pipeline: ``` db.collection.runCommand('aggregate', {pipeline: [ { "$match" : { "timestamp" : { "$gt" : 1388425361294 , "$lt" : 1388443361294}}} ], explain: true}) ``` the execution plan is pretty much what is expected, i.e. 4 documents scanned. Excerpt from 'explain': ``` "cursor" : { "cursor" : "BtreeCursor timestamp_1", "isMultiKey" : false, "n" : 4, "nscannedObjects" : 4, "nscanned" : 4, "nscannedObjectsAllPlans" : 4, "nscannedAllPlans" : 4, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "timestamp" : [ [ 1388425361294, 1388443361294 ] ] }, ....... ``` But the behavior is drastically changed, once I use any of $project parameters. The following command (the 'country' field may not even exist in any of docs, it doesn't make any difference): ``` db.collection.runCommand('aggregate', {pipeline: [ { "$project" : { "country" : "$country"} , { "$match" : { "timestamp" : { "$gt" : 1388425361294 , "$lt" : 1388443361294}}} ], explain: true}) ``` produces this plan: ``` "cursor" : { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 500001, "nscannedObjects" : 500001, "nscanned" : 500001, "nscannedObjectsAllPlans" : 50 "nscannedAllPlans" : 500001, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 101, "indexBounds" : { }, ``` .... obviously forces to scan ALL records of the collection, which is unacceptable for me. Did I miss something important in using of $project pipeline stage?