Getting confuse on the Compound Index prefixes

Working with Data
,
1

Im a bit confused on the behavior of compound index using prefix
let say my compound index is .createIndex({ a: 1, b: 1, c: 1 })
If my query is .find({ a: 'test' }).explain() it will give me on the IXSCAN stage a keysExamined let say 100
But if my query is this .find({ a: 'test', c: 'test' }).explain() Im expecting to get the same number of keysExamined because my query is not a prefix of my index
What I got on my 2nd query on IXSCAN stage was a lower number of keysExamined

Can you help me understand what was happened?

2

Hey there,

I’ve tried to reproduce the described behavior but I’m getting the same amount of keysExamined for both queries:

  1. db.test.insertMany([ {a:"test", b:"test", c:"test"}, {a:"test", b:"test", c:"test"}, {a:"test", b:"test", c:"test"}, {a:"test", b:"test", c:"test"}, {a:"test", b:"test", c:"test"}, {a:"test", b:"test", c:"test"}, {a:"foo", b:"bar", c:"yo"} ])

  2. db.test.createIndex({ a: 1, b: 1, c: 1 })

  3. db.test.find({ a: 'test' }).explain("executionStats") ==> "keysExamined" : 6

  4. db.test.find({ a: 'test', c: 'test' }).explain("executionStats") ==> "keysExamined" : 6

Could you please share your code so that I can have a look?

3

Hello Carl
Thanks for looking into this

So here is my index

db.getCollection("my-collection").createIndex(
    {
        type: 1,
        'details.category': 1,
        'details.description': 1,
        'details,overview': 1,
        'details.reference': 1
    })

And here is my first query

db.getCollection("my-collection").find({
    type: "core.module.safetyplan",
}).explain("executionStats")

Here is the executionStats

{
    "executionSuccess": true,
    "nReturned": 302.0,
    "executionTimeMillis": 0.0,
    "totalKeysExamined": 302.0,
    "totalDocsExamined": 302.0,
    "executionStages": {
        "stage": "FETCH",
        "nReturned": 302.0,
        "executionTimeMillisEstimate": 1.0,
        "works": 303.0,
        "advanced": 302.0,
        "needTime": 0.0,
        "needYield": 0.0,
        "saveState": 0.0,
        "restoreState": 0.0,
        "isEOF": 1.0,
        "docsExamined": 302.0,
        "alreadyHasObj": 0.0,
        "inputStage": {
            "stage": "IXSCAN",
            "nReturned": 302.0,
            "executionTimeMillisEstimate": 1.0,
            "works": 303.0,
            "advanced": 302.0,
            "needTime": 0.0,
            "needYield": 0.0,
            "saveState": 0.0,
            "restoreState": 0.0,
            "isEOF": 1.0,
            "keyPattern": {
                "type": 1.0,
                "details.category": 1.0,
                "details.description": 1.0,
                "details,overview": 1.0,
                "details.reference": 1.0
            },
            "indexName": "type_1_details.category_1_details.description_1_details,overview_1_details.reference_1",
            "isMultiKey": false,
            "multiKeyPaths": {
                "type": [],
                "details.category": [],
                "details.description": [],
                "details,overview": [],
                "details.reference": []
            },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2.0,
            "direction": "forward",
            "indexBounds": {
                "type": [
                    "[\"core.module.safetyplan\", \"core.module.safetyplan\"]"
                ],
                "details.category": [
                    "[MinKey, MaxKey]"
                ],
                "details.description": [
                    "[MinKey, MaxKey]"
                ],
                "details,overview": [
                    "[MinKey, MaxKey]"
                ],
                "details.reference": [
                    "[MinKey, MaxKey]"
                ]
            },
            "keysExamined": 302.0,
            "seeks": 1.0,
            "dupsTested": 0.0,
            "dupsDropped": 0.0
        }
    }
}

And when I add another field on my query

db.getCollection("my-collection").find({
    type: "core.module.safetyplan",
    'details.description': "This is long description."
}).explain("executionStats")

And here is the executionStats of my second query

