[SERVER-56020] Unnecessary FETCH stage Created: 11/Apr/21  Updated: 25/Apr/21  Resolved: 20/Apr/21

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Performance, Query Execution
Affects Version/s: 4.0.16
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kay Agahd Assignee: Edwin Zhou
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-28889 Partial index shouldn't do fetch on c... Closed
Operating System: ALL
Steps To Reproduce:

Here is a simplified example:

for(x=0;x<1000;x++){db.test.insert(
{
	productId:x,
	price:x,
	del:false
}
)}
 
db.test.createIndex(
	{ productId:1, price:1 },
	{ partialFilterExpression: { del: false } }
)
 
db.test.explain(true).find({
	productId:500,
	del:false
},
{
	price:1
})

Here are the lines of the executionStats to show that the query was not covered because MongoDB fetched the whole document just to read the already known value of field del:

"executionStats" : {
		"nReturned" : 1,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 1,
		"executionStages" : {
			"stage" : "PROJECTION",
			"nReturned" : 1,
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"del" : {
						"$eq" : false
					}
				},
				"nReturned" : 1,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 1,
					"keyPattern" : {
						"productId" : 1,
						"price" : 1
					},

Participants:

 Description   

When using a partialFilterExpression with an $eq condition, MongoDB may fetch the document only to read the field of the $eq condition of the the partialFilterExpression. However, fetching the value should be superfluous because the value is already known by the partialFilterExpression.

This is an undesired behaviour because it fetches uselessly documents from the disk which makes the query slow (especially, if documents are bigger).

To circumvent the issue, you'd need to add the field of the $eq condition of the partialFilterExpression to the index. However, this makes the index bigger and as such it will uses more resources.



 Comments   
Comment by Kay Agahd [ 25/Apr/21 ]

HiĀ edwin.zhou,
thank you very much for the pointer. Indeed, it's a duplicated issue that I wasn't aware of.
Best,
Kay

Comment by Edwin Zhou [ 20/Apr/21 ]

Hi kay.agahd@idealo.de,

Thank you for your detailed description of this behavior. I believe this issue duplicates SERVER-28889, which describes that partial indexes shouldn't trigger a fetch when the conditions are already met by the index.

Best,
Edwin

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