user1324887
Reputation: 662
Mongo date range index with filters
We have the below query
db.Comment.find(
{
$and: [
{ reportCount: { $gt: 0 } },
{ assignee: { $exists: false } },
{ creationDate: { $gt: new Date(1507831097809) } },
{ creationDate: { $lt: new Date(1508522297966) } },
{ siteId: 'MAIN' },
{ parent: { $exists: false } },
{ status: 'ACTIVE' }
]
})
.sort({ creationDate: 1 })
And we have an index
{
"v" : 2,
"key" : {
"creationDate" : 1,
"reportCount" : 1,
"label" : 1
}
}
Here are explain results:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "myNameSpace",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"creationDate" : 1.0,
"reportCount" : 1.0,
"label" : 1.0
},
"indexName" : "creationDate_1_reportCount_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"creationDate" : [],
"reportCount" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"creationDate" : [
"(new Date(1507831097809), new Date(1508522297966))"
],
"reportCount" : [
"(0.0, inf.0]"
],
"label" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"parent" : 1.0
},
"indexName" : "parent_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"parent" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"parent" : [
"[null, null]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"assignee" : 1.0
},
"indexName" : "assignee_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"assignee" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"assignee" : [
"[null, null]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"siteId" : 1.0,
"updatedDate" : 1.0,
"label" : 1.0
},
"indexName" : "siteId_1_updatedDate_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"siteId" : [],
"updatedDate" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"siteId" : [
"[\"MAIN\", \"MAIN\"]"
],
"updatedDate" : [
"[MinKey, MaxKey]"
],
"label" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"creationDate" : 1.0
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"creationDate" : {
"$lt" : ISODate("2017-10-20T17:58:17.966Z")
}
},
{
"creationDate" : {
"$gt" : ISODate("2017-10-12T17:58:17.809Z")
}
},
{
"reportCount" : {
"$gt" : 0.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"parent" : 1.0
},
"indexName" : "parent_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"parent" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"parent" : [
"[null, null]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"assignee" : 1.0
},
"indexName" : "assignee_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"assignee" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"assignee" : [
"[null, null]"
]
}
}
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 19,
"executionTimeMillis" : 8,
"totalKeysExamined" : 533,
"totalDocsExamined" : 56,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"siteId" : {
"$eq" : "MAIN"
}
},
{
"status" : {
"$eq" : "ACTIVE"
}
},
{
"$nor" : [
{
"assignee" : {
"$exists" : true
}
}
]
},
{
"$nor" : [
{
"parent" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 19,
"executionTimeMillisEstimate" : 0,
"works" : 534,
"advanced" : 19,
"needTime" : 513,
"needYield" : 0,
"saveState" : 20,
"restoreState" : 20,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 56,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 56,
"executionTimeMillisEstimate" : 0,
"works" : 533,
"advanced" : 56,
"needTime" : 476,
"needYield" : 0,
"saveState" : 20,
"restoreState" : 20,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"creationDate" : 1.0,
"reportCount" : 1.0,
"label" : 1.0
},
"indexName" : "creationDate_1_reportCount_1_label_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"creationDate" : [],
"reportCount" : [],
"label" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"creationDate" : [
"(new Date(1507831097809), new Date(1508522297966))"
],
"reportCount" : [
"(0.0, inf.0]"
],
"label" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 533,
"seeks" : 477,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"ok" : 1.0
}
The query is still taking 700-800 ms to return the data. How can I change the index to make the query run faster? Don't consider "keysExamined" : 533, "seeks" : 477, This data. This is just test data.
Looks like its using an index but only the first field in the index? Also multuKey is false?
Upvotes: 2
Views: 4434
Answers (1)
glytching
Reputation: 47885
A few key points from the explain plan output:
- The query addresses the following attributes:
siteId, status, creationDate, reportCount, assignee, parent - The winning plan has two stages:
- IX_SCAN uses
creationDate_1_reportCount_1_label_1, this uses indexed lookups oncreationDateandreportCountto identify 56 documents which are then forwarded to the FETCH stage - FETCH receives 56 documents from the IX_SCAN stage and then interrogates these documents to apply the
siteId,status,assigneeandparentfilters. This interrogation causes 37 documents to be discarded resulting in 19 document to be returned.
- IX_SCAN uses
So, your index covers just 2 of the 6 attributes in your query and the remaining 4 attributes in your query are applied by examining the documents not the index. If you want this query to be fully index covered then create the following index:
db.collection.createIndex(
{siteId: 1, status: 1, creationDate: 1, reportCount: 1, assignee: 1, parent: 1}
)
If you re run with this index in place then you should find that (a) MongoDB chooses this index and (b) the number of documents forwarded by the IX_SCAN stage is the same as the number of documents returned by your find call.
I say "should find" because there are other aspects here which might result in MongoDB choosing a different index e.g. use of $nor and the sort stage (creationDate: 1). I would recommend tweaking the index and running with explain 'on' after each tweak and looking for these key items in the executionStats sub document:
- "nReturned"
- "totalKeysExamined"
- "totalDocsExamined"
A simple rule of thumb is this: the closer totalKeysExamined is to nReturned and the closer totalDocsExamined is to zero ... the better your index coverage.
There is also the question of the cost of an index (in terms of impact on write times and index storage) so I'd suggest considering your non functional requirements - can your desired elapsed times be achieved without full index coverage? If not, then you should proceed with empirical testing but be prepared to tweak your choice in reponse to what the explain() output tells you.
Upvotes: 4
Related Questions
- MongoDB - Query with date range and other field
- Array of dates matches my date range
- How to query date range in mongo, where field itself is a date
- MongoDB: Find document in specific date range
- date range query in mongodb 3.6
- Mongo query for multiple date ranges
- Querying for a Date Range
- Mongodb - how can I query by date range?
- How to query for objects between date ranges
- MongoDB : Querying collection within a Date Range