{
    "executionSuccess": true,
    "nReturned": 21.0,
    "executionTimeMillis": 0.0,
    "totalKeysExamined": 26.0,
    "totalDocsExamined": 21.0,
    "executionStages": {
        "stage": "FETCH",
        "nReturned": 21.0,
        "executionTimeMillisEstimate": 0.0,
        "works": 26.0,
        "advanced": 21.0,
        "needTime": 4.0,
        "needYield": 0.0,
        "saveState": 0.0,
        "restoreState": 0.0,
        "isEOF": 1.0,
        "docsExamined": 21.0,
        "alreadyHasObj": 0.0,
        "inputStage": {
            "stage": "IXSCAN",
            "nReturned": 21.0,
            "executionTimeMillisEstimate": 0.0,
            "works": 26.0,
            "advanced": 21.0,
            "needTime": 4.0,
            "needYield": 0.0,
            "saveState": 0.0,
            "restoreState": 0.0,
            "isEOF": 1.0,
            "keyPattern": {
                "type": 1.0,
                "details.category": 1.0,
                "details.description": 1.0,
                "details,overview": 1.0,
                "details.reference": 1.0
            },
            "indexName": "type_1_details.category_1_details.description_1_details,overview_1_details.reference_1",
            "isMultiKey": false,
            "multiKeyPaths": {
                "type": [],
                "details.category": [],
                "details.description": [],
                "details,overview": [],
                "details.reference": []
            },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2.0,
            "direction": "forward",
            "indexBounds": {
                "type": [
                    "[\"core.module.safetyplan\", \"core.module.safetyplan\"]"
                ],
                "details.category": [
                    "[MinKey, MaxKey]"
                ],
                "details.description": [
                    "[\"This is long description.\", \"This is long description.\"]"
                ],
                "details,overview": [
                    "[MinKey, MaxKey]"
                ],
                "details.reference": [
                    "[MinKey, MaxKey]"
                ]
            },
            "keysExamined": 26.0,
            "seeks": 5.0,
            "dupsTested": 0.0,
            "dupsDropped": 0.0
        }
    }
}

As you can see the keysExamined decreased

4

Why do you think it is not normal?

When you make a more restrictive query it is kind of normal than less key or the same number of keys are examined.

In the first case, you start with 1 key for type:, then you recursively scan the keys with the same type prefix. So you scan all details.category that exists for the given type, and then all the details.descriptions for each details.category. In the second case you only have 1 details.description per details.category.

1 Like
5

Hello Steve
Can you elaborate more?

Because the keysExamined should be the same on both query because the second query in terms of prefix is same with the first query

As you can see with the result from @Carl_Champain that result was the same I was expecting

6

Do some documenta not have b set?

/Edit, never mind I tested a few scenarios and could not get it behave the way you are…do you have a sample from your data that can demo this?

I was thinking that if some document didnt have a field set on the second index match then it would be able to short-circuit within the lookup.

7

Carl_Champlain example was an edge case where all values were the same and both queries were returning the same number of documents. So the second query was not more selective as the first.

If you start with a slightly modified collection:

db.test.insertMany( [
    {a:"test", b:"test", c:"test"},
    {a:"test", b:"test", c:"test"},
    {a:"test", b:"test", c:"test"},
    {a:"test", b:"test", c:"test"},
    {a:"test", b:"test", c:"test"},
    {a:"test", b:"test", c:"zoo"}, /* in this document c: is zoo rather than test */
    {a:"foo", b:"bar", c:"yo"}
] )
db.test.createIndex( { a: 1, b: 1, c: 1 } )

Then

1 - db.test.find({ a: 'test'}) we get 6 keysExamined.
2 - db.test.find({ a: 'test', c: 'zoo' }) we get 2 keysExamined

2 Likes
8

Because the behavior that Im encountering contradicts to the index prefix concept in the mongodb manual
Do you know any articles/documents that fully explain this behavior?

9

Could you please provide more details about your observations that

I do not see any.

10

Is says there this

if a query omits a particular index prefix, it is unable to make use of any index fields that follow that prefix.

Since a query on item and stock omits the location index prefix, it cannot use the stock index field which follows location. Only the item field in the index can support this query. See Create Indexes to Support Your Queries for more information.

What I was expecting on the executionStats on explain is same number of keysExamined and it should have FETCH stage after the IXSCAN where it will filter the the keys/documents using details.description

11

On your example
I still dont understand why only 2 keysExamined in this query
db.test.find({ a: 'test', c: 'zoo' })

12

I did not see any contradiction in the link you shared because I read:

MongoDB can also use the index to support a query on the item and stock fields, since the item field corresponds to a prefix.

I could also read:

However, in this case the index would not be as efficient in supporting the query as it would be if the index were on only item and stock.

Simply because it examines only 1 instance of the key a:test/b:test/c:test while with c:test it has to examined once for each document with that keys.

Indexes are trees, when a node does not match branches do not need to be followed.

13

Maybe I’ll go deeper on how index works to be able to fully understand

Thanks