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

Query planner should consider index scans on empty query predicates

XMLWordPrintableJSON

    • Fully Compatible
    • Query 2017-05-08, Query 2017-05-29, Query 2017-06-19, Query 2017-07-10
    • 0

      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
}

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

              Created:
              Updated:
              Resolved: