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

Query planner should consider index scans on empty query predicates

    XMLWordPrintable

Details

    • Fully Compatible
    • Query 2017-05-08, Query 2017-05-29, Query 2017-06-19, Query 2017-07-10
    • 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

              kyle.suarez@mongodb.com Kyle Suarez
              charlie.swanson@mongodb.com Charlie Swanson
              Votes:
              1 Vote for this issue
              Watchers:
              18 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: