Using an index for sort stage of aggregation pipeline with last or first in group stage

Viewed 70

I've noticed that MongoDB is doing a collection scan when I do last or first in the group stage of an aggregation pipeline even if I sort using an index. Say I want to get all the fields of matching documents - would I have to have an index that contains all the fields to be able to avoid the collection scan?

Here is my sample data:

{ "_id" : 1, "item" : "abc", "date" : ISODate("2014-01-01T08:00:00Z"), "price" : 10, "quantity" : 2 }
{ "_id" : 2, "item" : "jkl", "date" : ISODate("2014-02-03T09:00:00Z"), "price" : 20, "quantity" : 1 }
{ "_id" : 3, "item" : "xyz", "date" : ISODate("2014-02-03T09:05:00Z"), "price" : 5, "quantity" : 5 }
{ "_id" : 4, "item" : "abc", "date" : ISODate("2014-02-15T08:00:00Z"), "price" : 10, "quantity" : 10 }
{ "_id" : 5, "item" : "xyz", "date" : ISODate("2014-02-15T09:05:00Z"), "price" : 5, "quantity" : 10 }
{ "_id" : 6, "item" : "xyz", "date" : ISODate("2014-02-15T12:05:10Z"), "price" : 5, "quantity" : 5 }
{ "_id" : 7, "item" : "xyz", "date" : ISODate("2014-02-15T14:12:12Z"), "price" : 5, "quantity" : 10 }

With this index: {item : 1, date : 1}

And this query:

db.sales.aggregate(

   [
     { $sort: { item: 1, date: 1 } },
     {
       $group:
         {
           _id: "$item",
           lastSalesDate: { $last: "$date" }

         }
     }
   ]
)

we find that 7 keys are examined and 0 docs were examined. However if we run the following query with the same index:

db.sales.aggregate(
   [
     { $sort: { item: 1, date: 1 } },
     {
       $group:
         {
           _id: "$item",
           lastSalesDate: { $last: "$date" },
           lastPrice: {$last: "$price"}

         }
     }
   ]
)

and look at the profiler output, we find that 7 keys are examined and 7 docs are also examined. Is it really the case that first and last are necessarily inefficient? If so what are the common workarounds?

0 Answers
Related