Each clause in a $or expression can use its own index so when a query has a lot $or and each $or has a lot of clause it can create a very large profiler entry when profiler is enabled.
{ "op" : "query", "ns" : "database.collection", "command" : { "find" : "collection", "filter" : { "$or" : [ { "$and" : [ { "$or" : [ { "field1" : "1163" }, { "field2" : "1163" } ] }, { "field3" : "G3" }, { "field4" : "GYN" } ] }, { "$and" : [ { "$or" : [ { "field1" : "3402" }, { "field2" : "3402" } ] }, { "field3" : "IB" }, { "field4" : "MAD" } ] } ] }, "lsid" : { "id" : UUID("d7f6df2a-1941-4463-9e85-91e050c35f38") }, "$clusterTime" : { "clusterTime" : Timestamp(1614021369, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } }, "$db" : "database" }, "keysExamined" : 25, "docsExamined" : 8, "cursorExhausted" : true, "numYield" : 0, "nreturned" : 8, "locks" : { "Global" : { "acquireCount" : { "r" : NumberLong(1) } }, "Database" : { "acquireCount" : { "r" : NumberLong(1) } }, "Collection" : { "acquireCount" : { "r" : NumberLong(1) } } }, "responseLength" : 53607, "protocol" : "op_msg", "millis" : 0, "planSummary" : "IXSCAN { field3: 1, field1: 1, field5: 1, field6: 1 }, IXSCAN { field2: 1, field5: 1, field7: 1, field3: 1, field4: 1 }, IXSCAN { field3: 1, field1: 1, field5: 1, field6: 1 }, IXSCAN { field2: 1, field5: 1, field7: 1, field3: 1, field4: 1 }", "execStats" : { "stage" : "SUBPLAN", "nReturned" : 8, "executionTimeMillisEstimate" : 0, "works" : 27, "advanced" : 8, "needTime" : 18, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "OR", "nReturned" : 8, "executionTimeMillisEstimate" : 0, "works" : 27, "advanced" : 8, "needTime" : 18, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "dupsTested" : 8, "dupsDropped" : 0, "recordIdsForgotten" : 0, "inputStages" : [ { "stage" : "FETCH", "filter" : { "field4" : { "$eq" : "GYN" } }, "nReturned" : 3, "executionTimeMillisEstimate" : 0, "works" : 14, "advanced" : 3, "needTime" : 10, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 3, "alreadyHasObj" : 0, "inputStage" : { "stage" : "OR", "nReturned" : 3, "executionTimeMillisEstimate" : 0, "works" : 14, "advanced" : 3, "needTime" : 10, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "dupsTested" : 6, "dupsDropped" : 3, "recordIdsForgotten" : 0, "inputStages" : [ { "stage" : "IXSCAN", "nReturned" : 3, "executionTimeMillisEstimate" : 0, "works" : 4, "advanced" : 3, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "field3" : 1, "field1" : 1, "field5" : 1, "field6" : 1 }, "indexName" : "index_1", "isMultiKey" : false, "multiKeyPaths" : { "field3" : [ ], "field1" : [ ], "field5" : [ ], "field6" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "field3" : [ "[\"G3\", \"G3\"]" ], "field1" : [ "[\"1163\", \"1163\"]" ], "field5" : [ "[MinKey, MaxKey]" ], "field6" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 3, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 }, { "stage" : "IXSCAN", "nReturned" : 3, "executionTimeMillisEstimate" : 0, "works" : 10, "advanced" : 3, "needTime" : 6, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "field2" : 1, "field5" : 1, "field7" : 1, "field3" : 1, "field4" : 1 }, "indexName" : "index_2", "isMultiKey" : false, "multiKeyPaths" : { "field2" : [ ], "field5" : [ ], "field7" : [ ], "field3" : [ ], "field4" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "field2" : [ "[\"1163\", \"1163\"]" ], "field5" : [ "[MinKey, MaxKey]" ], "field7" : [ "[MinKey, MaxKey]" ], "field3" : [ "[\"G3\", \"G3\"]" ], "field4" : [ "[\"GYN\", \"GYN\"]" ] }, "keysExamined" : 10, "seeks" : 7, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } ] } }, { "stage" : "FETCH", "filter" : { "field4" : { "$eq" : "MAD" } }, "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 13, "advanced" : 5, "needTime" : 7, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 5, "alreadyHasObj" : 0, "inputStage" : { "stage" : "OR", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 13, "advanced" : 5, "needTime" : 7, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "dupsTested" : 10, "dupsDropped" : 5, "recordIdsForgotten" : 0, "inputStages" : [ { "stage" : "IXSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 5, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "field3" : 1, "field1" : 1, "field5" : 1, "field6" : 1 }, "indexName" : "index_1", "isMultiKey" : false, "multiKeyPaths" : { "field3" : [ ], "field1" : [ ], "field5" : [ ], "field6" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "field3" : [ "[\"IB\", \"IB\"]" ], "field1" : [ "[\"3402\", \"3402\"]" ], "field5" : [ "[MinKey, MaxKey]" ], "field6" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 5, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 }, { "stage" : "IXSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 7, "advanced" : 5, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "field2" : 1, "field5" : 1, "field7" : 1, "field3" : 1, "field4" : 1 }, "indexName" : "index_2", "isMultiKey" : false, "multiKeyPaths" : { "field2" : [ ], "field5" : [ ], "field7" : [ ], "field3" : [ ], "field4" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "field2" : [ "[\"3402\", \"3402\"]" ], "field5" : [ "[MinKey, MaxKey]" ], "field7" : [ "[MinKey, MaxKey]" ], "field3" : [ "[\"IB\", \"IB\"]" ], "field4" : [ "[\"MAD\", \"MAD\"]" ] }, "keysExamined" : 7, "seeks" : 2, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } ] } } ] } }, "ts" : ISODate("2021-02-22T19:16:17.280Z"), "client" : "127.0.0.1", "appName" : "MongoDB Shell", "allUsers" : [ ], "user" : "" }
When the profiler entry is too large it can trigger the "BSONObjectTooLarge" error and causing such a query fail to execute.
Error: error: { "operationTime" : Timestamp(1614018738, 1), "ok" : 0, "errmsg" : "BSONObj size: 20642198 (0x13AF996) is invalid. Size must be between 0 and 16793600(16MB) First element: stage: \"OR\"", "code" : 10334, "codeName" : "BSONObjectTooLarge", "$clusterTime" : { "clusterTime" : Timestamp(1614018738, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
- is related to
-
SERVER-14123 some operations can create BSON object larger than the 16MB limit
- Closed