Mongo Partial Compound Unique Index | Not Used In Query

Question

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

Answer 1

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" }}]})