4

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

Taha Samad
  • 1,135
  • 1
  • 9
  • 22
  • Well if there is only one item in the collection this is hardly surprising. The Atlas tool is probably not the best place to be looking anyway. Instead you should look at the full result in the shell with `.explain(true)`. This will likely show that even though the index was "considered" it was discarded in favor of a collection scan for the single result. – Neil Lunn Jul 05 '17 at 03:02
  • Actually there are multiple items in collections. As you can see documents examined:5. This happens on larger collections as well. – Taha Samad Jul 05 '17 at 03:12
  • 1
    Do you think you could show a more "complete" example then? And while you are at it, please actually use the `mongo` "shell" and include the "text" output here instead of pictures. Really hard to read pictures in a post, when "text" is so much clearer, and we can all copy and paste data and output. Which is something else you cannot do with pictures. – Neil Lunn Jul 05 '17 at 03:40

1 Answers1

5

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" }}]})
Stennie
  • 63,885
  • 14
  • 149
  • 175