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
-