MongoDB – Which Query Performs Better: $or or $facet?

indexmongodbquery-performance

I have a query similar to the following;

    db.getCollection('list').aggregate([
    {
        $facet: {
            "events":[{
                $match: {
                   'type': 'Event'
                }
            }],
            "tasks": [{
                $match: {
                   'type': 'Task'
                }
            }]
        }
    },
    {$project: {activity:{$setUnion:['$events','$tasks']}}},
    {$unwind: '$activity'},
    {$replaceRoot: { newRoot: "$activity" }}
]);

One another easier approach to write this query is by using $or operator.

    db.getCollection('list').aggregate([{
    $match: {
        $or: [{
            type: 'Event'
        }, {
            type: 'Task'
        }]
    }
}]);

Source: https://gist.github.com/cthurston/7aead8229e10caa0be175babf7e8ddf1

The query that I'm working with is very similar, except I have other fields, not just 'type' going through this example. My idea is that, if I use $facet I'd get better performance if I create correct indexes for each query (that is inside $facet). On the other hand, if I use $or query as it is in the example, Mongo may struggle while trying to find the correct index for the query. Is this how it works?

Edit: It seems MongoDB can handle indexes for $or statements;

https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes

Best Answer

$facet - Index Use says:

The $facet stage, and its sub-pipelines, cannot make use of indexes, even if its sub-pipelines use $match or if $facet is the first stage in the pipeline. The $facet stage will always perform a COLLSCAN during execution.

$or Clauses and Indexes says:

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans.


And, your query's $match stage:

$match: {
    $or: [
        { type: 'Event' }, 
        { type: 'Task' }
    ]
  }

can be simply written as:

$match: { type: { $in: [ 'Event', 'Task' ] } }

See $or versus $in says:

When using $or with that are equality checks for the value of the same field, use the $in operator instead of the $or operator.

Using the $or with proper indexing is likely to perform better than that of using the $facet stage.

Related Question