CODEWITHSUNDEEP
mongodb
Taha Samad
Taha Samad

Reputation: 1145

Mongo Partial Compound Unique Index | Not Used In Query

I am facing a strange issue. I have a partial, compound, unique index with defination:

createIndex({a: 1, b:1, c: 1}, {unique:1, partialFilterExpression: {c: {$type: "string"}}})

Now when I perform a query this index is never used as per the explain plan. Even though there are document(s) matching the query.

Chaning same index to sparse instead of partial fixes the above issue, but sparse, compound, unique indexes have following issue: dealing-with-mongodb-unique-sparse-compound-indexes

Index Is Properly Created

Query returns a document

Index is not Used

Upvotes: 4

Views: 2239

Answers (1)

Stennie
Stennie

Reputation: 65323

As noted in the query coverage documentation for partial indexes:

MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.

To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition.

In your set up you create a partial index filtering on {c: {$type: "string"}}.

Your query conditions are {a:"1", b:"p", c:"2"}, or a query shape of three equality comparisons ({a: eq, b: eq, c: eq}). Since this query shape does not include a $type filter on c, the query planner has to consider that queries fitting the shape should match values of any data type and the partial index is not a viable candidate for complete results.

Some example queries that would use your partial index (tested with MongoDB 3.4.5):

// Search on {a, b} with c criteria matching the index filter
db.mydb.find({a:"1", b:"p", c: { $type: "string" } })

// Search on {a,b,c} and use $and to include the type of c
db.mydb.find({a:"1", b:"p", $and: [{ c: "2"} , {c: { $type: "string" }}]})

Upvotes: 5

Related Questions