Index is not used on OR(NOT Exists(col), col is NULL)

MongoDB Atlas
,
1

Hi, I have a collection with an index on { Review: 1}, and am trying to count the documents without a Review property or where the Review property is null.

Query is: "aggregate" ("pipeline": [{"$match": {"$or": [{"Review": {"$exists": false}},{"Review": null}]}},{ "$group": {"_id": {},"COUNT": {"$count": {}}}}

In the execution plan, it seems like the index is scanned, not seeked. Is there a way to get a seek out of this?

2

Please provide the plan. What do you mean by scanned vs seeked?

3

Hello, Bruno,

I attempted to replicate your scenario by generating a collection review containing various documents.

[
  {
    "_id": {
      "$oid": "65565c276a3d12785849fc55"
    },
    "review": "very good comment"
  },
  {
    "_id": {
      "$oid": "65565c276a3d12785849fc56"
    },
    "review": 1
  },
  {
    "_id": {
      "$oid": "65565c276a3d12785849fc57"
    },
    "review": null
  }
]

Subsequently, I established an index on the review property using the following syntax:

db.review.createIndex({review: 1})

Following this, I executed your query and examined the Explain Plan:

db.review.aggregate(
    [{ $match: { $or: [{ review: { $exists: false } }, { review: null }] } }, { $group: { _id: {}, "COUNT": { $count: {} } } }]
).explain('allPlansExecution')

Plan:

  "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                  "review": 1
                },
                "indexName": "review_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "review": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "review": [
                    "[undefined, undefined]",
                    "[null, null]"
                  ]
                }
              }

Your query optimally utilizes the index. According to the MongoDB documentation:

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. That is, for MongoDB to use indexes to evaluate an $or expression, all the clauses in the $or expression must be supported by indexes.

I hope this help you!

1 Like