Hi, mongodb often use index which include the field not in the filter , and the field will using [MaxKey , MinKey] ,this is not the same as what I saw in the index prefix documentation.
Why it will use wrong index ?
the query like this :
db.Wager.aggregate([{“$match”: {“CompanyId”: 1341, “UpdatedDateUtc” : {“$gte” : ISODate(“2021-03-25T23:35:00Z”)}}},{$project:{“CompanyId”:1,“UpdatedDateUtc”:1,_id:0}},{“$group”: {“_id”: 1,“n”: {“$sum”: 1}}}])
I hope this can use this index :
{ “CompanyId”: -1, “UpdatedDateUtc” : -1 ,“WagerEventDateUtc” : -1 }
but it always will use index :
{ “CompanyId” : 1,“BetDateUtc” : -1, “WagerEventDateUtc” : -1, “UpdatedDateUtc” : -1}
there is the explain :
{ "nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 101,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION_COVERED",
"nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 101,
"needTime" : 0,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 2,
"isEOF" : 0,
"transformBy" : {
"CompanyId" : 1,
"UpdatedDateUtc" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 101,
"needTime" : 0,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 2,
"isEOF" : 0,
"keyPattern" : {
"CompanyId" : 1,
"BetDateUtc" : -1,
"WagerEventDateUtc" : -1,
"UpdatedDateUtc" : -1
},
"indexName" : "CompanyId_1_BetDateUtc_-1_WagerEventDateUtc_-1_UpdatedDateUtc_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"CompanyId" : [ ],
"BetDateUtc" : [ ],
"WagerEventDateUtc" : [ ],
"UpdatedDateUtc" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"CompanyId" : [
"[1341.0, 1341.0]"
],
"BetDateUtc" : [
"[MaxKey, MinKey]"
],
"WagerEventDateUtc" : [
"[MaxKey, MinKey]"
],
"UpdatedDateUtc" : [
"[new Date(9223372036854775807), new Date(1616715300000)]"
]
},
"keysExamined" : 101,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
} }},
{
"nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 101,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION_COVERED",
"nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 101,
"needTime" : 0,
"needYield" : 0,
"saveState" : 10242,
"restoreState" : 10242,
"isEOF" : 0,
"transformBy" : {
"CompanyId" : 1,
"UpdatedDateUtc" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 101,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 101,
"needTime" : 0,
"needYield" : 0,
"saveState" : 10242,
"restoreState" : 10242,
"isEOF" : 0,
"keyPattern" : {
"CompanyId" : -1,
"UpdatedDateUtc" : -1,
"WagerEventDateUtc" : -1
},
"indexName" : "CompanyId_-1_UpdatedDateUtc_-1_WagerEventDateUtc_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"CompanyId" : [ ],
"UpdatedDateUtc" : [ ],
"WagerEventDateUtc" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"CompanyId" : [
"[1341.0, 1341.0]"
],
"UpdatedDateUtc" : [
"[new Date(9223372036854775807), new Date(1616715300000)]"
],
"WagerEventDateUtc" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 101,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
} }}
I think mongodb choose wrong index were because “saveState” and “restoreState” too high , but I’m not sure because I don’t know what these two fields mean.