Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-35018

remove unnecessary FETCH from plan when fetch tests condition that's implied by indexed condition

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • Query Optimization

      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.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated: