Does MongoDB fetch entire document even if single field is projected?

Question

I have a MongoDB Collection for weather data with each document consisting about 50 different weather parameters fields. Simple Example below:

{
"wind":7,
"swell":6,
"temp":32,
...
"50th_field":32
}

If I only need one field from all documents, say temp, my query would be this:

db.weather.find({},{ temp: 1})

So internally, does MongoDB has to fetch the entire document for just 1 field which was requested(projected)? Wouldn't it be an expensive operation?

I tried MongoDB Compass to benchmark timings, but the time required was <1ms so couldn't figure out.

Minsky · Answer 1 · 2020-11-18T04:47:29.957

1

Yes. This is how to avoid it:

create an index on temp
Use find(Temp)
turn off _id (necessary).

Run:

db.coll.find({ temp:{ $ne:null }},{ temp:1, _id:0 })`

{} triggers collscan because the algorithm tries to match the query fields with project

With {temp}, {temp, _id:0} it says: "Oh, I only need temp".

It should also be smart to tell that {}, {temp, _id:0} only needs index, but it's not.

edited Nov 18 '20 at 04:47

answered Nov 10 '20 at 03:04

Minsky

2,277
10
19

this is not a good answer.. see the edited question when it comes out, please – MartianMartian Jul 03 '21 at 04:26
what do you mean? @Martian2049 – Minsky Jul 03 '21 at 14:13
the review is in process...https://stackoverflow.com/review/suggested-edits/29321128 – MartianMartian Jul 06 '21 at 12:04
@Martian2049 indexes data are always in memory, if you query 1 field that is indexed and run the right query, it will be fast. – Minsky Jul 06 '21 at 13:09

score 1 · Answer 2 · answered Nov 10 '20 at 09:10

1

MonogDB will read all data, however only field temp (and _id) will be transmitted over your network to the client. In case your document are rather big, then the over all performance should be better when you project only the fields you need to get.

answered Nov 10 '20 at 09:10

Wernfried Domscheit

54,457
9
76
110

score 0 · Answer 3 · answered Nov 10 '20 at 03:51

Basically using projection with limiting fields is always faster then fetch full document, You can even use the covered index to avoid examining the documents(no disk IO) the archive better performance.

Check the executionStats of demo below, the totalDocsExamined was 0! but you must remove the _id field in projection because it's not included in index.

See also: https://docs.mongodb.com/manual/core/query-optimization/#covered-query

> db.test.insertOne({name: 'TJT'})
{
  "acknowledged" : true,
  "insertedId" : ObjectId("5faa0c8469dffee69357dde3")
}
> db.test.createIndex({name: 1})
{
  "createdCollectionAutomatically" : false,
  "numIndexesBefore" : 1,
  "numIndexesAfter" : 2,
  "ok" : 1
}

db.test.explain('executionStats').find({name: 'TJT'}, {_id: 0, name: 1})
{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "memo.test",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "name" : {
        "$eq" : "TJT"
      }
    },
    "winningPlan" : {
      "stage" : "PROJECTION",
      "transformBy" : {
        "_id" : 0,
        "name" : 1
      },
      "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
          "name" : 1
        },
        "indexName" : "name_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
          "name" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
          "name" : [
            "[\"TJT\", \"TJT\"]"
          ]
        }
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 1,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 1,
    "totalDocsExamined" : 0,
    "executionStages" : {
      "stage" : "PROJECTION",
      "nReturned" : 1,
      "executionTimeMillisEstimate" : 0,
      "works" : 2,
      "advanced" : 1,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 0,
      "restoreState" : 0,
      "isEOF" : 1,
      "invalidates" : 0,
      "transformBy" : {
        "_id" : 0,
        "name" : 1
      },
      "inputStage" : {
        "stage" : "IXSCAN",
        "nReturned" : 1,
        "executionTimeMillisEstimate" : 0,
        "works" : 2,
        "advanced" : 1,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 0,
        "restoreState" : 0,
        "isEOF" : 1,
        "invalidates" : 0,
        "keyPattern" : {
          "name" : 1
        },
        "indexName" : "name_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
          "name" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
          "name" : [
            "[\"TJT\", \"TJT\"]"
          ]
        },
        "keysExamined" : 1,
        "seeks" : 1,
        "dupsTested" : 0,
        "dupsDropped" : 0,
        "seenInvalidated" : 0
      }
    }
  }
}

Does MongoDB fetch entire document even if single field is projected?

3 Answers3