Aggregation pipeline and indexes

aggregation-framework, indexing, mongodb

Solution

Generally, only pipeline operators that can be flattened to a normal query (`$match`, `$limit`, `$sort`, and `$skip`) will be able to use the indexes on a collection. This is one of the reasons the `$geoNear` operator added in 2.4 has to be at the start of the pipeline.

Once you mutate the documents with `$project`, `$group`, or `$unwind` the index is no longer valid/usable.

If you have an index on an array field you can still use it before the `$unwind` to speed up the selection of documents to pipeline and then further refine the selected documents with a second `$match`.

Consider documents like:

{ tags: [ 'cat', 'bird', 'blue' ] }

With an index on `tags`.

If you only wanted to group the tags starting with `b` then you could perform an aggregation like:

{ pipeline: [
      { $match : { tags : /^b/ } },
      { $unwind : '$tags' },
      { $match : { tags : /^b/ } },
      /* the rest */
  ] }

The first `$match` does the coarse grain match using the index on `tags`.

The second match after the `$unwind` won't be able to use the index (the document above is now 3 documents) but can evaluate each of those documents to filter out the extra documents that get created (to remove { tags : 'cat' } from the example).

HTH - Rob.

Problem

From http://docs.mongodb.org/manual/core/indexes/#multikey-indexes, it is possible to create an index on an array field using a multikey index. http://docs.mongodb.org/manual/applications/aggregation/#pipeline-operators-and-indexes lists some ways of how an index can be used in aggregation framework. However, there may be times that I may need to perform an `$unwind` on an array field to perform a `$group`. My question is, are multikey indexes (or any index using such array field) can still be used once they are operated on in the middle of the pipeline?

Original source