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

Unnecessary FETCH stage

    XMLWordPrintableJSON

Details

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

    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.

      Attachments

        Issue Links

          is related to

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-28889 Partial index shouldn't do fetch on conditions that are true by the definition of the index

          • Major - P3 - Major loss of function.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: