Mongodb not follow prefix rule

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.

Hi @111148_1,

Welcome to MongoDB community.

Both of those indexes can be used for this query as both of them cover the searched fields.

MongoDB uses an empirical engine to run all candidates and the return first is the one choosen.

The explain plans show same works and same execution times so I don’t see why you will prefer one over the other.

If you wish to force an index use a hint on the query.

Thanks
Pavel