[SERVER-48669] Questions about plan cache scoring Created: 09/Jun/20  Updated: 18/Jun/20  Resolved: 16/Jun/20

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Minor - P4
Reporter: Adrien Rey-Jarthon Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

Hi,

We're currently investigating some slow queries in our mongoDB instance and most particularly for now queries using sub-optimal index when a better index exists.

I understand how mongo select the better index by trying several plan, rank them based on score and then cache the result. This can lead to problems if two queries with the same query shape have very different index cardinality, as mongo will re-evaluate plan (if the "works" is at least 10 times more than planned as I understand?) but if the query alternates we'll always get some good results and some bad results. I understand that and this is not the problem here.

When looking at the cache plans for some of our queries I compared the different plans and tried to understand mongodb score (I found this presentation which may be a bit outdated but helps a lot: https://fr.slideshare.net/miguelangelnieto/query-planner-88498371)

As I understand, when one of the plans finished (isEOF) before the others, it's supposed to get +1 score, the code seems to confirm that: https://github.com/mongodb/mongo/blob/210053393a311b942835445134794d1bb38268a2/src/mongo/db/query/plan_ranker.cpp#L121

But in every examples I saw, this was not the case and the astronomically better plan often ends up with almost the same score as the terribly bad plan, here's an example:

Here there's many possible plans, but the first plan (using approval_required_contents_count) is much better (isEOF) and all other plans were interrupted. Yet it's score is just equal to the others except for some tieBreaker probably? According to the code and presentation I would expect this one to get a score of 2 because of the EOF, no?

dimelo:PRIMARY> db['content_threads'].getPlanCache().getPlansByQuery({"deleted_at" : null, "source_id" : { "$in" : [ObjectId("5e25d8ef3298c901661d0d25")] }, "approval_required_contents_count" : { "$gt" : 0 }, "intervention_user_ids" : { "$ne" : [ ] }, "category_ids" : { "$nin" : [ObjectId("5b98cc5a14bf8a7912f06517")] }, "opened_and_assigned" : false})
{
	"plans" : [
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf approval_required_contents_count_1, pos: 0, can combine? 1\n---Node\n------Leaf \n---Node\n------Leaf \n)"
			},
			"reason" : {
				"score" : 1.0003000000000002,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"opened_and_assigned" : {
									"$eq" : false
								}
							},
							{
								"source_id" : {
									"$eq" : ObjectId("5e25d8ef3298c901661d0d25")
								}
							},
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							},
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 10,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 107,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 1,
					"invalidates" : 0,
					"docsExamined" : 107,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 107,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 107,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 1,
						"invalidates" : 0,
						"keyPattern" : {
							"approval_required_contents_count" : 1
						},
						"indexName" : "approval_required_contents_count_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"approval_required_contents_count" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : true,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"approval_required_contents_count" : [
								"(0, inf.0]"
							]
						},
						"keysExamined" : 107,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				"nfeedback" : 20,
				"scores" : [
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					},
					{
						"score" : 1.0003
					}
				]
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Node\n------Leaf \n---Node\n------Leaf intervention_user_ids_1, pos: 0, can combine? 1\n)"
			},
			"reason" : {
				"score" : 1.0003,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							},
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"opened_and_assigned" : {
									"$eq" : false
								}
							},
							{
								"source_id" : {
									"$eq" : ObjectId("5e25d8ef3298c901661d0d25")
								}
							},
							{
								"approval_required_contents_count" : {
									"$gt" : 0
								}
							},
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 10,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 108,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 107,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 107,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 107,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"intervention_user_ids" : 1
						},
						"indexName" : "intervention_user_ids_1",
						"isMultiKey" : true,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"intervention_user_ids" : [
								"[MinKey, undefined)",
								"(undefined, [])",
								"([], MaxKey]"
							]
						},
						"keysExamined" : 108,
						"seeks" : 2,
						"dupsTested" : 107,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Node\n------Leaf category_ids_1, pos: 0, can combine? 1\n---Node\n------Leaf \n)"
			},
			"reason" : {
				"score" : 1.0003,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							},
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"opened_and_assigned" : {
									"$eq" : false
								}
							},
							{
								"source_id" : {
									"$eq" : ObjectId("5e25d8ef3298c901661d0d25")
								}
							},
							{
								"approval_required_contents_count" : {
									"$gt" : 0
								}
							},
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 108,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 108,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 108,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 108,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"category_ids" : 1
						},
						"indexName" : "category_ids_1",
						"isMultiKey" : true,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"category_ids" : [
								"[MinKey, ObjectId('5b98cc5a14bf8a7912f06517'))",
								"(ObjectId('5b98cc5a14bf8a7912f06517'), MaxKey]"
							]
						},
						"keysExamined" : 108,
						"seeks" : 1,
						"dupsTested" : 108,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf opened_and_assigned_1, pos: 0, can combine? 1\n---Leaf \n---Leaf \n---Node\n------Leaf \n---Node\n------Leaf \n)"
			},
			"reason" : {
				"score" : 1.0003,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"source_id" : {
									"$eq" : ObjectId("5e25d8ef3298c901661d0d25")
								}
							},
							{
								"approval_required_contents_count" : {
									"$gt" : 0
								}
							},
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							},
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 108,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 108,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 108,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 108,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"opened_and_assigned" : 1
						},
						"indexName" : "opened_and_assigned_1",
						"isMultiKey" : false,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"opened_and_assigned" : [
								"[false, false]"
							]
						},
						"keysExamined" : 108,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf source_id_1, pos: 0, can combine? 1\n---Leaf \n---Node\n------Leaf \n---Node\n------Leaf \n)"
			},
			"reason" : {
				"score" : 1.0003,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"opened_and_assigned" : {
									"$eq" : false
								}
							},
							{
								"approval_required_contents_count" : {
									"$gt" : 0
								}
							},
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							},
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 108,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 108,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 108,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 108,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"source_id" : 1
						},
						"indexName" : "source_id_1",
						"isMultiKey" : false,
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"source_id" : [
								"[ObjectId('5e25d8ef3298c901661d0d25'), ObjectId('5e25d8ef3298c901661d0d25')]"
							]
						},
						"keysExamined" : 108,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf source_id_1_closed_at_1, pos: 0, can combine? 1\n---Leaf \n---Node\n------Leaf \n---Node\n------Leaf \n)"
			},
			"reason" : {
				"score" : 1.0003,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"deleted_at" : {
									"$eq" : null
								}
							},
							{
								"opened_and_assigned" : {
									"$eq" : false
								}
							},
							{
								"approval_required_contents_count" : {
									"$gt" : 0
								}
							},
							{
								"$nor" : [
									{
										"category_ids" : {
											"$eq" : ObjectId("5b98cc5a14bf8a7912f06517")
										}
									}
								]
							},
							{
								"$nor" : [
									{
										"intervention_user_ids" : {
											"$eq" : [ ]
										}
									}
								]
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 108,
					"advanced" : 0,
					"needTime" : 108,
					"needYield" : 0,
					"saveState" : 5,
					"restoreState" : 5,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 108,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 108,
						"executionTimeMillisEstimate" : 0,
						"works" : 108,
						"advanced" : 108,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5,
						"restoreState" : 5,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"source_id" : 1,
							"closed_at" : 1
						},
						"indexName" : "source_id_1_closed_at_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"source_id" : [ ],
							"closed_at" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"source_id" : [
								"[ObjectId('5e25d8ef3298c901661d0d25'), ObjectId('5e25d8ef3298c901661d0d25')]"
							],
							"closed_at" : [
								"[MinKey, MaxKey]"
							]
						},
						"keysExamined" : 108,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		}
	],
	"timeOfCreation" : ISODate("2020-06-06T05:21:32.091Z"),
	"ok" : 1,
	"operationTime" : Timestamp(1591713174, 254),
	"$clusterTime" : {
		"clusterTime" : Timestamp(1591713174, 255),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	}
}

