[SERVER-35018] remove unnecessary FETCH from plan when fetch tests condition that's implied by indexed condition Created: 16/May/18  Updated: 03/Oct/23

Status: Backlog
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 Optimization
Resolution: Unresolved Votes: 1
Labels: QFB, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-81792 Optimize $exists and $ne: null in the... Backlog
is related to SERVER-75079 Simplify boolean expressions before f... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

Compare these two queries and their plans:

db.full.explain().count({a:{$eq:1}})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.full",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"a" : {
				"$eq" : 1
			}
		},
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "COUNT_SCAN",
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"a" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"indexBounds" : {
					"startKey" : {
						"a" : 1
					},
					"startKeyInclusive" : true,
					"endKey" : {
						"a" : 1
					},
					"endKeyInclusive" : true
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.6.4",
		"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
	},
	"ok" : 1
}

With equality on "a" we get best possible plan.

But adding a "redundant" condition on "a" that by itself cannot be fulfilled using the same index, we now revert to the "worse" plan with additional FETCH:

test@127.0.0.1:27017(3.6.4) > db.full.explain().count({a:{$exists:true,$eq:1}})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.full",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"a" : {
						"$eq" : 1
					}
				},
				{
					"a" : {
						"$exists" : true
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"a" : {
						"$exists" : true
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"a" : 1
					},
					"indexName" : "a_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"a" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"a" : [
							"[1.0, 1.0]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.6.4",
		"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
	},
	"ok" : 1
}

 
This feels conceptually maybe related to SERVER-12281 (and even SERVER-34119) but seemed like it might be an easier fix on its own.

Same thing happens if $eq:1 is and'ed with $ne:null for instance.



 Comments   
Comment by Asya Kamsky [ 14/May/20 ]

It looks like the second case (adding redundant $ne:null) does not cause an inferior plan to be chosen. I suspect this is because of recent fix where $ne:null can now use an index without FETCH, however redundant $exists:true still forces worse plan.

Comment by Julian Edwards [ 29/May/18 ]

david.storch Sorry about that, the ticket number I meant to put is SERVER-35108 

Comment by David Storch [ 29/May/18 ]

julian.edwards, looks like commit https://github.com/mongodb/mongo/commit/133a00391a0d8ee80ccfd096d5771db710f7f2e8 had the wrong ticket number? I'm going to delete the comment from the githook on this ticket, so you may wish to comment on the intended ticket with a github link to the commit.

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