Query is slow despite index
39 views
Skip to first unread message
Meter App
Apr 19, 2017, 4:59:07 PM4/19/17
to mongodb-user
Hi, I have a db with about 6MM entries and have indexes on "startDate" and "deviceID" fields. Filtering and sorting seems to be unreasonably slow.
db.trips.find({"deviceID": {$in: ['2FC96F6D-4094-46B9-994E-3208FF4DFCE8']}}).explain() is almost instant.
db.trips.find({"deviceID": {$in: ['2FC96F6D-4094-46B9-994E-3208FF4DFCE8']}}).sort({"startDate": -1}).limit(50).explain() .... Takes about 13 seconds
Can someone take a look at the query plan and see what is wrong? Why does it seem to have 2 identical "inputStages" for the second query?
OUTPUT: db.trips.find({"deviceID": {$in: ['2FC96F6D-4094-46B9-994E-3208FF4DFCE8']}}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "ubermeter.trips",
"indexFilterSet" : false,
"parsedQuery" : {
"deviceID" : {
"$in" : [
"2FC96F6D-4094-46B9-994E-3208FF4DFCE8"
]
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"deviceID" : 1
},
"indexName" : "deviceID_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"deviceID" : [
"[\"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\", \"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"ok" : 1
}
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "ubermeter.trips",
"indexFilterSet" : false,
"parsedQuery" : {
"deviceID" : {
"$in" : [
"2FC96F6D-4094-46B9-994E-3208FF4DFCE8"
]
}
},
"winningPlan" : {
"stage" : "OR",
"inputStages" : [
{
"stage" : "SORT",
"sortPattern" : {
"startDate" : -1
},
"limitAmount" : 50,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"deviceID" : 1
},
"indexName" : "deviceID_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"deviceID" : [
"[\"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\", \"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\"]"
]
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"startDate" : -1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"deviceID" : 1
},
"indexName" : "deviceID_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"deviceID" : [
"[\"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\", \"2FC96F6D-4094-46B9-994E-3208FF4DFCE8\"]"
]
}
}
}
}
]
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"deviceID" : {
"$in" : [
"2FC96F6D-4094-46B9-994E-3208FF4DFCE8"
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"startDate" : 1
},
"indexName" : "startDate_1",
"isMultiKey" : false,
"direction" : "backward",
"indexBounds" : {
"startDate" : [
"[MaxKey, MinKey]"
]
}
}
}
]
},
"serverInfo" : {
"host" : "ubermeter-database",
"port" : 27017,
"version" : "3.0.12",
"gitVersion" : "33934938e0e95d534cebbaff656cde916b9c3573"
},
"ok" : 1
}
Here are my indexes too in case that helps:
> db.trips.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "ubermeter.trips"
},
{
"v" : 1,
"key" : {
"startDate" : 1
},
"name" : "startDate_1",
"ns" : "ubermeter.trips",
"background" : true
},
{
"v" : 1,
"key" : {
"deviceID" : 1
},
"name" : "deviceID_1",
"ns" : "ubermeter.trips"
}
]
Weishan Ang
Apr 19, 2017, 5:51:40 PM4/19/17
to mongodb-user
instead of having 2 indexes, could you have a a compound index with deviceID, startdate?
It seems like the sort stage is not using the index. It is performing a in-memory sort.
Message has been deleted
Message has been deleted
Rupa Narayanan
Apr 25, 2017, 10:49:36 PM4/25/17
to mongodb-user
Meter App,
As suggested above by Weishan and omongo, you need to create a compound index that covers both deviceID and startDate so that the query can execute more efficiently using this index. The current indexes defined don't cover both the fields.
To address your second question, please refer to this documentation which describes how MongoDB query planner optimizes to choose the right the query plan based on the indexes you have defined.
The difference between the two input stages is that the first one limits the sorted result set to 50 rows, refer to the line "limitAmount" : 50 in your output. The query planner chooses this winning plan which is used to generate the results.
Finally, I noticed you are on an older version of MongoDB, there have been a lot of improvements on the queryPlanner since, so I highly recommend you upgrade to the latest version 3.4.
Regards,
Rupa
Reply all
Reply to author
Forward
0 new messages