Confirmation about the behaviour of partial indexes and hints

[Rhys Campbell]

Hi,

I just want to confirm something about partial indexes and hints if possible. I've only done a single quick test on a small amount of data but, if I'm reading the explain stats correctly, I'm correct in thinking this may be the answer to a problem we've been having with a query on MongoDB.

Partial Index Definition

db.collection.createIndex({ "a": 1, "a": 1, "c": 1, "d": -1 },  { "partialFilterExpression": { "c": 20 } });

I wanted to know if MongoDB was smart enough to know, that if a collection didn't have a document that matched the filter expression, if it would know it could simply check this index. The answer seems to be "Yes"...

For example. The executionStats show the following details...

When 0 documents are present 

"totalKeysExamined" : 0,
"totalDocsExamined" : 0,

When 1 document is present...

"totalKeysExamined" : 1,
"totalDocsExamined" : 1,

When 30+ documents are present...

"totalKeysExamined" : 1,
"totalDocsExamined" : 1,

This is for queries of the following type...

db.messages.find({ a: "value1",
   b: "value1",
   d: { $gt: new Date(1449906272490) },
   c: 20 }).sort({d:-1}).limit(1).hint("name_of_index")

I've played with this a little and MongoDB scan a different number of documents depending on the index used. The partial index would be the optimal ones where c: 20.

So my question would be is MongoDB smart enough to know that if can just scan this index for documents where c: 20? I think I probably need to add a hint to enforce this behaviour but a confirmation would be nice.

Cheers,

Rhys

[Kevin Adistambha]

Hi Rhys

I wanted to know if MongoDB was smart enough to know, that if a collection didn’t have a document that matched the filter expression, if it would know it could simply check this index. The answer seems to be “Yes”…

Technically the answer is a partial “yes”. This is because the query planner:

• Will not choose a partial index if it will result in an incomplete result set (https://docs.mongodb.com/manual/core/index-partial/#query-coverage)
• Will examine if any other index can satisfy the query based on the query shape (https://docs.mongodb.com/manual/core/query-plans/)

Therefore a more complete answer is: MongoDB is smart enough to know that a partial index can satsify a given query and can return a complete result set. However, whether the partial index is actually used for the query or not depends on the existence of other indexes in the collection that can also satisfy the query.

This is because there could be no guarantee that a given partial index is the fastest one, since this would be use-case specific (i.e. it would largely depend on the distribution of the key fields in the collection). In some cases, the partial index would be the fastest, and in other cases, it may not be.

So my question would be is MongoDB smart enough to know that if can just scan this index for documents where c: 20? I think I probably need to add a hint to enforce this behaviour but a confirmation would be nice.

In this case, if you know that the partial index is the best index given the document distribution in your collection, it’s best to provide a hint() term to your query to make sure that the query planner use the desired index.

Best regards,
Kevin

​

[Rhys Campbell]

Thanks Kevin, just a clarification...

"can return a complete result set"

 I'm more interested to know if MongoDB is smart enough to be able to determine the abscene of a value from the index. As I said, it seemed to be (on my limited tests), for my use case this could be great as it should be blazing fast.

Cheers,

Rhys

[Kevin Adistambha]

Hi Rhys

Sorry for the late reply.

I’m more interested to know if MongoDB is smart enough to be able to determine the abscene of a value from the index.

I’m a bit unclear about your question. Could you give some example of what you meant?

With regard to partial indexes, MongoDB is aware whether using the index will return a complete result set or not. The partial index would only be considered for the query if MongoDB knows that the partial index will cover the entirety of the result set. You can of course force MongoDB to use the partial index anyway using hint(), but your result set may be missing some documents.

Best regards,
Kevin

​

[Rhys Campbell]

Sorry, there's a typo there; "abscene" shoudl have been "absence". What you've said seems to confirm what I think and that the hint is required for this

What I mean is...

If I create the following index...

db.collection.createIndex({"user": 1, "a":1}, { partialFilterExpression: { a: { $eq: 5 } } });

The index will therefore only contain entries where { "a": 5 } for each individual user and, at least in principle, the following query...

db.collection.count({ "user": "rhys", "a": 5 });

MongoDB could decide to simply scan the filtered index to determine that there were 0 for the user "rhys" where a = 5 rather than using a different index or scanning the entire collection. In this case we can be sure of the resultset not being incomplete since we are interested in documents where a = 5. 

The reason I ask about this is because this would be an ideal way to solve a minor performance problem in our MongoDB Cluster. The partial filter would only index a very small amount of documents and the resulting queries should be really fast. Adding the hint will force mongo to only consider these documents and we'll get the correct answer of 0 when the user has no documents where a = 5.

Hope that makes sense. Thanks for the confirmation.

[Kevin Adistambha]

Hi Rhys

MongoDB could decide to simply scan the filtered index to determine that there were 0 for the user “rhys” where a = 5 rather than using a different index or scanning the entire collection. In this case we can be sure of the resultset not being incomplete since we are interested in documents where a = 5.

Yes, the partial expression is tested against the query to ensure that the query is a subset of the partial expression. If this test is passed, this means that the partial index can return a complete result set.

Adding the hint will force mongo to only consider these documents and we’ll get the correct answer of 0 when the user has no documents where a = 5.

I believe you are correct. Be careful with hint()-ing a partial index though, since when you specify a hint(), the subset check result is ignored. So the caveat is, only use hint() with a partial index when you’re certain that the query forms a subset of the partial expression.

Hope this helps.

Best regards,
Kevin

​

[Rhys Campbell]

Great! Thanks Kevin.