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

Unnecessary FETCH stage when querying an array

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Works as Designed
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • ALL
    • Hide

      When querying an array as documented here, MonogDB needs to fetch the whole document to read the queried field although both the projected field and the queried field are in the index:

      db.test2.insert({a:["1"], p:"2"})
db.test2.createIndex({a:1, p:1})
db.test2.explain(true).find({a:["1"]}, {_id:0,p:1})
"executionStats" : {
		"nReturned" : 1,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 1,
...
"stage" : "FETCH",
				"filter" : {
					"a" : {
						"$eq" : [
							"1"
						]
					}
				},

      The same applies when the $elemMatch operator is used:

      db.test2.explain(true).find({a:{$elemMatch:{$eq:"1"}}}, {_id:0,p:1})
"executionStats" : {
		"nReturned" : 1,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 1,
...
"stage" : "FETCH",
				"filter" : {
					"a" : {
						"$elemMatch" : {
							"$eq" : "1"
						}
					}
				},

      However, using the $all operator does not require the FETCH stage:

      db.test2.explain(true).find({a:{$all:["1"]}}, {_id:0,p:1})
"executionStats" : {
		"nReturned" : 1,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 0,

      The same is true for the standard way to query a single element in an array:

      db.test2.explain(true).find({a:"1"}, {_id:0,p:1})
"executionStats" : {
		"nReturned" : 1,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 0,
      Show
      When querying an array as documented here , MonogDB needs to fetch the whole document to read the queried field although both the projected field and the queried field are in the index: db.test2.insert({a:[ "1" ], p: "2" }) db.test2.createIndex({a:1, p:1}) db.test2.explain( true ).find({a:[ "1" ]}, {_id:0,p:1}) "executionStats" : { "nReturned" : 1, "totalKeysExamined" : 1, "totalDocsExamined" : 1, ... "stage" : "FETCH" , "filter" : { "a" : { "$eq" : [ "1" ] } }, The same applies when the $elemMatch operator is used: db.test2.explain( true ).find({a:{$elemMatch:{$eq: "1" }}}, {_id:0,p:1}) "executionStats" : { "nReturned" : 1, "totalKeysExamined" : 1, "totalDocsExamined" : 1, ... "stage" : "FETCH" , "filter" : { "a" : { "$elemMatch" : { "$eq" : "1" } } }, However, using the $all operator does not require the FETCH stage: db.test2.explain( true ).find({a:{$all:[ "1" ]}}, {_id:0,p:1}) "executionStats" : { "nReturned" : 1, "totalKeysExamined" : 1, "totalDocsExamined" : 0, The same is true for the standard way to query a single element in an array: db.test2.explain( true ).find({a: "1" }, {_id:0,p:1}) "executionStats" : { "nReturned" : 1, "totalKeysExamined" : 1, "totalDocsExamined" : 0,

      When querying an array as documented here, MonogDB may need to fetch the whole document to read the queried field although both the projected field and the queried field are in the index.

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

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

              Created:
              Updated:
              Resolved: