[SERVER-39241] Plan scoring incorrectly applies noFetchBonus bonus to all plans when no projection is provided Created: 29/Jan/19  Updated: 29/Oct/23  Resolved: 19/Nov/19

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 4.2.6, 4.3.2, 4.0.19

Type: Bug Priority: Major - P3
Reporter: Chris Harris Assignee: David Percy
Resolution: Fixed Votes: 0
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Problem/Incident
Related
is related to SERVER-39191 Performance regression for counts pos... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v4.2, v4.0
Steps To Reproduce:

use ranktest
db.score.drop()
db.score.createIndexes([{x:1},{x:1,y:1}])
db.setLogLevel(2,'query')
db.score.find({x:1},{_id:0, y:1})
db.score.find({x:1}) 

Then check the log file

Sprint: Query 2019-11-18, Query 2019-12-02
Participants:
Case:
Linked BF Score: 0

 Description   

When a projection is applied to a query, the algorithm to score the plans correctly takes into consideration if a plan is covered or not.  This can be seen in the following log output ("query" component set to level 2) for the operation find({x:123},{_id:0, y:1})

2019-01-28T21:58:09.666-0600 D QUERY    [conn12] Scoring query plan: IXSCAN { x: 1 } planHitEOF=1
2019-01-28T21:58:09.666-0600 D QUERY    [conn12] score(1.0002) = baseScore(1) + productivity((0 advanced)/(1 works) = 0) + tieBreakers(0 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0002)
2019-01-28T21:58:09.666-0600 D QUERY    [conn12] Scoring query plan: IXSCAN { x: 1, y: 1 } planHitEOF=1
2019-01-28T21:58:09.666-0600 D QUERY    [conn12] score(1.0003) = baseScore(1) + productivity((0 advanced)/(1 works) = 0) + tieBreakers(0.0001 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0003)
2019-01-28T21:58:09.667-0600 D QUERY    [conn12] Winning plan: IXSCAN { x: 1, y: 1 } 

As expected, only the plan using the {x:1,y:1} index received a nonzero noFetchBonus value. 

However, when the command is issued without a projection (eg find({x:123})), both of the plans receive the bonus despite the fact that they both contain FETCH stages:

2019-01-28T22:00:04.415-0600 D QUERY    [conn12] Scoring query plan: IXSCAN { x: 1 } planHitEOF=1
2019-01-28T22:00:04.415-0600 D QUERY    [conn12] score(1.0003) = baseScore(1) + productivity((0 advanced)/(1 works) = 0) + tieBreakers(0.0001 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0003)
2019-01-28T22:00:04.415-0600 D QUERY    [conn12] Scoring query plan: IXSCAN { x: 1, y: 1 } planHitEOF=1
2019-01-28T22:00:04.416-0600 D QUERY    [conn12] score(1.0003) = baseScore(1) + productivity((0 advanced)/(1 works) = 0) + tieBreakers(0.0001 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0003)
2019-01-28T22:00:04.416-0600 D QUERY    [conn12] Winning plan: IXSCAN { x: 1 } 

 



 Comments   
Comment by Githook User [ 22/Apr/20 ]

Author:

{'name': 'David Percy', 'email': 'david.percy@mongodb.com', 'username': 'dpercy'}

Message: SERVER-39241 Apply noFetchBonus iff no FETCH stage; disregard PROJECTION
Branch: v4.0
https://github.com/mongodb/mongo/commit/72d82d072a80d447babfd69c10e8116fb25b0ea0

Comment by Githook User [ 31/Mar/20 ]

Author:

{'name': 'David Percy', 'email': 'david.percy@mongodb.com', 'username': 'dpercy'}

Message: SERVER-39241 Apply noFetchBonus iff no FETCH stage; disregard PROJECTION
Branch: v4.2
https://github.com/mongodb/mongo/commit/c281752a3a47f6e8654bd8bd0213b87e36c3ca76

Comment by Githook User [ 19/Nov/19 ]

Author:

{'username': 'dpercy', 'email': 'david.percy@mongodb.com', 'name': 'David Percy'}

Message: SERVER-39241 Apply noFetchBonus iff no FETCH stage; disregard PROJECTION
Branch: master
https://github.com/mongodb/mongo/commit/24821c1d1266ada109dd62ec71d4d8a900fbdec0

Comment by David Storch [ 11/Apr/19 ]

I'm flagging this issue to be re-triaged, since james.wahlin identified a situation in which it is not benign. Quoting his comment from SERVER-14423:

As of SERVER-3645 we perform shard filtering on sharded count operations. In the case where we perform a count on a prefix of the shard key, and where more than one index exists on the shard with that prefix, it is possible that we will choose an index other than the shard key index. This can result in document fetch in order to satisfy the shard key filter, where use of the shard key index would allow for a covered operation.

I produced a short repro script which demonstrates the issue:

(function() {
    "use strict";
 
    const st = new ShardingTest({shards: 2, rs: {nodes: 1}});
    const mongosDb = st.s.getDB("test");
    const coll = mongosDb.coll;
 
    assert.commandWorked(coll.createIndex({a: 1}));
    assert.commandWorked(coll.createIndex({a: 1, b: 1}));
 
    // Shard the collection by {a: 1, b: 1}.
    st.shardColl(coll, {a: 1, b: 1}, {a: 0, b: 0}, {a: 0, b: 0}, mongosDb.getName());
 
    printjson(coll.explain().count({a: 1}));
}());

This script produces explain output such as the following:

{
	"queryPlanner" : {
		"mongosPlannerVersion" : 1,
		"winningPlan" : {
			"stage" : "SINGLE_SHARD",
			"shards" : [
				{
					"shardName" : "repro-rs0",
					"connectionString" : "repro-rs0/storchbox:20020",
					"serverInfo" : {
						"host" : "storchbox",
						"port" : 20020,
						"version" : "0.0.0",
						"gitVersion" : "unknown"
					},
					"plannerVersion" : 1,
					"namespace" : "test.coll",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"a" : {
							"$eq" : 1
						}
					},
					"queryHash" : "4B53BE76",
					"planCacheKey" : "4281AFAD",
					"winningPlan" : {
						"stage" : "SHARDING_FILTER",
						"inputStage" : {
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"a" : 1
								},
								"indexName" : "a_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"a" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"a" : [
										"[1.0, 1.0]"
									]
								}
							}
						}
					},
					"rejectedPlans" : [
						{
							"stage" : "SHARDING_FILTER",
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"a" : 1,
									"b" : 1
								},
								"indexName" : "a_1_b_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"a" : [ ],
									"b" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"a" : [
										"[1.0, 1.0]"
									],
									"b" : [
										"[MinKey, MaxKey]"
									]
								}
							}
						}
					]
				}
			]
		}
	},
	"ok" : 1,
	"operationTime" : Timestamp(1555005739, 18),
	"$clusterTime" : {
		"clusterTime" : Timestamp(1555005739, 18),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	}
}

Here we see that there is a rejected plan which is covered while the winning plan requires a FETCH in order to gather the fields required by the SHARDING_FILTER stage. This problem would be fixed by correcting the logic around noFetchBonus. It should be safe to award the noFetchBonus to any plan that has neither a FETCH or a COLLSCAN stage.

Generated at Thu Feb 08 04:51:28 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.