Most of the time this good index is used, some times it's not though, leading to:

planSummary: IXSCAN { intervention_user_ids: 1 } keysExamined:982857 docsExamined:905127

Probably because another query shape made this index better.

So my questions are:

  • What about this +1 score for EOF? why don't I see it?
  • Does the query shape only care about attribute name? so { attribute: 'xx' }

    is considered as the same plan as

    Unknown macro: { attribute}

    ? are there any improvements planned on this side? or workarounds (besides hint)? when I see mongo using "attribute" index for a "not equal" or "not in" query and thus full scan the collection I feel very sad.

  • I noticed that $in with 2+ values uses a different plan than $in with 1 value so it seems the "matcher" does mater a bit for the query shape? Is there any explanation somewhere about how does this work? what is considered the same "shape"?
  • Sometimes I searched for a specific query in the .listQueryShapes() but couldn't find it (while slow queries were regularly visible in the logs). After a .clear() the query I was looking for quickly appears in the list. Is it expected? what is the size limit of this cache? what happens when it's full? I read somewhere that it's "LRU" but in this case I don't understand why my recent slow query was not even present in the existing list (the list of cached query plans had about 250 entries)

Thanks a lot for your help!

ps: this example is on MongoDB 3.6.8, but I also checked on another MongoDB instance running 4.2.7 and I also don't see the "+1" for EOF scoring:

