[SERVER-43671] Adding an index ends up making my query heavier Created: 27/Sep/19  Updated: 01/Oct/19  Resolved: 01/Oct/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 3.4.19
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Frederic Maillasson Assignee: Carl Champain (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-19114 Rooted $or queries with a sort may se... Backlog
Operating System: ALL
Steps To Reproduce:

Set-up:

  • Run MongoDB 3.4 (but I'm facing the same issue when my application is running on MongoDB 3.6)
  • db.createCollection("agendaJobs")
  • db.agendaJobs.createIndex( {"name":1,"nextRunAt":1,"priority":-1,"lockedAt":1,"disabled":1}

    )

  • Insert a bunch of documents:

    var yesterday = Date.now() - 86400000; 
    var bulk = db.agendaJobs.initializeUnorderedBulkOp(); 
    for (var i = 0; i < 100000; i++) {
        bulk.insert({
        "name" : "testJob", 
        "data" : {}, 
        "type" : "normal", 
        "priority" : 0, 
        "nextRunAt" : new Date(yesterday + _rand()*10000), "lastModifiedBy" : null });
    } 
     bulk.execute();
    

  • Get the execution stats of the following query:

    db.agendaJobs.explain("executionStats").findAndModify(
    {query: { $or: [ 
    { name: "testJob", lockedAt: null, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, 
    { name: "testJob", lockedAt: { $exists: false }, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, 
    { name: "testJob", lockedAt: { $lte: new Date() }, disabled: { $ne: true } } 
    ] }, 
    update: {"$set": {fma: 1}}, 
    sort: { nextRunAt: 1, priority: -1 }})
    

 
Reproduce the issue:

  • db.agendaJobs.createIndex({name: 1, lockedAt: 1, disabled: 1})
  • Get the execution stats of the same query:

    db.agendaJobs.explain("executionStats").findAndModify(
    {query: { $or: [ 
    { name: "testJob", lockedAt: null, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, 
    { name: "testJob", lockedAt: { $exists: false }, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, 
    { name: "testJob", lockedAt: { $lte: new Date() }, disabled: { $ne: true } } 
    ] }, 
    update: {"$set": {fma: 1}}, 
    sort: { nextRunAt: 1, priority: -1 }})
    })
    

Participants:

 Description   

I wouldn't really call that a bug, but I just stumbled upon a case in which adding an index actually degraded the performance of a query. So I guessed this might be interesting for you to know about that case.

For your information, the queries below come from the library Agenda (https://github.com/agenda/agenda), which is a job scheduler backed by MongoDB.

 

I have a collection named "agendaJobs"; each document in this collection has 5 fields:

  • name
  • nextRunAt
  • priority
  • lockedAt
  • disabled

This collection has the following index:

{{

{"name":1,"nextRunAt":1,"priority":1,"lockedAt":1,"disabled":1}

}}

The following query is performed on a regular basis (to "pull" some jobs that need to be executed):

db.agendaJobs.explain("executionStats").findAndModify({
    query: { $or: [ 
        { name: "testJob"}, 
        {lockedAt: null}, 
        {nextRunAt: { $lte: new Date() }}, 
        {disabled: { $ne: true } }, 
        { name: "testJob"}, 
        {lockedAt: { $exists: false }}, 
        {nextRunAt: { $lte: new Date() }}, 
        {disabled: { $ne: true }}, 
        { name: "testJob"}, 
        {lockedAt: { $lte: new Date() }}, 
        {disabled: { $ne: true }}  
    ]}, 
    update: {"$set": {fma: 1}}, 
    sort: { nextRunAt: 1, priority: -1 }
})

 

Whenever I populate my collections with a lot of documents for which nextRunAt < current date, I see that the query is doing a full index scan:

# First, create many document with nextRunAt < current date
> var yesterday = Date.now() - 86400000; var bulk = db.agendaJobs.initializeUnorderedBulkOp(); for (var i = 0; i < 100000; i++) {bulk.insert({"name" : "testJob", "data" : {}, "type" : "normal", "priority" : 0, "nextRunAt" : new Date(yesterday + _rand()*10000), "lastModifiedBy" : null });} bulk.execute();
 
# Then run the query and gets the execution stats
> db.agendaJobs.explain("executionStats").findAndModify({query: { $or: [ { name: "testJob", lockedAt: null, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, { name: "testJob", lockedAt: { $exists: false }, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, { name: "testJob", lockedAt: { $lte: new Date() }, disabled: { $ne: true } } ] }, update: {"$set": {fma: 1}}, sort: { nextRunAt: 1, priority: -1 }})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "mg-viking.agendaJobs",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$or" : [
				{
					"$and" : [
						{
							"lockedAt" : {
								"$eq" : null
							}
						},
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"nextRunAt" : {
								"$lte" : ISODate("2019-09-27T09:29:36.952Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						}
					]
				},
				{
					"$and" : [
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"lockedAt" : {
								"$lte" : ISODate("2019-09-27T09:29:36.952Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						}
					]
				},
				{
					"$and" : [
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"nextRunAt" : {
								"$lte" : ISODate("2019-09-27T09:29:36.952Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						},
						{
							"$nor" : [
								{
									"lockedAt" : {
										"$exists" : true
									}
								}
							]
						}
					]
				}
			]
		},
		"winningPlan" : {
			"stage" : "UPDATE",
			"inputStage" : {
				"stage" : "SUBPLAN",
				"inputStage" : {
					"stage" : "LIMIT",
					"limitAmount" : 1,
					"inputStage" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "SORT_MERGE",
							"sortPattern" : {
								"nextRunAt" : 1,
								"priority" : -1
							},
							"inputStages" : [
								{
									"stage" : "FETCH",
									"filter" : {
										"lockedAt" : {
											"$eq" : null
										}
									},
									"inputStage" : {
										"stage" : "IXSCAN",
										"keyPattern" : {
											"name" : 1,
											"nextRunAt" : 1,
											"priority" : -1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "findAndLockNextJobIndex",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"nextRunAt" : [ ],
											"priority" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"nextRunAt" : [
												"(true, new Date(1569576576952)]"
											],
											"priority" : [
												"[MaxKey, MinKey]"
											],
											"lockedAt" : [
												"[null, null]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										}
									}
								},
								{
									"stage" : "IXSCAN",
									"keyPattern" : {
										"name" : 1,
										"nextRunAt" : 1,
										"priority" : -1,
										"lockedAt" : 1,
										"disabled" : 1
									},
									"indexName" : "findAndLockNextJobIndex",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"name" : [ ],
										"nextRunAt" : [ ],
										"priority" : [ ],
										"lockedAt" : [ ],
										"disabled" : [ ]
									},
									"isUnique" : false,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"name" : [
											"[\"testJob\", \"testJob\"]"
										],
										"nextRunAt" : [
											"[MinKey, MaxKey]"
										],
										"priority" : [
											"[MaxKey, MinKey]"
										],
										"lockedAt" : [
											"(true, new Date(1569576576952)]"
										],
										"disabled" : [
											"[MinKey, true)",
											"(true, MaxKey]"
										]
									}
								},
								{
									"stage" : "FETCH",
									"filter" : {
										"$nor" : [
											{
												"lockedAt" : {
													"$exists" : true
												}
											}
										]
									},
									"inputStage" : {
										"stage" : "IXSCAN",
										"keyPattern" : {
											"name" : 1,
											"nextRunAt" : 1,
											"priority" : -1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "findAndLockNextJobIndex",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"nextRunAt" : [ ],
											"priority" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"nextRunAt" : [
												"(true, new Date(1569576576952)]"
											],
											"priority" : [
												"[MaxKey, MinKey]"
											],
											"lockedAt" : [
												"[null, null]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										}
									}
								}
							]
						}
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 201,
		"totalKeysExamined" : 10002,
		"totalDocsExamined" : 4,
		"executionStages" : {
			"stage" : "UPDATE",
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 205,
			"works" : 10005,
			"advanced" : 1,
			"needTime" : 10003,
			"needYield" : 0,
			"saveState" : 81,
			"restoreState" : 81,
			"isEOF" : 1,
			"invalidates" : 0,
			"nMatched" : 1,
			"nWouldModify" : 1,
			"nInvalidateSkips" : 0,
			"wouldInsert" : false,
			"fastmodinsert" : false,
			"inputStage" : {
				"stage" : "SUBPLAN",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 205,
				"works" : 10004,
				"advanced" : 1,
				"needTime" : 10003,
				"needYield" : 0,
				"saveState" : 82,
				"restoreState" : 82,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "LIMIT",
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 205,
					"works" : 10004,
					"advanced" : 1,
					"needTime" : 10003,
					"needYield" : 0,
					"saveState" : 82,
					"restoreState" : 82,
					"isEOF" : 1,
					"invalidates" : 0,
					"limitAmount" : 1,
					"inputStage" : {
						"stage" : "FETCH",
						"nReturned" : 1,
						"executionTimeMillisEstimate" : 205,
						"works" : 10004,
						"advanced" : 1,
						"needTime" : 10003,
						"needYield" : 0,
						"saveState" : 82,
						"restoreState" : 82,
						"isEOF" : 0,
						"invalidates" : 0,
						"docsExamined" : 1,
						"alreadyHasObj" : 1,
						"inputStage" : {
							"stage" : "SORT_MERGE",
							"nReturned" : 1,
							"executionTimeMillisEstimate" : 205,
							"works" : 10004,
							"advanced" : 1,
							"needTime" : 10003,
							"needYield" : 0,
							"saveState" : 82,
							"restoreState" : 82,
							"isEOF" : 0,
							"invalidates" : 0,
							"sortPattern" : {
								"nextRunAt" : 1,
								"priority" : -1
							},
							"dupsTested" : 3,
							"dupsDropped" : 1,
							"inputStages" : [
								{
									"stage" : "FETCH",
									"filter" : {
										"lockedAt" : {
											"$eq" : null
										}
									},
									"nReturned" : 1,
									"executionTimeMillisEstimate" : 104,
									"works" : 1,
									"advanced" : 1,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 82,
									"restoreState" : 82,
									"isEOF" : 0,
									"invalidates" : 0,
									"docsExamined" : 1,
									"alreadyHasObj" : 0,
									"inputStage" : {
										"stage" : "IXSCAN",
										"nReturned" : 1,
										"executionTimeMillisEstimate" : 104,
										"works" : 1,
										"advanced" : 1,
										"needTime" : 0,
										"needYield" : 0,
										"saveState" : 82,
										"restoreState" : 82,
										"isEOF" : 0,
										"invalidates" : 0,
										"keyPattern" : {
											"name" : 1,
											"nextRunAt" : 1,
											"priority" : -1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "findAndLockNextJobIndex",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"nextRunAt" : [ ],
											"priority" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"nextRunAt" : [
												"(true, new Date(1569576576952)]"
											],
											"priority" : [
												"[MaxKey, MinKey]"
											],
											"lockedAt" : [
												"[null, null]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										},
										"keysExamined" : 1,
										"seeks" : 1,
										"dupsTested" : 0,
										"dupsDropped" : 0,
										"seenInvalidated" : 0
									}
								},
								{
									"stage" : "IXSCAN",
									"nReturned" : 0,
									"executionTimeMillisEstimate" : 101,
									"works" : 10000,
									"advanced" : 0,
									"needTime" : 9999,
									"needYield" : 0,
									"saveState" : 82,
									"restoreState" : 82,
									"isEOF" : 1,
									"invalidates" : 0,
									"keyPattern" : {
										"name" : 1,
										"nextRunAt" : 1,
										"priority" : -1,
										"lockedAt" : 1,
										"disabled" : 1
									},
									"indexName" : "findAndLockNextJobIndex",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"name" : [ ],
										"nextRunAt" : [ ],
										"priority" : [ ],
										"lockedAt" : [ ],
										"disabled" : [ ]
									},
									"isUnique" : false,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"name" : [
											"[\"testJob\", \"testJob\"]"
										],
										"nextRunAt" : [
											"[MinKey, MaxKey]"
										],
										"priority" : [
											"[MaxKey, MinKey]"
										],
										"lockedAt" : [
											"(true, new Date(1569576576952)]"
										],
										"disabled" : [
											"[MinKey, true)",
											"(true, MaxKey]"
										]
									},
									"keysExamined" : 9999,
									"seeks" : 10000,
									"dupsTested" : 0,
									"dupsDropped" : 0,
									"seenInvalidated" : 0
								},
								{
									"stage" : "FETCH",
									"filter" : {
										"$nor" : [
											{
												"lockedAt" : {
													"$exists" : true
												}
											}
										]
									},
									"nReturned" : 2,
									"executionTimeMillisEstimate" : 0,
									"works" : 2,
									"advanced" : 2,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 82,
									"restoreState" : 82,
									"isEOF" : 0,
									"invalidates" : 0,
									"docsExamined" : 2,
									"alreadyHasObj" : 0,
									"inputStage" : {
										"stage" : "IXSCAN",
										"nReturned" : 2,
										"executionTimeMillisEstimate" : 0,
										"works" : 2,
										"advanced" : 2,
										"needTime" : 0,
										"needYield" : 0,
										"saveState" : 82,
										"restoreState" : 82,
										"isEOF" : 0,
										"invalidates" : 0,
										"keyPattern" : {
											"name" : 1,
											"nextRunAt" : 1,
											"priority" : -1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "findAndLockNextJobIndex",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"nextRunAt" : [ ],
											"priority" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"nextRunAt" : [
												"(true, new Date(1569576576952)]"
											],
											"priority" : [
												"[MaxKey, MinKey]"
											],
											"lockedAt" : [
												"[null, null]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										},
										"keysExamined" : 2,
										"seeks" : 1,
										"dupsTested" : 0,
										"dupsDropped" : 0,
										"seenInvalidated" : 0
									}
								}
							]
						}
					}
				}
			}
		}
	},
	"serverInfo" : {
		"host" : "localhost.localdomain",
		"port" : 27017,
		"version" : "3.4.19",
		"gitVersion" : "a2d97db8fe449d15eb8e275bbf318491781472bf"
	},
	"ok" : 1
}

 

I was trying to work around that. It looks like the third arm of the $or is actually the one that makes the full index scan necessary; if I remove it, the query only scans a couple of index keys.

So I tried to optimize this part by adding the following index:

db.agendaJobs.createIndex({name: 1, lockedAt: 1, disabled: 1})

After that, running the same query gives a completely different execution plans that seems worse (200k documents scanned for this query, while the previous query only scanned 4 documents!):

> db.agendaJobs.explain("executionStats").findAndModify({query: { $or: [ { name: "testJob", lockedAt: null, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, { name: "testJob", lockedAt: { $exists: false }, nextRunAt: { $lte: new Date() }, disabled: { $ne: true } }, { name: "testJob", lockedAt: { $lte: new Date() }, disabled: { $ne: true } } ] }, update: {"$set": {fma: 1}}, sort: { nextRunAt: 1, priority: -1 }})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "mg-viking.agendaJobs",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$or" : [
				{
					"$and" : [
						{
							"lockedAt" : {
								"$eq" : null
							}
						},
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"nextRunAt" : {
								"$lte" : ISODate("2019-09-27T09:33:54.221Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						}
					]
				},
				{
					"$and" : [
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"lockedAt" : {
								"$lte" : ISODate("2019-09-27T09:33:54.221Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						}
					]
				},
				{
					"$and" : [
						{
							"name" : {
								"$eq" : "testJob"
							}
						},
						{
							"nextRunAt" : {
								"$lte" : ISODate("2019-09-27T09:33:54.221Z")
							}
						},
						{
							"$nor" : [
								{
									"disabled" : {
										"$eq" : true
									}
								}
							]
						},
						{
							"$nor" : [
								{
									"lockedAt" : {
										"$exists" : true
									}
								}
							]
						}
					]
				}
			]
		},
		"winningPlan" : {
			"stage" : "UPDATE",
			"inputStage" : {
				"stage" : "SUBPLAN",
				"inputStage" : {
					"stage" : "SORT",
					"sortPattern" : {
						"nextRunAt" : 1,
						"priority" : -1
					},
					"limitAmount" : 1,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"inputStage" : {
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "OR",
								"inputStages" : [
									{
										"stage" : "FETCH",
										"filter" : {
											"$or" : [
												{
													"$nor" : [
														{
															"lockedAt" : {
																"$exists" : true
															}
														}
													]
												},
												{
													"lockedAt" : {
														"$eq" : null
													}
												}
											]
										},
										"inputStage" : {
											"stage" : "IXSCAN",
											"keyPattern" : {
												"name" : 1,
												"nextRunAt" : 1,
												"priority" : -1,
												"lockedAt" : 1,
												"disabled" : 1
											},
											"indexName" : "findAndLockNextJobIndex",
											"isMultiKey" : false,
											"multiKeyPaths" : {
												"name" : [ ],
												"nextRunAt" : [ ],
												"priority" : [ ],
												"lockedAt" : [ ],
												"disabled" : [ ]
											},
											"isUnique" : false,
											"isSparse" : false,
											"isPartial" : false,
											"indexVersion" : 2,
											"direction" : "forward",
											"indexBounds" : {
												"name" : [
													"[\"testJob\", \"testJob\"]"
												],
												"nextRunAt" : [
													"(true, new Date(1569576834221)]"
												],
												"priority" : [
													"[MaxKey, MinKey]"
												],
												"lockedAt" : [
													"[null, null]"
												],
												"disabled" : [
													"[MinKey, true)",
													"(true, MaxKey]"
												]
											}
										}
									},
									{
										"stage" : "IXSCAN",
										"keyPattern" : {
											"name" : 1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "name_1_lockedAt_1_disabled_1",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"lockedAt" : [
												"(true, new Date(1569576834221)]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										}
									}
								]
							}
						}
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 496,
		"totalKeysExamined" : 100000,
		"totalDocsExamined" : 200000,
		"executionStages" : {
			"stage" : "UPDATE",
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 381,
			"works" : 100005,
			"advanced" : 1,
			"needTime" : 100003,
			"needYield" : 0,
			"saveState" : 798,
			"restoreState" : 798,
			"isEOF" : 1,
			"invalidates" : 0,
			"nMatched" : 1,
			"nWouldModify" : 1,
			"nInvalidateSkips" : 0,
			"wouldInsert" : false,
			"fastmodinsert" : false,
			"inputStage" : {
				"stage" : "SUBPLAN",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 415,
				"works" : 100004,
				"advanced" : 1,
				"needTime" : 100003,
				"needYield" : 0,
				"saveState" : 799,
				"restoreState" : 799,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "SORT",
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 370,
					"works" : 100004,
					"advanced" : 1,
					"needTime" : 100003,
					"needYield" : 0,
					"saveState" : 794,
					"restoreState" : 794,
					"isEOF" : 1,
					"invalidates" : 0,
					"sortPattern" : {
						"nextRunAt" : 1,
						"priority" : -1
					},
					"memUsage" : 129,
					"memLimit" : 33554432,
					"limitAmount" : 1,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 100000,
						"executionTimeMillisEstimate" : 360,
						"works" : 100003,
						"advanced" : 100000,
						"needTime" : 2,
						"needYield" : 0,
						"saveState" : 794,
						"restoreState" : 794,
						"isEOF" : 1,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 100000,
							"executionTimeMillisEstimate" : 304,
							"works" : 100002,
							"advanced" : 100000,
							"needTime" : 1,
							"needYield" : 0,
							"saveState" : 794,
							"restoreState" : 794,
							"isEOF" : 1,
							"invalidates" : 0,
							"docsExamined" : 100000,
							"alreadyHasObj" : 100000,
							"inputStage" : {
								"stage" : "OR",
								"nReturned" : 100000,
								"executionTimeMillisEstimate" : 259,
								"works" : 100002,
								"advanced" : 100000,
								"needTime" : 1,
								"needYield" : 0,
								"saveState" : 794,
								"restoreState" : 794,
								"isEOF" : 1,
								"invalidates" : 0,
								"dupsTested" : 100000,
								"dupsDropped" : 0,
								"recordIdsForgotten" : 0,
								"inputStages" : [
									{
										"stage" : "FETCH",
										"filter" : {
											"$or" : [
												{
													"$nor" : [
														{
															"lockedAt" : {
																"$exists" : true
															}
														}
													]
												},
												{
													"lockedAt" : {
														"$eq" : null
													}
												}
											]
										},
										"nReturned" : 100000,
										"executionTimeMillisEstimate" : 218,
										"works" : 100001,
										"advanced" : 100000,
										"needTime" : 0,
										"needYield" : 0,
										"saveState" : 794,
										"restoreState" : 794,
										"isEOF" : 1,
										"invalidates" : 0,
										"docsExamined" : 100000,
										"alreadyHasObj" : 0,
										"inputStage" : {
											"stage" : "IXSCAN",
											"nReturned" : 100000,
											"executionTimeMillisEstimate" : 64,
											"works" : 100001,
											"advanced" : 100000,
											"needTime" : 0,
											"needYield" : 0,
											"saveState" : 794,
											"restoreState" : 794,
											"isEOF" : 1,
											"invalidates" : 0,
											"keyPattern" : {
												"name" : 1,
												"nextRunAt" : 1,
												"priority" : -1,
												"lockedAt" : 1,
												"disabled" : 1
											},
											"indexName" : "findAndLockNextJobIndex",
											"isMultiKey" : false,
											"multiKeyPaths" : {
												"name" : [ ],
												"nextRunAt" : [ ],
												"priority" : [ ],
												"lockedAt" : [ ],
												"disabled" : [ ]
											},
											"isUnique" : false,
											"isSparse" : false,
											"isPartial" : false,
											"indexVersion" : 2,
											"direction" : "forward",
											"indexBounds" : {
												"name" : [
													"[\"testJob\", \"testJob\"]"
												],
												"nextRunAt" : [
													"(true, new Date(1569576834221)]"
												],
												"priority" : [
													"[MaxKey, MinKey]"
												],
												"lockedAt" : [
													"[null, null]"
												],
												"disabled" : [
													"[MinKey, true)",
													"(true, MaxKey]"
												]
											},
											"keysExamined" : 100000,
											"seeks" : 1,
											"dupsTested" : 0,
											"dupsDropped" : 0,
											"seenInvalidated" : 0
										}
									},
									{
										"stage" : "IXSCAN",
										"nReturned" : 0,
										"executionTimeMillisEstimate" : 0,
										"works" : 1,
										"advanced" : 0,
										"needTime" : 0,
										"needYield" : 0,
										"saveState" : 794,
										"restoreState" : 794,
										"isEOF" : 1,
										"invalidates" : 0,
										"keyPattern" : {
											"name" : 1,
											"lockedAt" : 1,
											"disabled" : 1
										},
										"indexName" : "name_1_lockedAt_1_disabled_1",
										"isMultiKey" : false,
										"multiKeyPaths" : {
											"name" : [ ],
											"lockedAt" : [ ],
											"disabled" : [ ]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"name" : [
												"[\"testJob\", \"testJob\"]"
											],
											"lockedAt" : [
												"(true, new Date(1569576834221)]"
											],
											"disabled" : [
												"[MinKey, true)",
												"(true, MaxKey]"
											]
										},
										"keysExamined" : 0,
										"seeks" : 1,
										"dupsTested" : 0,
										"dupsDropped" : 0,
										"seenInvalidated" : 0
									}
								]
							}
						}
					}
				}
			}
		}
	},
	"serverInfo" : {
		"host" : "localhost.localdomain",
		"port" : 27017,
		"version" : "3.4.19",
		"gitVersion" : "a2d97db8fe449d15eb8e275bbf318491781472bf"
	},
	"ok" : 1
}

I know that query planning is a difficult problem, so I would completely understand if you tell me that this is some kind of edge case, and that I have to live with it I just thought it might be interesting to you.

 



 Comments   
Comment by Carl Champain (Inactive) [ 01/Oct/19 ]

Hi again frederic.maillasson@gemalto.com,

Sorry, I misspoke in my previous comment.
We are really grateful you are looking out for cases like this, and I just noticed our query team is already aware of this problem in SERVER-19114. So, we are closing this ticket as a duplicate. You can watch that ticket for updates.

To elaborate: the second index you created is less optimal for this query because using it requires an in-memory sort. The query planner should be noticing this but isn't, for the reasons described in SERVER-19114.

Kind regards,
Carl

Comment by Carl Champain (Inactive) [ 01/Oct/19 ]

Hi frederic.maillasson@gemalto.com,

Thanks for the report.
I was able to test your example, and I also noticed that adding the new index does make the query less efficient.
I will pass this on to Query team.

Kind regards,
Carl

Generated at Thu Feb 08 05:03:47 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.