[SERVER-47826] Server does not appropriately respect setting internalQueryEnumerationMaxOrSolutions to 0 Created: 28/Apr/20  Updated: 29/Oct/23  Resolved: 14/Oct/20

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 4.9.0

Type: Bug Priority: Minor - P4
Reporter: Chris Harris Assignee: Ted Tuckman
Resolution: Fixed Votes: 0
Labels: qopt-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-36393 Contained $or pushdown optimization c... Backlog
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

db.foo.drop()
db.foo.createIndex({common:1})
db.foo.createIndex({one:1})
db.foo.createIndex({two:1})
 
// Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated: 
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
 
 
// Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan:
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
 
 
// With a value of 1, we get the same result as when it is 0.  This is output seems correct for this setting:
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
 
// Further increases to the parameter have the expected increase to the number of plans:
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4})
db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length

Full output from a mongo shell session against a 4.2.2 mongod:

> db.foo.drop()
true
> 
> db.foo.createIndex({common:1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.createIndex({one:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}
> db.foo.createIndex({two:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 3,
	"numIndexesAfter" : 4,
	"ok" : 1
}
> 
> 
> // Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated:
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10})
{ "was" : 10, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
4
> 
> 
> // Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan:
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0})
{ "was" : 10, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
1
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
{
	"stage" : "FETCH",
	"filter" : {
		"common" : {
			"$eq" : 1
		}
	},
	"inputStage" : {
		"stage" : "OR",
		"inputStages" : [
			{
				"stage" : "FETCH",
				"filter" : {
					"two" : {
						"$eq" : 0
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"one" : 1
					},
					"indexName" : "one_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"one" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"one" : [
							"[0.0, 0.0]"
						]
					}
				}
			},
			{
				"stage" : "FETCH",
				"filter" : {
					"one" : {
						"$eq" : 1
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"two" : 1
					},
					"indexName" : "two_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"two" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"two" : [
							"[1.0, 1.0]"
						]
					}
				}
			}
		]
	}
}
> 
> // With a value of 1, we get the same result as when it is 0.  But the output now seems correct based on the updated parameter value:
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1})
{ "was" : 0, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
1
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
{
	"stage" : "FETCH",
	"filter" : {
		"common" : {
			"$eq" : 1
		}
	},
	"inputStage" : {
		"stage" : "OR",
		"inputStages" : [
			{
				"stage" : "FETCH",
				"filter" : {
					"two" : {
						"$eq" : 1
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"one" : 1
					},
					"indexName" : "one_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"one" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"one" : [
							"[1.0, 1.0]"
						]
					}
				}
			},
			{
				"stage" : "FETCH",
				"filter" : {
					"one" : {
						"$eq" : 0
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"two" : 1
					},
					"indexName" : "two_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"two" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"two" : [
							"[0.0, 0.0]"
						]
					}
				}
			}
		]
	}
}
> 
> 
> // Further increases to the parameter have the expected corresponding increase to the number of plans:
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2})
{ "was" : 1, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
2
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3})
{ "was" : 2, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
3
> db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4})
{ "was" : 3, "ok" : 1 }
> db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
4 

Sprint: Query 2020-06-01, Query 2020-06-15, Query 2020-06-29, Query 2020-07-13, Query 2020-07-27, Query 2020-08-10, Query 2020-08-24, Query 2020-09-07, Query 2020-09-21, Query 2020-10-05, Query 2020-10-19
Participants:

 Description   

When internalQueryEnumerationMaxOrSolutions is set to 0 the database will still generate and consider a plan which contains a "OR" stage.  This is the same behavior as when the parameter is set to a value of {{1 }}so it seems incorrect.  Further increases to the parameter after that generate additional plans as expected.  



 Comments   
Comment by Githook User [ 14/Oct/20 ]

Author:

{'name': 'Ted Tuckman', 'email': 'ted.tuckman@mongodb.com', 'username': 'TedTuckman'}

Message: SERVER-47826 Respect when internalQueryEnumerationMaxOrSolutions is set to zero
Branch: master
https://github.com/mongodb/mongo/commit/2667abf5f988c9cf9b548e23db961680a24c7a1b

Generated at Thu Feb 08 05:15:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.