Query find() - Will anyone pls explain me this behaviour?

[azzy _home]

Following are 4 similar queries to get count with find.

Query 1: Gives me wrong count (returns total documents count)

db.test.tourArrivals.find({
  "tour.stops.locationId": locationId
}).max({
  "scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:37.9960000+02:00"
}).min({
  "scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:10.8910000+02:00"
}).size();

Execution time: 0,1s

Query 2: explain() shows correct count returned???

db.test.tourArrivals.find({
  "tour.stops.locationId": locationId
}).max({
  "scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:37.9960000+02:00"
}).min({
  "scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:10.8910000+02:00"
}).explain("executionStats").executionStats.nReturned;

Execution time: 0,3s

Query 3: using find and $and with count

db.test.tourArrivals.find({
  $and: [
    {
      "scheduleInfo.stopSchedules.lastETAResult": {
        $gte: "2016-05-04T15:29:10.8910000+02:00"
      }
    },
    {
      "scheduleInfo.stopSchedules.lastETAResult": {
        $lte: "2016-05-04T15:29:37.9960000+02:00"
      }
    },
    {
      "tour.stops.locationId": locationId
    }
  ]
}).count();

Execution time: 0,8s

Query 4: same as 3, get count from explain()

db.test.tourArrivals.find({
  $and: [
    {
      "scheduleInfo.stopSchedules.lastETAResult": {
        $gte: "2016-05-04T15:29:10.8910000+02:00"
      }
    },
    {
      "scheduleInfo.stopSchedules.lastETAResult": {
        $lte: "2016-05-04T15:29:37.9960000+02:00"
      }
    },
    {
      "tour.stops.locationId": locationId
    }
  ]
}).explain("executionStats").executionStats.nReturned;

Execution time: 0,5s


My question:
1. Why are the explain("executionStats") faster than normal count()/size()?
2. Why does the 1. query return count of all document but the explain(..).executionStats.nReturned gives me correct count?


Output in NoSQL Manager:
// 10.05.2016 18:31:35
// Command #3:
// db.test.tourArrivals.find({"tour.stops.locationId":locationId}).max({"scheduleInfo.stopSchedules.lastETAResult":"2016-05-04T15:29:37.9960000+02:00"}).min({"scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:10.8910000+02:00"}).size();
// Execution time: 0,1s
// Result:
13629

// 10.05.2016 18:31:35
// Command #4:
// db.test.tourArrivals.find({"tour.stops.locationId":locationId}).max({"scheduleInfo.stopSchedules.lastETAResult":"2016-05-04T15:29:37.9960000+02:00"}).min({"scheduleInfo.stopSchedules.lastETAResult": "2016-05-04T15:29:10.8910000+02:00"}).explain("executionStats").executionStats.nReturned;
// Execution time: 0,3s
// Result:
4638

// 10.05.2016 18:31:36
// Command #5:
// db.test.tourArrivals.find({$and: [
// {"scheduleInfo.stopSchedules.lastETAResult":{$gte: "2016-05-04T15:29:10.8910000+02:00"}},
// {"scheduleInfo.stopSchedules.lastETAResult":{$lte: "2016-05-04T15:29:37.9960000+02:00"}},
// {"tour.stops.locationId":locationId}
// ]}).count();
// Execution time: 0,8s
// Result:
4639

// 10.05.2016 18:31:36
// Command #6:
// db.test.tourArrivals.find({$and: [
// {"scheduleInfo.stopSchedules.lastETAResult":{$gte: "2016-05-04T15:29:10.8910000+02:00"}},
// {"scheduleInfo.stopSchedules.lastETAResult":{$lte: "2016-05-04T15:29:37.9960000+02:00"}},
// {"tour.stops.locationId":locationId}
// ]}).explain("executionStats").executionStats.nReturned;
// Execution time: 0,5s
// Result:
4639


Regards,

[Kevin Adistambha]

Hi,

Why are the explain(“executionStats”) faster than normal count()/size()?

I am assuming you are referring to queries #3 and #4. The actual performance numbers will be very specific to your deployment and database content, but there are some things that you could try:

• Did you run #4 immediately after #3? Have you tried reversing the order and see a similar result?
• The count() method only counts the number of documents referenced by a cursor, and does not perform the same operation as a find() query. If you are using MongoDB 3.0.x and above, you could try the command db.test.tourArrivals.explain('executionStats').find(...).count() and see that it’s a different plan compared to db.test.tourArrivals.explain('executionStats').find(...).
• Have you tried running queries #3 and #4 multiple times (e.g. 1000 times each) to minimize the influence of caching, working set changes, etc. between the two queries, and confirm that the execution timing of the two queries stay consistent?

Why does the 1. query return count of all document but the explain(..).executionStats.nReturned gives me correct count?

The size() operator is a cursor method, which returns a count of the number of documents that match the find() query after application of skip() and limit() cursor methods, but not max() or min() cursor methods. Therefore the query db.collection.find(...).count() will output the same number as db.collection.find(...).max(...).min(...).count(), since the max() and min() methods are ignored by the count() method.

The query planner executionStats mode executes the winning plan to completion and returns statistics describing the execution. This is why the number returned by nReturned shows the actual document count after the query was executed, which includes the min() and max() method application on the cursor.

Best regards,
Kevin

​