rs0:PRIMARY> db.apdexes.getPlanCache().getPlansByQuery({"check_id" : ObjectId("59146ca527939d1d28691b75"),"hostname" : "fra","group_by" : null,"time" : ISODate("2020-05-31T18:00:00Z")})
{
	"plans" : [
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf (check_id_1_time_1, ), pos: 0, can combine? 1\n---Leaf \n---Leaf \n---Leaf (check_id_1_time_1, ), pos: 1, can combine? 1\n)"
			},
			"reason" : {
				"score" : 1.3335333333333335,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"group_by" : {
									"$eq" : null
								}
							},
							{
								"hostname" : {
									"$eq" : "fra"
								}
							}
						]
					},
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 1,
					"needTime" : 1,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"docsExamined" : 2,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 2,
						"executionTimeMillisEstimate" : 0,
						"works" : 3,
						"advanced" : 2,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"keyPattern" : {
							"check_id" : 1,
							"time" : 1
						},
						"indexName" : "check_id_1_time_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"check_id" : [ ],
							"time" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"check_id" : [
								"[ObjectId('59146ca527939d1d28691b75'), ObjectId('59146ca527939d1d28691b75')]"
							],
							"time" : [
								"[new Date(1590948000000), new Date(1590948000000)]"
							]
						},
						"keysExamined" : 2,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0
					}
				}
			},
			"feedback" : {
				"nfeedback" : 20,
				"scores" : [
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.1430571428571428
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.1113111111111111
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.0002
					},
					{
						"score" : 1.3335333333333332
					},
					{
						"score" : 1.0002
					}
				]
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf (group_by_1_time_1, ), pos: 0, can combine? 1\n---Leaf \n---Leaf (group_by_1_time_1, ), pos: 1, can combine? 1\n)"
			},
			"reason" : {
				"score" : 1.0002,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"group_by" : {
									"$eq" : null
								}
							},
							{
								"check_id" : {
									"$eq" : ObjectId("59146ca527939d1d28691b75")
								}
							},
							{
								"hostname" : {
									"$eq" : "fra"
								}
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 3,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 0,
					"docsExamined" : 2,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 2,
						"executionTimeMillisEstimate" : 0,
						"works" : 3,
						"advanced" : 2,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 0,
						"keyPattern" : {
							"group_by" : 1,
							"time" : 1
						},
						"indexName" : "group_by_1_time_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"group_by" : [ ],
							"time" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 1,
						"direction" : "forward",
						"indexBounds" : {
							"group_by" : [
								"[undefined, undefined]",
								"[null, null]"
							],
							"time" : [
								"[new Date(1590948000000), new Date(1590948000000)]"
							]
						},
						"keysExamined" : 3,
						"seeks" : 2,
						"dupsTested" : 0,
						"dupsDropped" : 0
					}
				}
			},
			"feedback" : {
				
			},
			"filterSet" : false
		}
	],
	"timeOfCreation" : ISODate("2020-05-31T18:24:37.809Z"),
	"queryHash" : "A3B8FBBB",
	"planCacheKey" : "B6206164",
	"isActive" : true,
	"works" : NumberLong(3),
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1591711488, 357),
		"signature" : {
			"hash" : BinData(0,"+l0ww44TffvyeY8LwGiSgfuhKtE="),
			"keyId" : NumberLong("6801101762850717697")
		}
	},
	"operationTime" : Timestamp(1591711488, 357)
}



 Comments   
Comment by Adrien Rey-Jarthon [ 18/Jun/20 ]

Thanks a lot for these precisions!

Understood for the EOF flag, the comment is clear.

Indeed I didn't think there was a bug yet, I'm just trying to better understand how it works to make better optimisations on our side. The query plan cache obviously can not work perfectly in every cases and that's understandable.

The documentation (https://docs.mongodb.com/manual/reference/glossary/#term-query-shape) doesn't really explain in detail what makes up the query shape, especially when it comes to operators and the number of items in an array as I asked in my comment. But after some manual testing I think I get the idea, operators seems to be taken into account, and for the array with one item or more I guess there's an optimisation removing the $in internally or something like that.

I'll post on the forum or SO if I have other questions, no problem! I though the "Question" type here in jira was ok for technical questions that other users probably can't answer.

Thanks,
Adrien

Comment by Kelsey Schubert [ 16/Jun/20 ]

Hi adrien.jarthon@ringcentral.com,

I would recommending reviewing SERVER-32452 for workarounds as I expect the same workaround should be considered here as well. The ticket describes a fix that may help here, but isn't the complete story. We're continuing to actively improve our query planner, see SERVER-48228, as a recent example.

Regarding your question about the EOF bonus. It is applied, but not stored in the plan cache entry as described in this comment, which why you aren't seeing it when you retrieve the cached entry: https://github.com/mongodb/mongo/blob/57edf434219c9659354f01fb6bf4f4e6c0370bc4/src/mongo/db/query/plan_ranker.cpp#L165-L168

It challenging discuss the observations that you've noted with the plan caching without a specific examples or repros. From your description, these do not necessarily sound a like bug in the system. For more details about what is significant in determining query shape, please review our documentation: https://docs.mongodb.com/manual/reference/glossary/#term-query-shape

Please note that the SERVER project is for bugs and feature suggestions for the MongoDB server. If you need further assistance troubleshooting around this issue, I would encourage you to ask our community by posting on the MongoDB Community Forums or on Stack Overflow with the mongodb tag.

Thank you,
Kelsey

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