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

Query planner should consider index scans on empty query predicates

    XMLWordPrintable

    Details

    • Backwards Compatibility:
      Fully Compatible
    • Sprint:
      Query 2017-05-08, Query 2017-05-29, Query 2017-06-19, Query 2017-07-10
    • Case:
    • Linked BF Score:
      0

      Description

      Allow the planner to generate whole covered index scans when possible, rather than defaulting to a collection scan. This feature is disabled by default, as there is a chance that this will cause a performance regression for certain workloads and deployments, especially when all the data in a collection fits in memory.

      To enable this feature, set the internalQueryPlannerGenerateCoveredWholeIndexScans query knob. For example, you can start mongod like

      $ mongod --setParameter internalQueryPlannerGenerateCoveredWholeIndexScans=1
      


      Original Description

      In cases where the query predicate is empty, the selected plan will always involve a collection scan:

      > db.foo.explain().find({}, {a: 1, b: 1, _id: 0})
      {
      	"waitedMS" : NumberLong(0),
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.foo",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [ ]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"a" : 1,
      				"b" : 1
      			},
      			"inputStage" : {
      				"stage" : "COLLSCAN",
      				"filter" : {
      					"$and" : [ ]
      				},
      				"direction" : "forward"
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "franklinia",
      		"port" : 27017,
      		"version" : "3.1.7-pre-",
      		"gitVersion" : "none"
      	},
      	"ok" : 1
      }
      

      This is likely to be the most efficient plan if you want to read all of the documents in a collection, but in some cases it may not be the most efficient plan. e.g. In cases where the projection is a small subset of the fields, and it can be covered by an index scan, an index scan may be preferred to avoid fetching documents. The query system should consider such plans.

      > db.foo.getIndices()
      [
      	{
      		"v" : 1,
      		"key" : {
      			"_id" : 1
      		},
      		"name" : "_id_",
      		"ns" : "test.foo"
      	},
      	{
      		"v" : 1,
      		"key" : {
      			"a" : 1,
      			"b" : 1,
      			"c" : 1
      		},
      		"name" : "a_1_b_1_c_1",
      		"ns" : "test.foo"
      	}
      ]
      > db.foo.find({a: {$lt: 0}}).count() // All documents have 'a' >= 0
      0
      > db.foo.explain().find({a: {$gte: 0}}, {a: 1, b: 1, _id: 0})
      {
      	"waitedMS" : NumberLong(0),
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.foo",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"f0" : {
      				"$gte" : 0
      			}
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"a" : 1,
      				"b" : 1,
      				"_id" : 0
      			},
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"a" : 1,
      					"b" : 1,
      					"c" : 1
      				},
      				"indexName" : "a_1_b_1_c_1",
      				"isMultiKey" : false,
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 1,
      				"direction" : "forward",
      				"indexBounds" : {
      					"a" : [
      						"[0.0, inf.0]"
      					],
      					"b" : [
      						"[MinKey, MaxKey]"
      					],
      					"c" : [
      						"[MinKey, MaxKey]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "franklinia",
      		"port" : 27017,
      		"version" : "3.1.7-pre-",
      		"gitVersion" : "none"
      	},
      	"ok" : 1
      }
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              kyle.suarez Kyle Suarez
              Reporter:
              charlie.swanson Charlie Swanson
              Participants:
              Votes:
              1 Vote for this issue
              Watchers:
              16 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: