[SERVER-20066] Query planner should consider index scans on empty query predicates Created: 20/Aug/15  Updated: 08/Nov/22  Resolved: 20/Jun/17

Status: Closed
Project: Core Server
Component/s: Performance, Querying
Affects Version/s: 3.0.5
Fix Version/s: 3.5.9

Type: Improvement Priority: Major - P3
Reporter: Charlie Swanson Assignee: Kyle Suarez
Resolution: Done Votes: 1
Labels: bi-performance, optimization
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-3342 utilize index for full collection sca... Closed
is duplicated by SERVER-30130 Covered query with empty find() witho... Closed
Related
related to SERVER-23406 index scan is slower than full collec... Backlog
related to SERVER-29444 Use a covered and streaming plan for ... Backlog
is related to SERVER-59341 Query planner choses collscan over co... Backlog
is related to SERVER-4507 aggregation: optimize $group to take... Backlog
Backwards Compatibility: Fully Compatible
Sprint: Query 2017-05-08, Query 2017-05-29, Query 2017-06-19, Query 2017-07-10
Participants:
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
}



 Comments   
Comment by Kyle Suarez [ 13/Jul/18 ]

if a valid index scan plan is generated then it will always be used instead of a collection scan plan.  Is that the case here as well?

Yes, that's correct. The query planner will always assume that an index scan is better than a collection scan.

does this ticket simply enable the generation of index scan plans (for empty predicate queries when the internal parameter is enabled), and the rest of the plan selection process is unchanged for these plans afterwards?

Correct. This is the code that was added to generate these plans; it then falls through to the rest of the query planning logic: https://github.com/mongodb/mongo/blob/1345c0476cf47d691e8db532967238800d0a70c2/src/mongo/db/query/query_planner.cpp#L1005-L1037

Comment by Chris Harris [ 13/Jul/18 ]

With respect to the expected behavior of this ticket, the case description says that the planner should "consider" index scans on empty query predicates.  My impression of the current behavior in the general case is that if a valid index scan plan is generated then it will always be used instead of a collection scan plan.  Is that the case here as well?  Or, said another way, does this ticket simply enable the generation of index scan plans (for empty predicate queries when the internal parameter is enabled), and the rest of the plan selection process is unchanged for these plans afterwards?

Comment by Githook User [ 20/Jun/17 ]

Author:

{u'username': u'ksuarz', u'name': u'Kyle Suarez', u'email': u'kyle.suarez@mongodb.com'}

Message: SERVER-20066 disable generating whole covered ixscans by default

Users can enable this behavior by turning on the
internalQueryPlannerGenerateCoveredWholeIndexScans query knob.
Branch: master
https://github.com/mongodb/mongo/commit/3d25846ea31d91468475086035f4794ad126ed0f

Comment by Kyle Suarez [ 15/Jun/17 ]

Re-opening this ticket, as we want to place this behavior behind a query knob for now.

Comment by Githook User [ 07/Jun/17 ]

Author:

{u'username': u'ksuarz', u'name': u'Kyle Suarez', u'email': u'kyle.suarez@mongodb.com'}

Message: SERVER-20066 consider ixscans if query is empty but projection can be covered
Branch: master
https://github.com/mongodb/mongo/commit/1345c0476cf47d691e8db532967238800d0a70c2

Generated at Thu Feb 08 03:53:02 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.