Query optimisation: count() with $and quite slow

43 views
Skip to first unread message

azzy _home

unread,
May 11, 2016, 3:24:32 AM5/11/16
to mongodb-user

I have been scratching my head over this problem long now. My Count query takes aroud ~500ms for 5000 documents and I hope someone can help me optimise it.

Following is my query: 

var from =  NumberLong(Date.parse('2016-01-01T00:00:00.0000000+02:00')/1000);
var until = NumberLong(Date.parse('2016-04-30T00:00:00.0000000+02:00')/1000);



db.tourCloudTest2.count({ $and: [{'scheduleInfo.stopSchedules.lastETAResultUnix':{$gte: from, $lte: until}},{'tour.stops.locationId': {$exists: 1}}]});



My collection contains following schema:

{
  "tour": {
    "stops": [ // stop with locationId
      "locationId": "bla.."
    ],[
      // stop without locationId
    ]
  }"scheduleInfo": {
    "stopSchedules": [
      "lastETAResultUnix": NumberLong("date...")
    ]
  }
}



The above query takes me around 500ms for ~5000 documents without using any index on "lastETAResultUnix" field. Expain(true) with the query gives me this...


{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "TestUnixDate.tourCloudTest2",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        {
          "scheduleInfo.stopSchedules.lastETAResultUnix": {
            "$lte": 1461967200
          }
        },
        {
          "scheduleInfo.stopSchedules.lastETAResultUnix": {
            "$gte": -62135596800
          }
        },
        {
          "tour.stops.locationId": {
            "$exists": true
          }
        }
      ]
    },
    "winningPlan": {
      "stage": "COLLSCAN",
      "filter": {
        "$and": [
          {
            "scheduleInfo.stopSchedules.lastETAResultUnix": {
              "$lte": 1461967200
            }
          },
          {
            "scheduleInfo.stopSchedules.lastETAResultUnix": {
              "$gte": -62135596800
            }
          },
          {
            "tour.stops.locationId": {
              "$exists": true
            }
          }
        ]
      },
      "direction": "forward"
    },
    "rejectedPlans": [
      
    ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 5661,
    "executionTimeMillis": 827,
    "totalKeysExamined": 0,
    "totalDocsExamined": 45302,
    "executionStages": {
      "stage": "COLLSCAN",
      "filter": {
        "$and": [
          {
            "scheduleInfo.stopSchedules.lastETAResultUnix": {
              "$lte": 1461967200
            }
          },
          {
            "scheduleInfo.stopSchedules.lastETAResultUnix": {
              "$gte": -62135596800
            }
          },
          {
            "tour.stops.locationId": {
              "$exists": true
            }
          }
        ]
      },
      "nReturned": 5661,
      "executionTimeMillisEstimate": 780,
      "works": 45304,
      "advanced": 5661,
      "needTime": 39642,
      "needFetch": 0,
      "saveState": 353,
      "restoreState": 353,
      "isEOF": 1,
      "invalidates": 0,
      "direction": "forward",
      "docsExamined": 45302
    },
    "allPlansExecution": [
      
    ]
  },
  "serverInfo": {
    "host": "DAve-build-1",
    "port": 27017,
    "version": "3.0.6",
    "gitVersion": "nogitversion"
  },
  "ok": 1
}

