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

Unnecessary FETCH stage

XMLWordPrintableJSON

    • ALL
    • Hide

      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
					},
      Show
      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 },

      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.

            Assignee:
            edwin.zhou@mongodb.com Edwin Zhou
            Reporter:
            kay.agahd@idealo.de Kay Agahd
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: