Mongo IndexBounds doubts

Hi everyone!

I have some doubts about indexBounds.
I have a database and I want to do a search for a range of ids.
I noticed that the query is slow when I use some ranges of ids and the indexBounds presents the value “-inf.0”, which I don’t understand the reason.

Case 01
db.collection.find({“productId”: {"$gt": 479894,"$lte": 479995}}).explain(“executionStats”);

Query time: 14s

"indexBounds" : {
    "productId" : [
        "(479894.0, inf.0]"
    ]
}

ExecutionStats:
"nReturned" : 16782,
"executionTimeMillis" : 10140,
"totalKeysExamined" : 741071,
"totalDocsExamined" : 399367

Case 02
db.collection.find({“productId”: {"$gt": 0,"$lte": 100000}}).explain(“executionStats”);

Query time: 0.36s

"indexBounds" : {
    "productId" : [
        "[-inf.0, 100000.0]"
    ]
}

ExecutionStats:
"nReturned" : 15087,
"executionTimeMillis" : 175,
"totalKeysExamined" : 25925,
"totalDocsExamined" : 15087,

Case 03
db.collection.find({“productId”: {"$gt": 1479894,"$lte": 2479894}}).explain(“executionStats”);

Query time: 6.15s

"indexBounds" : {
    "productId" : [
        "[-inf.0, 2479894.0]"
    ]
}

ExecutionStats:
"nReturned" : 186809,
"executionTimeMillis" : 5618,
"totalKeysExamined" : 336759,
"totalDocsExamined" : 186809,

Note that the number of keys examined in case 01 is much higher than in case 02 even though the range is much lower.

If anyone can help understand these scenarios.

Hi @Andre_Luiz,

Can you share an entire winning exec plan?

My guess would be that the index is either ascending or descending so the index bounds takes only one of the 2 conditions and then their is another filter later in the query plan to resolve the other condition.

Which version of MongoDB is this?

Cheers,
Maxime.

Hi @MaBeuLux88 !!

4.4.14

WinningPlan:

 "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "products.productInfo.productId" : {
                    "$lte" : 479899.0
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "products.productInfo.productId" : 1
                },
                "indexName" : "products.productInfo.productId",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "products.productInfo.productId" : [ 
                        "products"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "products.productInfo.productId" : [ 
                        "(479894.0, inf.0]"
                    ]
                }
            }
        }

Yes, so because your index is ascending,

  • First the index resolves the $gt part of your query.
  • Then the docs are fetched from disc using the filter $lte: XXX to resolve the remaining part of your query.

LGTM

Is there any way to solve it, with compound indexes?

I have already seen that before. Therefore the “LGTM”.
But thing is, I can’t reproduce this behaviour in 5.0.8. So maybe it’s the version diff, or maybe it’s something else…

db.coll.drop()
db.coll.createIndex({n:1})
db.coll.insertMany([{n:1},{n:2},{n:3},{n:4},{n:5},{n:6},{n:7},{n:8},{n:9},{n:10}])
db.coll.find({n:{$gt: 4, $lte: 8}}).explain(true)

Output:

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'test.coll',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [ { n: { '$lte': 8 } }, { n: { '$gt': 4 } } ]
    },
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { n: 1 },
        indexName: 'n_1',
        isMultiKey: false,
        multiKeyPaths: { n: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { n: [ '(4, 8]' ] }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 4,
    executionTimeMillis: 0,
    totalKeysExamined: 4,
    totalDocsExamined: 4,
    executionStages: {
      stage: 'FETCH',
      nReturned: 4,
      executionTimeMillisEstimate: 0,
      works: 5,
      advanced: 4,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      docsExamined: 4,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 4,
        executionTimeMillisEstimate: 0,
        works: 5,
        advanced: 4,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: { n: 1 },
        indexName: 'n_1',
        isMultiKey: false,
        multiKeyPaths: { n: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { n: [ '(4, 8]' ] },
        keysExamined: 4,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    },
    allPlansExecution: []
  },
  command: {
    find: 'coll',
    filter: { n: { '$gt': 4, '$lte': 8 } },
    '$db': 'test'
  },
  serverInfo: {
    host: 'hafx',
    port: 27017,
    version: '5.0.8',
    gitVersion: 'c87e1c23421bf79614baf500fda6622bd90f674e'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1653497779, i: 1 }),
    signature: {
      hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
      keyId: Long("0")
    }
  },
  operationTime: Timestamp({ t: 1653497779, i: 1 })
}

Can you test the same query in 5.0.8 in the same conditions (more or less) and can you reproduce this behaviour?

Note: the fact that this is a multikey index and I’m not using one might be part of the problem here.

Cheers,
Maxime.

In your explain you have this

 "stage" : "IXSCAN",
                "keyPattern" : {
                    "products.productInfo.productId" : 1
                },
                "indexName" : "products.productInfo.productId",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "products.productInfo.productId" : [ 
                        "products"
                    ]
                },

Which is a multiKey index meaning this is an index on an array. Your Quey you say is

db.collection.find({“productId”: {"$gt": 1479894,"$lte": 2479894}}).explain(“executionStats”);

But presumably it is

db.collection.find({“"products.productInfo.productId" ”: {"$gt": 1479894,"$lte": 2479894}}).explain(“executionStats”);

This query is not looking for what you think it is as it is looking for a record where “products.productInfo.productId” is >1479894 and “products.productInfo.productId” < 2479894 but those two clauses can be true for different elements in the array - thus it needs to find where one clause is true then fetch the record to look at all the other values in the array.

If you want these to apply to the same value (and use the index) you need to use $elemMatch

2 Likes

Yup, John is totally right @Andre_Luiz. I hope that makes sense!

Simulate according to my environment and the array actually generates this behavior.

db.testeRange.drop()
db.testeRange.createIndex({"n.id":1, "n.id":-1})
db.testeRange.insertMany([{"n": [{"id":1}]},{"n": [{"id":3}]},{"n": [{"id":4}]}])
db.testeRange.find({"n.id":{$gt: 4, $lte: 8}}).explain(true)

Output

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "display.testeRange",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "n.id" : {
                        "$lte" : 8.0
                    }
                }, 
                {
                    "n.id" : {
                        "$gt" : 4.0
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "n.id" : {
                    "$lte" : 8.0
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "n.id" : -1.0
                },
                "indexName" : "n.id_-1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "n.id" : [ 
                        "n"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "n.id" : [ 
                        "[inf.0, 4.0)"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "n.id" : {
                        "$gt" : 4.0
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "n.id" : -1.0
                    },
                    "indexName" : "n.id_-1",
                    "isMultiKey" : true,
                    "multiKeyPaths" : {
                        "n.id" : [ 
                            "n"
                        ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "n.id" : [ 
                            "[8.0, -inf.0]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "n.id" : {
                    "$lte" : 8.0
                }
            },
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 0,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 0,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 0,
                "works" : 1,
                "advanced" : 0,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "n.id" : -1.0
                },
                "indexName" : "n.id_-1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "n.id" : [ 
                        "n"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "n.id" : [ 
                        "[inf.0, 4.0)"
                    ]
                },
                "keysExamined" : 0,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "operationTime" : Timestamp(1653498607, 11)
}

I’m going to do some tests this way.
Thanks @AdventureMaker .

1 Like

@AdventureMaker , elemMatch worked for me.

Thanks!!!

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.