[SERVER-15221] Planner sort analysis should not add FETCH stage if sort key included in index key pattern Created: 11/Sep/14  Updated: 15/Mar/19  Resolved: 15/Mar/19

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

Type: Improvement Priority: Major - P3
Reporter: J Rassi Assignee: James Wahlin
Resolution: Done Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-3473 scan and order always loads the full ... Closed
duplicates SERVER-5019 covered indexes are not implemented f... Closed
is duplicated by SERVER-23999 Query fully covered by index still re... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2019-01-28, Query 2019-03-11, Query 2019-03-25
Participants:

 Description   

Queries that require a blocking sort always fetch the full document from the collection to retrieve the sort key, even if the query plan includes an index scan over an index that has the sort key as a part of its key pattern. These queries should be covered instead.

To reproduce:

> db.foo.drop()
true
> db.foo.ensureIndex({a: 1, b: 1})
> db.foo.find({a: {$gt: 0}}, {a: 1, b: 1, _id: 0}).sort({b: 1}).explain(true).stats
{
	"stage" : "PROJECTION",
	"nReturned" : 0,
	"executionTimeMillis" : 2,
	"works" : 4,
	"advanced" : 0,
	"needTime" : 0,
	"isEOF" : 1,
	"invalidates" : 0,
	"transformBy" : {
		"_id" : 0,
		"a" : 1,
		"b" : 1
	},
	"inputStage" : {
		"stage" : "SORT",
		"nReturned" : 0,
		"executionTimeMillis" : 2,
		"works" : 4,
		"advanced" : 0,
		"needTime" : 2,
		"isEOF" : 1,
		"invalidates" : 0,
		"sortPattern" : {
			"b" : 1
		},
		"memUsage" : 0,
		"memLimit" : 33554432,
		"inputStage" : {
			"stage" : "KEEP_MUTATIONS",
			"nReturned" : 0,
			"executionTimeMillis" : 0,
			"works" : 2,
			"advanced" : 0,
			"needTime" : 1,
			"isEOF" : 1,
			"invalidates" : 0,
			"inputStage" : {
				"stage" : "FETCH",
				"nReturned" : 0,
				"executionTimeMillis" : 0,
				"works" : 2,
				"advanced" : 0,
				"needTime" : 1,
				"isEOF" : 1,
				"invalidates" : 0,
				"docsExamined" : 0,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 0,
					"executionTimeMillis" : 0,
					"works" : 1,
					"advanced" : 0,
					"needTime" : 1,
					"isEOF" : 1,
					"invalidates" : 0,
					"keysExamined" : 0,
					"keyPattern" : {
						"a" : 1,
						"b" : 1
					},
					"isMultiKey" : false,
					"indexBounds" : "field #0['a']: (0.0, inf.0], field #1['b']: [MinKey, MaxKey]",
					"dupsTested" : 0,
					"dupsDropped" : 0,
					"seenInvalidated" : 0,
					"matchTested" : 0
				}
			}
		}
	}
}



 Comments   
Comment by Githook User [ 15/Mar/19 ]

Author:

{'name': 'James Wahlin', 'username': 'jameswahlin', 'email': 'james@mongodb.com'}

Message: SERVER-15221 Planner sort analysis should not add FETCH stage if sort key included in index key pattern
Branch: master
https://github.com/mongodb/mongo/commit/0f4aa577ed1e408eed4bb2b0ed8207f911683f7c

Comment by Charlie Swanson [ 25/Feb/19 ]

Pulling this into the current sprint since James will soon be blocked and doesn't believe that SERVER-39678 will be a large amount of work even when unblocked.

Comment by Asya Kamsky [ 23/Oct/18 ]

Another example I ran into with TPCC:

db.DISTRICT.explain().findAndModify( { query: { D_ID: 2, D_W_ID: 3 }, update: { $inc: { D_NEXT_O_ID: 1 } }, fields: { D_ID: 1, D_W_ID: 1, _id: 0, D_NEXT_O_ID: 1, D_TAX: 1 }, sort: { NO_O_ID: 1 }})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "tpcc.DISTRICT",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"D_ID" : {
						"$eq" : 2
					}
				},
				{
					"D_W_ID" : {
						"$eq" : 3
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "PROJECTION",
			"transformBy" : {
				"D_ID" : 1,
				"D_W_ID" : 1,
				"_id" : 0,
				"D_NEXT_O_ID" : 1,
				"D_TAX" : 1
			},
			"inputStage" : {
				"stage" : "UPDATE",
				"inputStage" : {
					"stage" : "SORT",
					"sortPattern" : {
						"NO_O_ID" : 1
					},
					"limitAmount" : 1,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"inputStage" : {
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"D_W_ID" : 1,
									"D_ID" : 1,
									"D_NEXT_O_ID" : 1,
									"D_TAX" : 1
								},
								"indexName" : "D_W_ID_1_D_ID_1_D_NEXT_O_ID_1_D_TAX_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"D_W_ID" : [ ],
									"D_ID" : [ ],
									"D_NEXT_O_ID" : [ ],
									"D_TAX" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"D_W_ID" : [
										"[3.0, 3.0]"
									],
									"D_ID" : [
										"[2.0, 2.0]"
									],
									"D_NEXT_O_ID" : [
										"[MinKey, MaxKey]"
									],
									"D_TAX" : [
										"[MinKey, MaxKey]"
									]
								}
							}
						}
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "4.0.0",
		"gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
	},
	"ok" : 1
}

Comment by David Storch [ 04/May/16 ]

Hi andrey.hohutkin@gmail.com,

Unfortunately this ticket has not been scheduled for the current development cycle, but it remains open for future consideration. Note that the "fixVersion" field reflects its current prioritization. Please continue to watch for updates.

Best,
Dave

Comment by Andrey Hohutkin [ 04/May/16 ]

Can someone give higher priority to this issue?
Really important feature for me.

Comment by J Rassi [ 11/Sep/14 ]

Relevant code excerpt here:

    QuerySolutionNode* QueryPlannerAnalysis::analyzeSort(const CanonicalQuery& query,
                                                         const QueryPlannerParams& params,
                                                         QuerySolutionNode* solnRoot,
                                                         bool* blockingSortOut) {
 
    [...]
 
        // Add a fetch stage so we have the full object when we hit the sort stage.  TODO: Can we
        // pull the values that we sort by out of the key and if so in what cases?  Perhaps we can
        // avoid a fetch.
        if (!solnRoot->fetched()) {
            FetchNode* fetch = new FetchNode();
            fetch->children.push_back(solnRoot);
            solnRoot = fetch;
        }
 
    [...]
 
    }

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