After migrate to version 7.0.2 using Atlas we are facing slow queries with regex

Cleiton_dos_Santos_Garcia · October 25, 2023, 11:30am

Hello everyone,

We have recently migrate to version 7.0.2, as result we observe a terible performance in queries that we have in the same way in our application for more than 3 years.
We upgrade the atlas cluser from a M20 to a M40, but it continues with a lower response time than before.

Follows a simple query that is with this terrible issue for our users. find: Object
OrgId: AAAAAAAAA
PId: BBBBBB
AttributeId: _CASO
Value: Object
$regex: .JOAO DA .
$options: i
sort: Object
Value: 1

Any are facing the same issue?

I could migrate to atlas search, but it is a legacy system version. We will not invest to do that, it is waiting for planing the migration.

Christopher_Harris · October 25, 2023, 3:34pm

Hi @Cleiton_dos_Santos_Garcia!

My name is Chris and I’m presently a Senior Product Manager here at MongoDB after spending a number of years in our Technical Services organization. We are sorry to hear that you’re encountering issues since the upgrade and would definitely like to help figure out what is going on here.

Unfortunately there is not enough information at this point for us to draw any conclusions about what might be happening or to make any recommendations. In terms of next steps, it would be very helpful if you could:

Confirm what version you upgraded from and when that happened.
Quantify the performance change. How fast was the operation before the upgrade and how long does it take now?
Share the .explain("allPlansExecution") output for the query.

Alternative methods for continuing the discussion and getting further help can be found in Request Support.

Thanks again for letting us know about your experience and for the proactive steps that you have taken to attempt to mitigate the issue thus far.

Best,
Chris

Cleiton_dos_Santos_Garcia · October 25, 2023, 4:40pm

I was in version 6.x probably the last as it is managed by Atlas, now it is 7.x
According our users it was 10x faster.
Unfortunatly, I do not have any environment at version 6 anymore.

