I have a query that uses an index but during the fetch is looking up too many documents.
The index in question is:
{
“v” : 2,
“key” : {
“vw” : -1,
“if” : 1,
“sa” : 1,
“dd” : -1,
“ca” : 1
},
“name” : “Viewed_By_Category”,
“ns” : “redacted”,
“background” : false
}
the query in question:
db.stories.find({ 'if': {$ne: true}, 'sa': 2, 'dd': {$ne : null}, 'ca': 11}).skip(3990).limit(30).sort({'vw':-1}).explain('executionStats')*
and this is the explain output:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "lushstories.stories",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 30,
"inputStage" : {
"stage" : "SKIP",
"skipAmount" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"vw" : -1,
"if" : 1,
"sa" : 1,
"dd" : -1,
"ca" : 1
},
"indexName" : "Viewed_By_Category",
"isMultiKey" : false,
"multiKeyPaths" : {
"vw" : [ ],
"if" : [ ],
"sa" : [ ],
"dd" : [ ],
"ca" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"vw" : [
"[MaxKey, MinKey]"
],
"if" : [
"[MinKey, MaxKey]"
],
"sa" : [
"[MinKey, MaxKey]"
],
"dd" : [
"[MaxKey, MinKey]"
],
"ca" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SKIP",
"skipAmount" : 3990,
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"vw" : -1
},
"limitAmount" : 4020,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"dd" : {
"$not" : {
"$eq" : null
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"dd" : -1,
"if" : 1,
"sa" : 1,
"ca" : 1,
"ha" : 1
},
"indexName" : "Story_Visible_With_Audio",
"isMultiKey" : false,
"multiKeyPaths" : {
"dd" : [ ],
"if" : [ ],
"sa" : [ ],
"ca" : [ ],
"ha" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"dd" : [
"[MaxKey, null)",
"(null, MinKey]"
],
"if" : [
"[MinKey, true)",
"(true, MaxKey]"
],
"sa" : [
"[2.0, 2.0]"
],
"ca" : [
"[11.0, 11.0]"
],
"ha" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 30,
"executionTimeMillis" : 5500,
"totalKeysExamined" : 55743,
"totalDocsExamined" : 55743,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 30,
"executionTimeMillisEstimate" : 5372,
"works" : 55744,
"advanced" : 30,
"needTime" : 55713,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 30,
"inputStage" : {
"stage" : "SKIP",
"nReturned" : 30,
"executionTimeMillisEstimate" : 5372,
"works" : 55743,
"advanced" : 30,
"needTime" : 55713,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"skipAmount" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"nReturned" : 4020,
"executionTimeMillisEstimate" : 5372,
"works" : 55743,
"advanced" : 4020,
"needTime" : 51723,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 55743,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 55743,
"executionTimeMillisEstimate" : 80,
"works" : 55743,
"advanced" : 55743,
"needTime" : 0,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"vw" : -1,
"if" : 1,
"sa" : 1,
"dd" : -1,
"ca" : 1
},
"indexName" : "Viewed_By_Category",
"isMultiKey" : false,
"multiKeyPaths" : {
"vw" : [ ],
"if" : [ ],
"sa" : [ ],
"dd" : [ ],
"ca" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"vw" : [
"[MaxKey, MinKey]"
],
"if" : [
"[MinKey, MaxKey]"
],
"sa" : [
"[MinKey, MaxKey]"
],
"dd" : [
"[MaxKey, MinKey]"
],
"ca" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 55743,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "redacted",
"port" : 27017,
"version" : "4.0.9",
"gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
},
"ok" : 1
}
So why is the IXSCAN scan stage not using any of the predicates to filter, the indexBounds are all using [MaxKey, MinKey]
This is returning the full number of records 55743 which is being fed into the fetch.
Is there something I don't understand about these indexes?
Thanks
