[SERVER-17148] Remove plans do not need a FETCH stage Created: 02/Feb/15  Updated: 30/Jan/24

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

Type: Improvement Priority: Major - P3
Reporter: David Storch Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 5
Labels: query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-27622 Skip document read from disk when del... Closed
Related
related to SERVER-27822 Count plans sometimes don't need a FETCH Closed
related to SERVER-17266 count command performance optimizatio... Backlog
related to SERVER-80423 Make the preImage document always ava... Closed
Assigned Teams:
Query Optimization
Sprint: Quint 9 09/18/15
Participants:
Case:

 Description   

Currently, indexed .remove() plans will always fetch the documents associated with the index keys before performing the remove:

> t.drop()
true
> t.ensureIndex({x: 1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> t.explain().remove({x: {$gte: 5, $lte: 10}})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.t",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"x" : {
						"$lte" : 10
					}
				},
				{
					"x" : {
						"$gte" : 5
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "DELETE",
			"inputStage" : {
				"stage" : "FETCH", // This stage is not needed!
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"x" : 1
					},
					"indexName" : "x_1",
					"isMultiKey" : false,
					"direction" : "forward",
					"indexBounds" : {
						"x" : [
							"[5.0, 10.0]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "dstorch-desktop",
		"port" : 27017,
		"version" : "3.1.0-pre-",
		"gitVersion" : "ee4db2a36eb894e2694fea781ab70f0cca33d0b8"
	},
	"ok" : 1
}

As an optimization, we could change the query planner to generate indexed .remove() plans without the FETCH stage.



 Comments   
Comment by Asya Kamsky [ 18/Sep/17 ]

Note that many count commands now don't use a FETCH as result of SERVER-27822 being fixed for 3.5.9. The related SERVER-17266 also seems to have had all its examples fixed, so this ticket is now only tracking remove using fetch.

Comment by David Storch [ 07/Feb/17 ]

Note that this also applies to IDHACK remove and IDHACK count plans, which are used when the remove/count predicate is a simple lookup on _id. Such plans won't have an explicit FETCH stage, since the IDHACK stage fetches the entire document itself. However, we can still optimize such plans by refraining from fetching the document during IDHACK.

One can see that the IDHACK stage involves a document fetch in the explain output:

> db.c.drop();
> db.c.insert({_id: 3});
> db.c.explain("executionStats").remove({_id: 3});
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.c",
		"indexFilterSet" : false,
		"winningPlan" : {
			"stage" : "DELETE",
			"inputStage" : {
				"stage" : "IDHACK"
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 1,
		"executionStages" : {
			"stage" : "DELETE",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 0,
			"works" : 2,
			"advanced" : 0,
			"needTime" : 1,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"nWouldDelete" : 1,
			"nInvalidateSkips" : 0,
			"inputStage" : {
				"stage" : "IDHACK",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 1,
				"advanced" : 1,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 1,
				"restoreState" : 1,
				"isEOF" : 1,
				"invalidates" : 0,
				"keysExamined" : 1,
				"docsExamined" : 1
			}
		}
	},
	"serverInfo" : {
		"host" : "dstorch",
		"port" : 27017,
		"version" : "0.0.0",
		"gitVersion" : "unknown"
	},
	"ok" : 1
}

In particular, the IDHACK stage in the executionStats section reports a docsExamined value of 1.

Comment by zhyk [ 10/Jun/16 ]

SERVER-17266 is actually another problem, it is more like COUNT_SCAN vs. IXSCAN problem (after doing explain on my machine).
Here the problem is, COUNT, like DELETE, do NOT need a FETCH stage to perform their work.

Comment by zhyk [ 10/Jun/16 ]

So that COUNT may depend on FETCH?
A query with projection gives NO FETCH, until the applying count() to the returned cursor, that's what I observed.
When the PROJECTION stage become COUNT stage, a FETCH being added? why?

Comment by Asya Kamsky [ 10/Jun/16 ]

Looks like SERVER-17266 is the count issue.

Comment by Asya Kamsky [ 10/Jun/16 ]

I believe that's expected when COUNT_SCAN cannot be used. If that's something we want to fix it should probably be a different ticket.

Comment by zhyk [ 10/Jun/16 ]

Count FETCHs when COUNT_SCAN not work, and forced IXSCAN.

> db.t1.ensureIndex({a:1,b:1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.t1.explain().count({a:{$gt:1},b:{$gt:1}})
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.t1",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "a" : {
                                                "$gt" : 1
                                        }
                                },
                                {
                                        "b" : {
                                                "$gt" : 1
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COUNT",
                        "inputStage" : {
                                "stage" : "FETCH",
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "a" : 1,
                                                "b" : 1
                                        },
                                        "indexName" : "a_1_b_1",
                                        "isMultiKey" : false,
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 1,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "a" : [
                                                        "(1.0, 1.#INF]"
                                                ],
                                                "b" : [
                                                        "(1.0, 1.#INF]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "zhyk-pc",
                "port" : 27018,
                "version" : "3.2.6",
                "gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
        },
        "ok": 1
}

Comment by Asya Kamsky [ 18/May/16 ]

Count does not seem to ever FETCH so I think this ticket is just for remove now.

Comment by J Rassi [ 12/Feb/15 ]

Count plans, too.

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