Follows the explain
{
“explainVersion”: “2”,
“queryPlanner”: {
“namespace”: “DBX.AttributeValues”,
“indexFilterSet”: false,
“parsedQuery”: {
“$and”: [
{
“OId”: {
“$eq”: “AAAAAAA”
}
},
{
“AttributeId”: {
“$eq”: “_CASO”
}
},
{
“PId”: {
“$eq”: “BBBBBBBB”
}
},
{
“Value”: {
“$regex”: “JOAO ALBERTO ROBERTO”,
“$options”: “i”
}
}
]
},
“queryHash”: “DC93163D”,
“planCacheKey”: “C05B5A91”,
“maxIndexedOrSolutionsReached”: false,
“maxIndexedAndSolutionsReached”: false,
“maxScansToExplodeReached”: false,
“winningPlan”: {
“queryPlan”: {
“stage”: “FETCH”,
“planNodeId”: 2,
“inputStage”: {
“stage”: “IXSCAN”,
“planNodeId”: 1,
“filter”: {
“Value”: {
“$regex”: “JOAO ALBERTO ROBERTO”,
“$options”: “i”
}
},
“keyPattern”: {
“AccessGroupId”: 1,
“ProcessId”: 1,
“AttributeId”: 1,
“Value”: 1
},
“indexName”: “AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”,
“isMultiKey”: false,
“multiKeyPaths”: {
“AccessGroupId”: ,
“ProcessId”: ,
“AttributeId”: ,
“Value”:
},
“isUnique”: false,
“isSparse”: false,
“isPartial”: true,
“indexVersion”: 2,
“direction”: “forward”,
“indexBounds”: {
“AccessGroupId”: [
“["AAAAAAA", "AAAAAAA"]”
],
“ProcessId”: [
“["BBBBBBBB", "BBBBBBBB"]”
],
“AttributeId”: [
“["_CASO", "_CASO"]”
],
“Value”: [
“["", {})”,
“[/JOAO ALBERTO ROBERTO/i, /JOAO ALBERTO ROBERTO/i]”
]
}
}
},
“slotBasedPlan”: {
“slots”: “$$RESULT=s31 env: { s2 = Nothing (SEARCH_META), s30 = PcreRegex(/JOAO ALBERTO ROBERTO/i), s9 = IndexBounds("field #0[‘AccessGroupId’]: [CollationKey(0x63613632633736652d386131312d346239632d616538612d303765363438653637343639), CollationKey(0x63613632633736652d386131312"…), s3 = Timestamp(1698251553, 80) (CLUSTER_TIME), s28 = true, s13 = {"AccessGroupId" : 1, "ProcessId" : 1, "AttributeId" : 1, "Value" : 1}, s4 = 1698251555018 (NOW), s1 = TimeZoneDatabase(Pacific/Pohnpei…Africa/Mbabane) (timeZoneDB), s29 = /JOAO ALBERTO ROBERTO/i, s17 = Nothing }”,
“stages”: “[2] nlj inner [s23, s24, s25, s26, s27] \n left \n [1] filter {(((s5 == s29) ?: false) || (regexMatch(s30, s5) ?: false))} \n [1] branch {s28} [s5, s23, s24, s25, s26, s27] \n [s6, s8, s10, s11, s12, s13] [1] ixscan_generic s9 s12 s8 s10 s11 lowPriority [s6 = 3] @"0726b45e-2098-44ce-b055-3972a03c5877" @"AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1" true \n [s7, s14, s20, s21, s22, s13] [1] nlj inner [s15, s16] \n left \n [1] project [s15 = getField(s18, \“l\”), s16 = getField(s18, \“h\”)] \n [1] unwind s18 s19 s17 false \n [1] limit 1 \n [1] coscan \n right \n [1] ixseek s15 s16 s22 s14 s20 s21 [s7 = 3] @"0726b45e-2098-44ce-b055-3972a03c5877" @"AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1" true \n right \n [2] limit 1 \n [2] seek s23 s31 s32 s24 s25 s26 s27 @"0726b45e-2098-44ce-b055-3972a03c5877" true false \n”
}
},
“rejectedPlans”:
},
“executionStats”: {
“executionSuccess”: true,
“nReturned”: 0,
“executionTimeMillis”: 34551,
“totalKeysExamined”: 3313762,
“totalDocsExamined”: 0,
“executionStages”: {
“stage”: “nlj”,
“planNodeId”: 2,
“nReturned”: 0,
“executionTimeMillisEstimate”: 34550,
“opens”: 1,
“closes”: 1,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 1,
“totalDocsExamined”: 0,
“totalKeysExamined”: 3313762,
“collectionScans”: 0,
“collectionSeeks”: 0,
“indexScans”: 0,
“indexSeeks”: 1,
“indexesUsed”: [
“AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”,
“AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”
],
“innerOpens”: 0,
“innerCloses”: 0,
“outerProjects”: ,
“outerCorrelated”: [
{
“low”: 23,
“high”: 0,
“unsigned”: false
},
{
“low”: 24,
“high”: 0,
“unsigned”: false
},
{
“low”: 25,
“high”: 0,
“unsigned”: false
},
{
“low”: 26,
“high”: 0,
“unsigned”: false
},
{
“low”: 27,
“high”: 0,
“unsigned”: false
}
],
“outerStage”: {
“stage”: “filter”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 34550,
“opens”: 1,
“closes”: 1,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 1,
“numTested”: 3313761,
“filter”: "(((s5 == s29) ?: false) || (regexMatch(s30, s5) ?: false)) ",
“inputStage”: {
“stage”: “branch”,
“planNodeId”: 1,
“nReturned”: 3313761,
“executionTimeMillisEstimate”: 28242,
“opens”: 1,
“closes”: 1,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 1,
“numTested”: 1,
“thenBranchOpens”: 1,
“thenBranchCloses”: 1,
“elseBranchOpens”: 0,
“elseBranchCloses”: 0,
“filter”: "s28 ",
“thenSlots”: [
{
“low”: 6,
“high”: 0,
“unsigned”: false
},
{
“low”: 8,
“high”: 0,
“unsigned”: false
},
{
“low”: 10,
“high”: 0,
“unsigned”: false
},
{
“low”: 11,
“high”: 0,
“unsigned”: false
},
{
“low”: 12,
“high”: 0,
“unsigned”: false
},
{
“low”: 13,
“high”: 0,
“unsigned”: false
}
],
“elseSlots”: [
{
“low”: 7,
“high”: 0,
“unsigned”: false
},
{
“low”: 14,
“high”: 0,
“unsigned”: false
},
{
“low”: 20,
“high”: 0,
“unsigned”: false
},
{
“low”: 21,
“high”: 0,
“unsigned”: false
},
{
“low”: 22,
“high”: 0,
“unsigned”: false
},
{
“low”: 13,
“high”: 0,
“unsigned”: false
}
],
“outputSlots”: [
{
“low”: 5,
“high”: 0,
“unsigned”: false
},
{
“low”: 23,
“high”: 0,
“unsigned”: false
},
{
“low”: 24,
“high”: 0,
“unsigned”: false
},
{
“low”: 25,
“high”: 0,
“unsigned”: false
},
{
“low”: 26,
“high”: 0,
“unsigned”: false
},
{
“low”: 27,
“high”: 0,
“unsigned”: false
}
],
“thenStage”: {
“stage”: “ixscan_generic”,
“planNodeId”: 1,
“nReturned”: 3313761,
“executionTimeMillisEstimate”: 27994,
“opens”: 1,
“closes”: 1,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 1,
“indexName”: “AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”,
“keysExamined”: 3313762,
“seeks”: 1,
“numReads”: 3313762,
“indexKeySlot”: 12,
“recordIdSlot”: 8,
“snapshotIdSlot”: 10,
“indexIdentSlot”: 11,
“outputSlots”: [
{
“low”: 6,
“high”: 0,
“unsigned”: false
}
],
“indexKeysToInclude”: “00000000000000000000000000001000”
},
“elseStage”: {
“stage”: “nlj”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“totalDocsExamined”: 0,
“totalKeysExamined”: 0,
“collectionScans”: 0,
“collectionSeeks”: 0,
“indexScans”: 0,
“indexSeeks”: 0,
“indexesUsed”: [
“AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”
],
“innerOpens”: 0,
“innerCloses”: 0,
“outerProjects”: ,
“outerCorrelated”: [
{
“low”: 15,
“high”: 0,
“unsigned”: false
},
{
“low”: 16,
“high”: 0,
“unsigned”: false
}
],
“outerStage”: {
“stage”: “project”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“projections”: {
“15”: "getField(s18, "l") ",
“16”: "getField(s18, "h") "
},
“inputStage”: {
“stage”: “unwind”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“inputSlot”: 17,
“outSlot”: 18,
“outIndexSlot”: 19,
“preserveNullAndEmptyArrays”: 0,
“inputStage”: {
“stage”: “limit”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“limit”: 1,
“inputStage”: {
“stage”: “coscan”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0
}
}
}
},
“innerStage”: {
“stage”: “ixseek”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“indexName”: “AccessGroupId_1_ProcessId_1_AttributeId_1_Value_1”,
“keysExamined”: 0,
“seeks”: 0,
“numReads”: 0,
“indexKeySlot”: 22,
“recordIdSlot”: 14,
“snapshotIdSlot”: 20,
“indexIdentSlot”: 21,
“outputSlots”: [
{
“low”: 7,
“high”: 0,
“unsigned”: false
}
],
“indexKeysToInclude”: “00000000000000000000000000001000”,
“seekKeyLow”: "s15 ",
“seekKeyHigh”: "s16 "
}
}
}
},
“innerStage”: {
“stage”: “limit”,
“planNodeId”: 2,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“limit”: 1,
“inputStage”: {
“stage”: “seek”,
“planNodeId”: 2,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 3728,
“restoreState”: 3728,
“isEOF”: 0,
“numReads”: 0,
“recordSlot”: 31,
“recordIdSlot”: 32,
“seekKeySlot”: 23,
“snapshotIdSlot”: 24,
“indexIdentSlot”: 25,
“indexKeySlot”: 26,
“indexKeyPatternSlot”: 27,
“fields”: ,
“outputSlots”:
}
}
},
“allPlansExecution”:
},
“command”: {
“find”: “AttributeValues”,
“filter”: {
“AccessGroupId”: “AAAAAAA”,
“ProcessId”: “BBBBBBBB”,
“AttributeId”: “_CASO”,
“Value”: {
“$regex”: {},
“$options”: “i”
}
},
“$db”: “”
},
“serverInfo”: {
“host”: “”,
“port”: ,
“version”: “7.0.2”,
“gitVersion”: “02b3c655e1302209ef046da6ba3ef6749dd0b62a”
},
“serverParameters”: {
“internalQueryFacetBufferSizeBytes”: 104857600,
“internalQueryFacetMaxOutputDocSizeBytes”: 104857600,
“internalLookupStageIntermediateDocumentMaxSizeBytes”: 104857600,
“internalDocumentSourceGroupMaxMemoryBytes”: 104857600,
“internalQueryMaxBlockingSortMemoryUsageBytes”: 104857600,
“internalQueryProhibitBlockingMergeOnMongoS”: 0,
“internalQueryMaxAddToSetBytes”: 104857600,
“internalDocumentSourceSetWindowFieldsMaxMemoryBytes”: 104857600,
“internalQueryFrameworkControl”: “trySbeEngine”
},
“ok”: 1,
“$clusterTime”: {
“clusterTime”: {
“$timestamp”: “7293935030840066050”
},
“signature”: {
“hash”: “fmLmFFOv5b16TyWEmH7QdMqgU8M=”,
“keyId”: {
“low”: 553,
“high”: 1686852637,
“unsigned”: false
}
}
},
“operationTime”: {
“$timestamp”: “7293935030840066050”
}
}