Hi,
We’ve got a collection of 58 000 objects (not that big, heh?)
find({employerId: ObjectId(‘badf90fe0546862aa50fe845’),erased: {$ne: true}, parentReport: {$exists: false}}).sort({createdAt: -1}).limit(50)
And last week, we got a problem where the sort would use more than 32mb. So we created more indexes but we still think they’re not optimal as the query is still very slow at > 2200ms (where other queries take less than 50ms in our db).
It says it’s using this index:
db.AssistantReports.createIndex({ employerId:1, erased: 1, parentReport:1, createdAt: -1 }, { background: true, name: ‘FAST_Publish_AssistantReports_TITLES_PAGED3’ })
Which seems right to me.
When we don’t specify a limit, it takes 15-25 seconds and it shows it examines ALL documents of the Collection instead of just those having the same employerId for instance (around 2500).
What is wrong with the index or the query ?
Best regards,
Here are the results of the explain:
{"queryPlanner":{"plannerVersion":1,"namespace":"iamw.AssistantReports","indexFilterSet":false,"parsedQuery":{"$and":[{"employerId":{"$eq":"badf90fe0546862aa50fe845"}},{"erased":{"$not":{"$eq":true}}},{"parentReport":{"$not":{"$exists":true}}}]},"winningPlan":{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"erased":1,"parentReport":1,"createdAt":-1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED_reverse2","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"parentReport":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"parentReport":["[null, null]"],"createdAt":["[MaxKey, MinKey]"]}}}}},"rejectedPlans":[{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"erased":1,"createdAt":1,"parentReport":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED3","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[],"parentReport":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":1,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MinKey, MaxKey]"],"parentReport":["[null, null]"]}}}}},{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"dateBegin":1,"dateEnd":1,"reportType":1,"erased":1},"indexName":"FAST_Publish_AssistantReports_QUERY","isMultiKey":false,"multiKeyPaths":{"employerId":[],"dateBegin":[],"dateEnd":[],"reportType":[],"erased":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":1,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"dateBegin":["[MinKey, MaxKey]"],"dateEnd":["[MinKey, MaxKey]"],"reportType":["[MinKey, MaxKey]"],"erased":["[MinKey, true)","(true, MaxKey]"]}}}}},{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"erased":1,"parentReport":1,"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"parentReport":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"parentReport":["[null, null]"],"createdAt":["[MinKey, MaxKey]"]}}}}},{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"erased":1,"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED4_1","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MinKey, MaxKey]"]}}}}},{"stage":"SORT","sortPattern":{"createdAt":-1},"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"inputStage":{"stage":"IXSCAN","keyPattern":{"employerId":1,"erased":1,"createdAt":-1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED4_minus1","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MaxKey, MinKey]"]}}}}},{"stage":"LIMIT","limitAmount":50,"inputStage":{"stage":"FETCH","filter":{"$and":[{"employerId":{"$eq":"badf90fe0546862aa50fe845"}},{"erased":{"$not":{"$eq":true}}},{"parentReport":{"$not":{"$exists":true}}}]},"inputStage":{"stage":"IXSCAN","keyPattern":{"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED_sortonly","isMultiKey":false,"multiKeyPaths":{"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"backward","indexBounds":{"createdAt":["[MaxKey, MinKey]"]}}}}]},"executionStats":{"executionSuccess":true,"nReturned":50,"executionTimeMillis":1206,"totalKeysExamined":2327,"totalDocsExamined":2324,"executionStages":{"stage":"SORT","nReturned":50,"executionTimeMillisEstimate":269,"works":2379,"advanced":50,"needTime":2328,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"sortPattern":{"createdAt":-1},"memUsage":22336030,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2324,"executionTimeMillisEstimate":252,"works":2328,"advanced":2324,"needTime":3,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2324,"executionTimeMillisEstimate":248,"works":2327,"advanced":2324,"needTime":2,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"docsExamined":2324,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2324,"executionTimeMillisEstimate":5,"works":2327,"advanced":2324,"needTime":2,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"keyPattern":{"employerId":1,"erased":1,"parentReport":1,"createdAt":-1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED_reverse2","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"parentReport":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"parentReport":["[null, null]"],"createdAt":["[MaxKey, MinKey]"]},"keysExamined":2327,"seeks":3,"dupsTested":0,"dupsDropped":0}}}},"allPlansExecution":[{"nReturned":50,"executionTimeMillisEstimate":269,"totalKeysExamined":2327,"totalDocsExamined":2324,"executionStages":{"stage":"SORT","nReturned":50,"executionTimeMillisEstimate":269,"works":2378,"advanced":50,"needTime":2328,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"sortPattern":{"createdAt":-1},"memUsage":22336030,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2324,"executionTimeMillisEstimate":252,"works":2328,"advanced":2324,"needTime":3,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2324,"executionTimeMillisEstimate":248,"works":2327,"advanced":2324,"needTime":2,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"docsExamined":2324,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2324,"executionTimeMillisEstimate":5,"works":2327,"advanced":2324,"needTime":2,"needYield":0,"saveState":136,"restoreState":136,"isEOF":1,"keyPattern":{"employerId":1,"erased":1,"parentReport":1,"createdAt":-1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED_reverse2","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"parentReport":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"parentReport":["[null, null]"],"createdAt":["[MaxKey, MinKey]"]},"keysExamined":2327,"seeks":3,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":0,"executionTimeMillisEstimate":132,"totalKeysExamined":2361,"totalDocsExamined":2030,"executionStages":{"stage":"SORT","nReturned":0,"executionTimeMillisEstimate":132,"works":2363,"advanced":0,"needTime":2362,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"sortPattern":{"createdAt":-1},"memUsage":33677830,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2030,"executionTimeMillisEstimate":60,"works":2362,"advanced":2030,"needTime":332,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2030,"executionTimeMillisEstimate":57,"works":2361,"advanced":2030,"needTime":331,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":2030,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2030,"executionTimeMillisEstimate":5,"works":2361,"advanced":2030,"needTime":331,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"employerId":1,"erased":1,"createdAt":1,"parentReport":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED3","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[],"parentReport":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":1,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MinKey, MaxKey]"],"parentReport":["[null, null]"]},"keysExamined":2361,"seeks":332,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":0,"executionTimeMillisEstimate":65,"totalKeysExamined":1382,"totalDocsExamined":1344,"executionStages":{"stage":"SORT","nReturned":0,"executionTimeMillisEstimate":65,"works":1384,"advanced":0,"needTime":1383,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"sortPattern":{"createdAt":-1},"memUsage":46521086,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":1104,"executionTimeMillisEstimate":45,"works":1383,"advanced":1104,"needTime":279,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":1104,"executionTimeMillisEstimate":45,"works":1382,"advanced":1104,"needTime":278,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":1344,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":1344,"executionTimeMillisEstimate":0,"works":1382,"advanced":1344,"needTime":38,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"employerId":1,"dateBegin":1,"dateEnd":1,"reportType":1,"erased":1},"indexName":"FAST_Publish_AssistantReports_QUERY","isMultiKey":false,"multiKeyPaths":{"employerId":[],"dateBegin":[],"dateEnd":[],"reportType":[],"erased":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":1,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"dateBegin":["[MinKey, MaxKey]"],"dateEnd":["[MinKey, MaxKey]"],"reportType":["[MinKey, MaxKey]"],"erased":["[MinKey, true)","(true, MaxKey]"]},"keysExamined":1382,"seeks":39,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":0,"executionTimeMillisEstimate":171,"totalKeysExamined":2030,"totalDocsExamined":2030,"executionStages":{"stage":"SORT","nReturned":0,"executionTimeMillisEstimate":171,"works":2032,"advanced":0,"needTime":2031,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"sortPattern":{"createdAt":-1},"memUsage":33677830,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2030,"executionTimeMillisEstimate":99,"works":2031,"advanced":2030,"needTime":1,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2030,"executionTimeMillisEstimate":96,"works":2030,"advanced":2030,"needTime":0,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":2030,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2030,"executionTimeMillisEstimate":1,"works":2030,"advanced":2030,"needTime":0,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"employerId":1,"erased":1,"parentReport":1,"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"parentReport":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"parentReport":["[null, null]"],"createdAt":["[MinKey, MaxKey]"]},"keysExamined":2030,"seeks":1,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":0,"executionTimeMillisEstimate":127,"totalKeysExamined":2361,"totalDocsExamined":2361,"executionStages":{"stage":"SORT","nReturned":0,"executionTimeMillisEstimate":127,"works":2363,"advanced":0,"needTime":2362,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"sortPattern":{"createdAt":-1},"memUsage":33677830,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2030,"executionTimeMillisEstimate":32,"works":2362,"advanced":2030,"needTime":332,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2030,"executionTimeMillisEstimate":32,"works":2361,"advanced":2030,"needTime":331,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":2361,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2361,"executionTimeMillisEstimate":0,"works":2361,"advanced":2361,"needTime":0,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"employerId":1,"erased":1,"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED4_1","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MinKey, MaxKey]"]},"keysExamined":2361,"seeks":1,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":0,"executionTimeMillisEstimate":162,"totalKeysExamined":2377,"totalDocsExamined":2377,"executionStages":{"stage":"SORT","nReturned":0,"executionTimeMillisEstimate":162,"works":2378,"advanced":0,"needTime":2378,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"sortPattern":{"createdAt":-1},"memUsage":22336030,"memLimit":33554432,"limitAmount":50,"inputStage":{"stage":"SORT_KEY_GENERATOR","nReturned":2101,"executionTimeMillisEstimate":150,"works":2378,"advanced":2101,"needTime":277,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"inputStage":{"stage":"FETCH","filter":{"parentReport":{"$not":{"$exists":true}}},"nReturned":2101,"executionTimeMillisEstimate":148,"works":2377,"advanced":2101,"needTime":276,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":2377,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2377,"executionTimeMillisEstimate":4,"works":2377,"advanced":2377,"needTime":0,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"employerId":1,"erased":1,"createdAt":-1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED4_minus1","isMultiKey":false,"multiKeyPaths":{"employerId":[],"erased":[],"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"forward","indexBounds":{"employerId":["[ObjectId('badf90fe0546862aa50fe845'), ObjectId('badf90fe0546862aa50fe845')]"],"erased":["[MinKey, true)","(true, MaxKey]"],"createdAt":["[MaxKey, MinKey]"]},"keysExamined":2377,"seeks":1,"dupsTested":0,"dupsDropped":0}}}}},{"nReturned":41,"executionTimeMillisEstimate":52,"totalKeysExamined":2378,"totalDocsExamined":2378,"executionStages":{"stage":"LIMIT","nReturned":41,"executionTimeMillisEstimate":52,"works":2378,"advanced":41,"needTime":2337,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"limitAmount":50,"inputStage":{"stage":"FETCH","filter":{"$and":[{"employerId":{"$eq":"badf90fe0546862aa50fe845"}},{"erased":{"$not":{"$eq":true}}},{"parentReport":{"$not":{"$exists":true}}}]},"nReturned":41,"executionTimeMillisEstimate":52,"works":2378,"advanced":41,"needTime":2337,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"docsExamined":2378,"alreadyHasObj":0,"inputStage":{"stage":"IXSCAN","nReturned":2378,"executionTimeMillisEstimate":3,"works":2378,"advanced":2378,"needTime":0,"needYield":0,"saveState":136,"restoreState":136,"isEOF":0,"keyPattern":{"createdAt":1},"indexName":"FAST_Publish_AssistantReports_TITLES_PAGED_sortonly","isMultiKey":false,"multiKeyPaths":{"createdAt":[]},"isUnique":false,"isSparse":false,"isPartial":false,"indexVersion":2,"direction":"backward","indexBounds":{"createdAt":["[MaxKey, MinKey]"]},"keysExamined":2378,"seeks":1,"dupsTested":0,"dupsDropped":0}}}}]},"serverInfo":{"host":"atlas-ykej2l-shard-00-00.a16u7.mongodb.net","port":27017,"version":"4.2.15","gitVersion":"d7fd78dead621a539c20791a93abec34bb1be385"},"ok":1,"$clusterTime":{"clusterTime":{"$timestamp":"6999635097099960321"},"signature":{"hash":"2WAjlmzbWAf15PihtPglFaFY934=","keyId":{"low":3,"high":1624322872,"unsigned":false}}},"operationTime":{"$timestamp":"6999635097099960321"}}