'MongoDb Index Search returns the entire collection when using a string contains operation

Why would MongoDb (4.2.6) return every row from an index (Collation locale: en_US, strength: 1), when searching for a string contained in the document field? Example query:

db.eClearFaces.aggregate()
    .match({
        "Name": /Test/s
    })
    .collation({ locale: "en_US", "strength": 1 })

The index it is using is:

enter image description here

Name is simply a string field on the document. Resulting query plan shows that every single record in the collection is returned: enter image description here

You can see in stage IXSCAN, it returned 56k+ docs (where I expected it to return only 6). That caused the next stage to fetch all 56k docs, but out of those fetched, it returned 6 (the correct count).

I am confused on why - I have both the Collation for the Index and Query configured the same, and its obviously hitting the index. I don't understand why its returning all those extras rows to the next stage.

Index output from profiler:

enter image description here

Did I miss a MongoDb Index or Query fundamental?

mongodbquery-optimization


Solution 1:[1]

The solution ended up being in the MongoDb docs.

The $regex implementation is not collation-aware and is unable to utilize case-insensitive indexes.

https://www.mongodb.com/docs/manual/reference/operator/query/regex/

So, supplying the collation ended up hindering the performance. Solution was to have an index without collation. It still performed an index scan as expected, but resulted in far fewer results before it fetched from the table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TravisWhidden

Related Questions