mongo: aggregate - $match before $project

Question

Having a mongodb with around 100GB of data and per field in the $match expression, i have an index (single field index).

Now I tried aggregate() and wrote $project as first part in the pipeline, $match behind this.

The aggregation runs and returns correct results, but it takes for hours! Does this really process the filtered ($match) data only or does mongo aggregate on the full range of data and filter afterwards?

In my test case, $match filters around 150MB (instead of the full data size of 100GB).

By accident, I changed the order and wrote $match before $project in the pipeline definition. This way, it was done within few seconds.

When does mongodb reduce the input data usually and does it also deal with the index for fields in $match?

Answer 1

As you have noticed, the order of pipeline operators is very crucial especially when dealing with large collection. If done incorrectly you can run out of memory let alone the process taking a long time. As noted in the docs:

The following pipeline operators take advantage of an index when they occur at the beginning of the pipeline:

$match 
$sort 
$limit 
$skip.

So as long as $match comes up front you index can be used. Also noted in the docs

The MongoDB aggregation pipeline streams MongoDB documents from one pipeline operator to the next to process the documents. Pipeline operators can be repeated in the pipe.

That means that your $project only sees a fraction of the entire collection if it is preceded by a $match.