[SERVER-34119] FETCH after AND_SORTED stage even though IXSCAN being intersected has tight bounds and query can be covered Created: 24/Mar/18  Updated: 27/Oct/23  Resolved: 28/Jun/19

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

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Backlog - Query Team (Inactive)
Resolution: Works as Designed Votes: 0
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query
Participants:

 Description   

Trying to analyze cost calculation for index intersection I came across something that seems incorrect (suboptimal) - maybe it's related to SERVER-13740?

There is an index on x and class and query is equality on x and class - this section is from rejectedPlans in explain with execution stats:

			{
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 123,
				"totalDocsExamined" : 1,
				"executionStages" : {
					"stage" : "PROJECTION",
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 0,
					"works" : 123,
					"advanced" : 1,
					"needTime" : 122,
					"needYield" : 0,
					"saveState" : 2,
					"restoreState" : 2,
					"isEOF" : 0,
					"invalidates" : 0,
					"transformBy" : {
						"_id" : 0,
						"x" : 1,
						"class" : 1
					},
					"inputStage" : {
						"stage" : "FETCH",
						"filter" : {
							"$and" : [
								{
									"x" : {
										"$eq" : 1
									}
								},
								{
									"class" : {
										"$eq" : 0.027375694861415
									}
								}
							]
						},
						"nReturned" : 1,
						"executionTimeMillisEstimate" : 0,
						"works" : 123,
						"advanced" : 1,
						"needTime" : 122,
						"needYield" : 0,
						"saveState" : 2,
						"restoreState" : 2,
						"isEOF" : 0,
						"invalidates" : 0,
						"docsExamined" : 1,
						"alreadyHasObj" : 0,
						"inputStage" : {
							"stage" : "AND_SORTED",
							"nReturned" : 1,
							"executionTimeMillisEstimate" : 0,
							"works" : 123,
							"advanced" : 1,
							"needTime" : 122,
							"needYield" : 0,
							"saveState" : 2,
							"restoreState" : 2,
							"isEOF" : 0,
							"invalidates" : 0,
							"flagged" : 0,
							"failedAnd_0" : 2,
							"failedAnd_1" : 1,
							"inputStages" : [
								{
									"stage" : "IXSCAN",
									"nReturned" : 116,
									"executionTimeMillisEstimate" : 0,
									"works" : 116,
									"advanced" : 116,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 2,
									"restoreState" : 2,
									"isEOF" : 0,
									"invalidates" : 0,
									"keyPattern" : {
										"x" : 1
									},
									"indexName" : "x_1",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"x" : [ ]
									},
									"isUnique" : false,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"x" : [
											"[1.0, 1.0]"
										]
									},
									"keysExamined" : 116,
									"seeks" : 1,
									"dupsTested" : 0,
									"dupsDropped" : 0,
									"seenInvalidated" : 0
								},
								{
									"stage" : "IXSCAN",
									"nReturned" : 7,
									"executionTimeMillisEstimate" : 0,
									"works" : 7,
									"advanced" : 7,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 2,
									"restoreState" : 2,
									"isEOF" : 0,
									"invalidates" : 0,
									"keyPattern" : {
										"class" : 1
									},
									"indexName" : "class_1",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"class" : [ ]
									},
									"isUnique" : false,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"class" : [
											"[0.027375694861415, 0.027375694861415]"
										]
									},
									"keysExamined" : 7,
									"seeks" : 1,
									"dupsTested" : 0,
									"dupsDropped" : 0,
									"seenInvalidated" : 0
								}
							]
						}
					}
				}
			},

After AND_SORTED there is the single matched document and it should be covered but there's a FETCH applying the query filter again before the PROJECTION stage. Seems like the FETCH is unnecessary (unless AND_SORTED isn't passed the index values? in which case this would be enhancement request to enable that).



 Comments   
Comment by David Storch [ 28/Jun/19 ]

The proposed optimization is not correct: see SERVER-16750. The conjunction on the x and class fields must be reapplied after performing index intersection in order to guard against the case that the class predicate matched in one storage snapshot, and the x predicate matched in another, but both predicates never matched simultaneously.

Generated at Thu Feb 08 04:35:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.