Performance using an index on the lastETAResult field is horrible. Again with explain ...

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "TestUnixDate.tourCloudTest2",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        {
          "scheduleInfo.stopSchedules.lastETAResultUnix": {
            "$lte": 1461967200
          }
        },
        {
          "scheduleInfo.stopSchedules.lastETAResultUnix": {
            "$gte": -62135596800
          }
        },
        {
          "tour.stops.locationId": {
            "$exists": true
          }
        }
      ]
    },
    "winningPlan": {
      "stage": "KEEP_MUTATIONS",
      "inputStage": {
        "stage": "FETCH",
        "filter": {
          "$and": [
            {
              "scheduleInfo.stopSchedules.lastETAResultUnix": {
                "$gte": -62135596800
              }
            },
            {
              "tour.stops.locationId": {
                "$exists": true
              }
            }
          ]
        },
        "inputStage": {
          "stage": "IXSCAN",
          "keyPattern": {
            "scheduleInfo.stopSchedules.lastETAResultUnix": 1
          },
          "indexName": "scheduleInfo.stopSchedules.lastETAResultUnix_1",
          "isMultiKey": true,
          "direction": "forward",
          "indexBounds": {
            "scheduleInfo.stopSchedules.lastETAResultUnix": [
              "[-1.#INF, 1461967200.0]"
            ]
          }
        }
      }
    },
    "rejectedPlans": [
      
    ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 5661,
    "executionTimeMillis": 4631,
    "totalKeysExamined": 76868,
    "totalDocsExamined": 45065,
    "executionStages": {
      "stage": "KEEP_MUTATIONS",
      "nReturned": 5661,
      "executionTimeMillisEstimate": 4380,
      "works": 76869,
      "advanced": 5661,
      "needTime": 71207,
      "needFetch": 0,
      "saveState": 609,
      "restoreState": 609,
      "isEOF": 1,
      "invalidates": 0,
      "inputStage": {
        "stage": "FETCH",
        "filter": {
          "$and": [
            {
              "scheduleInfo.stopSchedules.lastETAResultUnix": {
                "$gte": -62135596800
              }
            },
            {
              "tour.stops.locationId": {
                "$exists": true
              }
            }
          ]
        },
        "nReturned": 5661,
        "executionTimeMillisEstimate": 4360,
        "works": 76869,
        "advanced": 5661,
        "needTime": 71207,
        "needFetch": 0,
        "saveState": 609,
        "restoreState": 609,
        "isEOF": 1,
        "invalidates": 0,
        "docsExamined": 45065,
        "alreadyHasObj": 0,
        "inputStage": {
          "stage": "IXSCAN",
          "nReturned": 45065,
          "executionTimeMillisEstimate": 3750,
          "works": 76869,
          "advanced": 45065,
          "needTime": 31803,
          "needFetch": 0,
          "saveState": 609,
          "restoreState": 609,
          "isEOF": 1,
          "invalidates": 0,
          "keyPattern": {
            "scheduleInfo.stopSchedules.lastETAResultUnix": 1
          },
          "indexName": "scheduleInfo.stopSchedules.lastETAResultUnix_1",
          "isMultiKey": true,
          "direction": "forward",
          "indexBounds": {
            "scheduleInfo.stopSchedules.lastETAResultUnix": [
              "[-1.#INF, 1461967200.0]"
            ]
          },
          "keysExamined": 76868,
          "dupsTested": 76868,
          "dupsDropped": 31803,
          "seenInvalidated": 0,
          "matchTested": 0
        }
      }
    },
    "allPlansExecution": [
      
    ]
  },
  "serverInfo": {
    "host": "DAve-build-1",
    "port": 27017,
    "version": "3.0.6",
    "gitVersion": "nogitversion"
  },
  "ok": 1
}

Thank you for any help.

Asya Kamsky

unread,
May 11, 2016, 11:35:06 AM5/11/16
to mongodb-user
Your query syntax is likely not what you want.  If scheduleInfo.stopSchedules is an array, then you want to use $elemMatch to indicate that you want the same array element to match both $gte and $lte conditions.


Asya




--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.org/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/fa6db41f-b282-45fb-a5e7-b98485055c03%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers

azzy _home

unread,
May 12, 2016, 3:36:24 AM5/12/16
to mongodb-user
Yes, stopSchedules is an array of schedules where one of the field is lastETAResult. I tried your suggestion and got this:

-- with lastETAResult indexed

// db.tourCloudTest2.count({ $and: [{'scheduleInfo.stopSchedules.lastETAResultUnix':{$gte: from, $lte: until}},{'tour.stops.locationId': {$exists: 1}}]});
// Execution time: 5,0s
// Result:
433

// db.tourCloudTest2.count({ $and: [{'scheduleInfo.stopSchedules': { $elemMatch: { 'lastETAResultUnix': {$gte: from, $lte: until }} }},{'tour.stops.locationId': {$exists: 1}}]});
// Execution time: 5,0s
// Result:
433

-- without an index on lastETAResult

// db.tourCloudTest2.count({ $and: [{'scheduleInfo.stopSchedules.lastETAResultUnix':{$gte: from, $lte: until}},{'tour.stops.locationId': {$exists: 1}}]});
// Execution time: 0,5s
// Result:
433

// db.tourCloudTest2.count({ $and: [{'scheduleInfo.stopSchedules': { $elemMatch: { 'lastETAResultUnix': {$gte: from, $lte: until }} }},{'tour.stops.locationId': {$exists: 1}}]});
// Execution time: 0,5s
// Result:
433

Regards,

Asya Kamsky

unread,
May 13, 2016, 8:41:10 PM5/13/16
to mongodb-user
Could you provide us the output of "explain" on the queries?

Asya


--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.org/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/11c76172-b3ff-4533-9313-859d1eda374d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages