[SERVER-57988] Unfortunate index selection leads to too many index/document scans Created: 22/Jun/21  Updated: 07/Jul/22  Resolved: 07/Jul/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.4.2
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Daniel Hegener Assignee: Jacob Evans
Resolution: Duplicate Votes: 0
Labels: bonsai, indexing, perfomance, query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File executionStats.json    
Issue Links:
Duplicate
duplicates SERVER-14423 Plans which fetch different numbers o... Closed
is duplicated by SERVER-57989 Unfortunate index selection leads to ... Closed
Related
Operating System: ALL
Sprint: QO 2021-09-20, QO 2021-10-04, QO 2021-10-18
Participants:
Case:

 Description   

We have a query:

 

{
   "$match":{
      "$and":[
         {
            "$or":[
               {
                  "RecordId":{
                     "$ne":"Some_RecordId_To_Remove"
                  }
               },
               {
                  "DataSource":{
                     "$ne":"Some_DataSource_To_Remove"
                  }
               }
            ]
         },
         {
            "$or":[
               {
                  "DataSource":{
                     "$not":"/.*Base_DataSource.*/i"
                  }
               },
               {
                  "RecordId":"Some_RecordId_To_Add",
                  "DataSource":"Some_DataSource_To_Add"
               }
            ]
         },
         {
            "IsLatestRevision":true
         },
         {
            "IsDeleted":false
         },
         {
            "DeliveryId":"60d05da8db9d210104f31a6b"
         }
      ]
   }
}

 

Let's ignore the mind-boggling logic on the top part of the filter - the important thing to note is that there is a restriction on the "DeliveryId" field. Why is that of interest?

We run this query against a collection that contains ~29m documents which are semantically split into individual "deliveries" using this "DeliveryId" field. We're talking roughly 20 deliveries or so, each of which consists of either ~6m documents or a substantially smaller number of documents, e.g. 150k.

There is a bunch of indexes on the collection, too, one of which looks like

{ *DeliveryId: 1*, RecordId: 1, DataSource: 1, Revision: 1 }

and, as per my understanding, should be helpful to avoid full scans of all documents whenever a query filters by "DeliveryId".

What we are seeing in the logs sometimes is the below, however:

 

[...]
originatingCommand: { aggregate: "CollXyz", pipeline: [ { <THE ABOVE $MATCH STAGE> }, { $project: { _id: 1 } } ], allowDiskUse: true, cursor: {}, $db: "DbXyz", lsid: { id: UUID("631bb48c-991e-41ad-8092-8564b72d694e") } } planSummary: IXSCAN { DeliveryId: 1, RecordId: 1, DataSource: 1, Revision: 1 }, IXSCAN { RecordId: 1, DataSource: 1 } cursorid:4730283530601619740 keysExamined:28986788 docsExamined:28849489 cursorExhausted:1 numYields:241934 nreturned:137197 reslen:4004933 locks:{ ReplicationStateTransition: { acquireCount: { w: 241942 } }, Global: { acquireCount: { r: 241942 } }, Database: { acquireCount: { r: 241942 } }, Collection: { acquireCount: { r: 241941 } }, Mutex: { acquireCount: { r: 8 } } } storage:{ data: { bytesRead: 211830295502, timeReadingMicros: 802571385 } } protocol:op_msg 1467513ms

 

So, every now and again, MongoDB appears to choose to use two indexes:

  • { *DeliveryId: 1*, _RecordId: 1, DataSource: 1_, Revision: 1 }
  • { _RecordId: 1, DataSource: 1_ }

The things that I would claim are dubious here are

  • that we end up with keysExamined:28986788 and docsExamined:28849489 (so the whole collection gets read!!!)
  • but only nreturned:137197 which is basically just the effect of the "DeliveryId" filter part.

I also wonder why the second index gets considered in the first place as the

{ _RecordId: 1, DataSource: 1_ }

combination is already contained in the first index...

Anyway, it would appear that once MongoDB decides to use a different index that does not contain the most important field, we end up somehow scanning the whole collection...?

 

Some additional business background:

It's probably worth noting that the filter on the top

 

            "$or":[
               {
                  "RecordId":{
                     "$ne":"Some_RecordId_To_Remove"
                  }
               },
               {
                  "DataSource":{
                     "$ne":"Some_DataSource_To_Remove"
                  }
               }
            ]

 

is a filter of very limited use as it won't remove any documents from the result set at all in a lot of cases. The above query gets generated based on user input and unless the user specifies some meaningful values for either "RecordId" or "DataSource", it will match on any document in the entire collection. The intention here is to allow a user to remove individual documents from the result based on their "RecordId" and/or "DataSource".

Also, the next part

 

            "$or":[
               {
                  "DataSource":{
                     "$not":"/.*Base_DataSource.*/i"
                  }
               },
               {
                  "RecordId":"Some_RecordId_To_Add",
                  "DataSource":"Some_DataSource_To_Add"
               }
            ]

 

is an unusual filter as its purpose is to allow for a user to specify some values for "RecordId" and/or "DataSource" to add some records to a base set ("DataSource":{ "$not":"/.Base_DataSource./i" }) that shall remain included.

 



 Comments   
Comment by Jacob Evans [ 08/Oct/21 ]

We prepare plans for indexes even when another index is over a superset of fields. As long as the query contains a field reference which matches the first key in the index, the index will be considered. Choosing the index that "matches better" is something that is tracked in SERVER-21697.

When comparing the two plans, we observe the same behavior here we've seen in SERVER-14423. In fact it's similar to the situation tracked by SERVER-46904:  during the optimizer trial period we count the same number of work cycles for both plans. The individual accesses of the index, despite being slower due to more keys examined in the inferior plan's case are treated as identical by the scoring algorithm. The inefficiency of the overall plan is not observed during the trial phase

Further to the known issues above that are being tracked, there is an alternate planning algorithm which may help mitigate the problem described here. Using it simply requires the query to be reformatted from a contained $or to a rooted $or shape. An example of how to do so can be found in the "WORKAROUNDS" section in the description in SERVER-13732.

Comment by Daniel Hegener [ 05/Jul/21 ]

Here's some sample code that - for me - results in the problematic index selection getting considered but ultimately not chosen.

 

db.getCollection("c").insertOne({a:1, b:1, c:1})
db.getCollection("c").insertOne({a:2, b:2, c:2})
db.getCollection("c").insertOne({a:3, b:3, c:3})
db.getCollection("c").createIndex({a:1, b:1, c:1})
db.getCollection("c").createIndex({b:1, c:1})
db.getCollection("c").explain().find({a:1, $or:[{b:{$ne:1}},{c:{$ne:1}}]})

 

When I do

db.getCollection("c").explain().find({a:1, $or:[{b:{$ne:1}},{c:{$ne:1}}]})

I get this output:

> db.getCollection("c").explain().find({a:1, $or:[{b:{$ne:1}},{c:{$ne:1}}]})
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.c",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "$or" : [
                                                {
                                                        "b" : {
                                                                "$not" : {
                                                                        "$eq" : 1
                                                                }
                                                        }
                                                },
                                                {
                                                        "c" : {
                                                                "$not" : {
                                                                        "$eq" : 1
                                                                }
                                                        }
                                                }
                                        ]
                                },
                                {
                                        "a" : {
                                                "$eq" : 1
                                        }
                                }
                        ]
                },
                "queryHash" : "CE7054C2",
                "planCacheKey" : "22E15AED",
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$or" : [
                                        {
                                                "b" : {
                                                        "$not" : {
                                                                "$eq" : 1
                                                        }
                                                }
                                        },
                                        {
                                                "c" : {
                                                        "$not" : {
                                                                "$eq" : 1
                                                        }
                                                }
                                        }
                                ]
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "a" : 1,
                                        "b" : 1,
                                        "c" : 1
                                },
                                "indexName" : "a_1_b_1_c_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "a" : [ ],
                                        "b" : [ ],
                                        "c" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "a" : [
                                                "[1.0, 1.0]"
                                        ],
                                        "b" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "c" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "a" : {
                                                "$eq" : 1
                                        }
                                },
                                "inputStage" : {
                                        "stage" : "OR",
                                        "inputStages" : [
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "a" : 1,
                                                                "b" : 1,
                                                                "c" : 1
                                                        },
                                                        "indexName" : "a_1_b_1_c_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "a" : [ ],
                                                                "b" : [ ],
                                                                "c" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "a" : [
                                                                        "[1.0, 1.0]"
                                                                ],
                                                                "b" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "c" : [
                                                                        "[MinKey, 1.0)",
                                                                        "(1.0, MaxKey]"
                                                                ]
                                                        }
                                                },
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "b" : 1,
                                                                "c" : 1
                                                        },
                                                        "indexName" : "b_1_c_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "b" : [ ],
                                                                "c" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "b" : [
                                                                        "[MinKey, 1.0)",
                                                                        "(1.0, MaxKey]"
                                                                ],
                                                                "c" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                }
                                        ]
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "inputStage" : {
                                        "stage" : "OR",
                                        "inputStages" : [
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "a" : 1,
                                                                "b" : 1,
                                                                "c" : 1
                                                        },
                                                        "indexName" : "a_1_b_1_c_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "a" : [ ],
                                                                "b" : [ ],
                                                                "c" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "a" : [
                                                                        "[1.0, 1.0]"
                                                                ],
                                                                "b" : [
                                                                        "[MinKey, 1.0)",
                                                                        "(1.0, MaxKey]"
                                                                ],
                                                                "c" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                },
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "a" : 1,
                                                                "b" : 1,
                                                                "c" : 1
                                                        },
                                                        "indexName" : "a_1_b_1_c_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "a" : [ ],
                                                                "b" : [ ],
                                                                "c" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "a" : [
                                                                        "[1.0, 1.0]"
                                                                ],
                                                                "b" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "c" : [
                                                                        "[MinKey, 1.0)",
                                                                        "(1.0, MaxKey]"
                                                                ]
                                                        }
                                                }
                                        ]
                                }
                        }
                ]
        },
        "serverInfo" : {
                "host" : "EMEAZRHCARTSQL",
                "port" : 27017,
                "version" : "4.4.6",
                "gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
        },
        "ok" : 1
}
>

The first rejected plan is the one that shouldn't be in the list in the first place, I suppose. Obviously the sample data is small so that might have an effect, too. But you could play with the code at least...

Comment by Daniel Hegener [ 05/Jul/21 ]

There you go. I excluded a big nested field but it's not related to indexes so it should be irrelevant:

{
   "_id":"ObjectId(""60d3a73831467b129ce05b9e"")",
   "DeliveryId":"60d3a73731467b129ce05b94",
   "ReferenceDate":"ISODate(""2019-01-09T00:00:00Z"")",
   "FeedName":"Host Feed",
   "RecordId":"0",
   "DataSource":"0",
   "IsDeleted":false,
   "Description":"Anonymized",
   "IsLatestRevision":true,
   "PluginId":2,
   "CreatedBy":"someone",
   "Status":0,
   "Revision":1,
   "CreatedAt":"ISODate(""2021-06-23T21:48:24.105Z"")",
   "Aggregate":0,
   "ContentTypeId":"70768fae-a931-4699-91d5-b7c170e298f0"
}

Comment by Edwin Zhou [ 01/Jul/21 ]

Hi daniel.hegener@gmx.net,

Thanks for the clarification and the follow up. Are you also able to provide an example document in your collection? This will help us to reproduce the issue and further investigate the behavior.

Best,
Edwin

Comment by Daniel Hegener [ 01/Jul/21 ]

Thanks for that. Kindly note:

1. The problem really is not just the redundant index key scans themselves. It's really that with this problematic index selection, all documents in the entire collection get scanned instead of only the ones that remain in scope after the evaluation of the most restrictive filter (the one on DeliveryId). We see absolutely horrible performance degradations due to that: 25 minutes instead of seconds at the client, 40+ minutes instead of seconds on my larger test environment...

2. I made a little mistake in my previous comments by picking some query for the .explain(true) output that I had been experimenting with in a failed attempt to outsmart the index selection engine (split $match stages, reduced number of conditions, changed order) instead of the one I described in the initial text. Also, the issue text talks about 29m positions and delivery sizes of 6m documents but I was using a different database here so numbers in the execution stats look different. So this might cause confusion but it all still describes our problem correctly...

3. I have experimented a bit more and the shortest query that I could come up with that highlights the problem is this:

 

[{$match: {"DeliveryId":"60d3a73731467b129ce05b94", "$or":[{"RecordId":{"$ne":"xyz"}},{"DataSource":{"$ne":"xyz"}}]}}]

 

 

Comment by Edwin Zhou [ 30/Jun/21 ]

Hi daniel.hegener@gmx.net,

Thanks for providing the explain(true) output of your aggregation pipeline. I agree that it's unfortunate that we're seeing what appears to be redundant index key scans. I'm passing this along to the appropriate team for further investigation.

Best,
Edwin

Comment by Daniel Hegener [ 29/Jun/21 ]

                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "$or" : [
                                                                        {
                                                                                "DataSource" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte DataSource eintragen"
                                                                                        }
                                                                                }
                                                                        },
                                                                        {
                                                                                "RecordId" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte Position IDeintragen"
                                                                                        }
                                                                                }
                                                                        }
                                                                ]
                                                        },
                                                        {
                                                                "IsLatestRevision" : {
                                                                        "$eq" : true
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 157518,
                                        "restoreState" : 157518,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 0,
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "IsDeleted" : 1,
                                                        "CreatedAt" : 1,
                                                        "NextCreatedAt" : 1
                                                },
                                                "indexName" : "DI_ID_CA_NCA",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "IsDeleted" : [ ],
                                                        "CreatedAt" : [ ],
                                                        "NextCreatedAt" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "IsDeleted" : [
                                                                "[false, false]"
                                                        ],
                                                        "CreatedAt" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "NextCreatedAt" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 101,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        }
                                }
                        },
                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$or" : [
                                                        {
                                                                "DataSource" : {
                                                                        "$not" : {
                                                                                "$eq" : "Hier bitte DataSource eintragen"
                                                                        }
                                                                }
                                                        },
                                                        {
                                                                "RecordId" : {
                                                                        "$not" : {
                                                                                "$eq" : "Hier bitte Position IDeintragen"
                                                                        }
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 157518,
                                        "restoreState" : 157518,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 0,
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "IsLatestRevision" : 1,
                                                        "IsDeleted" : 1,
                                                        "Status" : 1
                                                },
                                                "indexName" : "DI_ILR_ID_S",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "IsLatestRevision" : [ ],
                                                        "IsDeleted" : [ ],
                                                        "Status" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "IsLatestRevision" : [
                                                                "[true, true]"
                                                        ],
                                                        "IsDeleted" : [
                                                                "[false, false]"
                                                        ],
                                                        "Status" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 101,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        }
                                }
                        },
                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "$or" : [
                                                                        {
                                                                                "DataSource" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte DataSource eintragen"
                                                                                        }
                                                                                }
                                                                        },
                                                                        {
                                                                                "RecordId" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte Position IDeintragen"
                                                                                        }
                                                                                }
                                                                        }
                                                                ]
                                                        },
                                                        {
                                                                "IsDeleted" : {
                                                                        "$eq" : false
                                                                }
                                                        },
                                                        {
                                                                "IsLatestRevision" : {
                                                                        "$eq" : true
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 157518,
                                        "restoreState" : 157518,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 0,
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "RecordId" : 1,
                                                        "DataSource" : 1,
                                                        "Revision" : 1
                                                },
                                                "indexName" : "DI_RI_DS_R",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "RecordId" : [ ],
                                                        "DataSource" : [ ],
                                                        "Revision" : [ ]
                                                },
                                                "isUnique" : true,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "RecordId" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "DataSource" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "Revision" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 101,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        }
                                }
                        },
                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "$or" : [
                                                                        {
                                                                                "DataSource" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte DataSource eintragen"
                                                                                        }
                                                                                }
                                                                        },
                                                                        {
                                                                                "RecordId" : {
                                                                                        "$not" : {
                                                                                                "$eq" : "Hier bitte Position IDeintragen"
                                                                                        }
                                                                                }
                                                                        }
                                                                ]
                                                        },
                                                        {
                                                                "IsDeleted" : {
                                                                        "$eq" : false
                                                                }
                                                        },
                                                        {
                                                                "IsLatestRevision" : {
                                                                        "$eq" : true
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 157518,
                                        "restoreState" : 157518,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 0,
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "CreatedAt" : -1
                                                },
                                                "indexName" : "DI_CA",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "CreatedAt" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "CreatedAt" : [
                                                                "[MaxKey, MinKey]"
                                                        ]
                                                },
                                                "keysExamined" : 101,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        }
                                }
                        }
                ]
        },
        "serverInfo" : {
                "host" : "xxx",
                "port" : 27017,
                "version" : "4.4.6",
                "gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
        },
        "ok" : 1
}

Comment by Daniel Hegener [ 29/Jun/21 ]

 

        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 9582643,
                "executionTimeMillis" : 2539267,
                "totalKeysExamined" : 114991716,
                "totalDocsExamined" : 105409073,
                "executionStages" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "DeliveryId" : {
                                                        "$eq" : "60d3a73731467b129ce05b94"
                                                }
                                        },
                                        {
                                                "IsDeleted" : {
                                                        "$eq" : false
                                                }
                                        },
                                        {
                                                "IsLatestRevision" : {
                                                        "$eq" : true
                                                }
                                        }
                                ]
                        },
                        "nReturned" : 9582643,
                        "executionTimeMillisEstimate" : 2185971,
                        "works" : 114991718,
                        "advanced" : 9582643,
                        "needTime" : 105409074,
                        "needYield" : 0,
                        "saveState" : 157518,
                        "restoreState" : 157518,
                        "isEOF" : 1,
                        "docsExamined" : 105409073,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "OR",
                                "nReturned" : 105409073,
                                "executionTimeMillisEstimate" : 213769,
                                "works" : 114991718,
                                "advanced" : 105409073,
                                "needTime" : 9582644,
                                "needYield" : 0,
                                "saveState" : 157518,
                                "restoreState" : 157518,
                                "isEOF" : 1,
                                "dupsTested" : 114991716,
                                "dupsDropped" : 9582643,
                                "inputStages" : [
                                        {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 9582643,
                                                "executionTimeMillisEstimate" : 16597,
                                                "works" : 9582644,
                                                "advanced" : 9582643,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 1,
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "RecordId" : 1,
                                                        "DataSource" : 1,
                                                        "Revision" : 1
                                                },
                                                "indexName" : "DI_RI_DS_R",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "RecordId" : [ ],
                                                        "DataSource" : [ ],
                                                        "Revision" : [ ]
                                                },
                                                "isUnique" : true,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "RecordId" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "DataSource" : [
                                                                "[MinKey, \"Hier bitte DataSource eintragen\")",
                                                                "(\"Hier bitte DataSource eintragen\", MaxKey]"
                                                        ],
                                                        "Revision" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 9582643,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        },
                                        {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 105409073,
                                                "executionTimeMillisEstimate" : 135834,
                                                "works" : 105409074,
                                                "advanced" : 105409073,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 1,
                                                "keyPattern" : {
                                                        "RecordId" : 1,
                                                        "DataSource" : 1
                                                },
                                                "indexName" : "RI_DS",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "RecordId" : [ ],
                                                        "DataSource" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "RecordId" : [
                                                                "[MinKey, \"Hier bitte Position IDeintragen\")",
                                                                "(\"Hier bitte Position IDeintragen\", MaxKey]"
                                                        ],
                                                        "DataSource" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 105409073,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0
                                        }
                                ]
                        }
                },
                "allPlansExecution" : [
                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "DeliveryId" : {
                                                                        "$eq" : "60d3a73731467b129ce05b94"
                                                                }
                                                        },
                                                        {
                                                                "IsDeleted" : {
                                                                        "$eq" : false
                                                                }
                                                        },
                                                        {
                                                                "IsLatestRevision" : {
                                                                        "$eq" : true
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 0,
                                        "restoreState" : 0,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "OR",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 0,
                                                "restoreState" : 0,
                                                "isEOF" : 0,
                                                "dupsTested" : 101,
                                                "dupsDropped" : 0,
                                                "inputStages" : [
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 101,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 101,
                                                                "advanced" : 101,
                                                                "needTime" : 0,
                                                                "needYield" : 0,
                                                                "saveState" : 0,
                                                                "restoreState" : 0,
                                                                "isEOF" : 0,
                                                                "keyPattern" : {
                                                                        "DeliveryId" : 1,
                                                                        "RecordId" : 1,
                                                                        "DataSource" : 1,
                                                                        "Revision" : 1
                                                                },
                                                                "indexName" : "DI_RI_DS_R",
                                                                "isMultiKey" : false,
                                                                "multiKeyPaths" : {
                                                                        "DeliveryId" : [ ],
                                                                        "RecordId" : [ ],
                                                                        "DataSource" : [ ],
                                                                        "Revision" : [ ]
                                                                },
                                                                "isUnique" : true,
                                                                "isSparse" : false,
                                                                "isPartial" : false,
                                                                "indexVersion" : 2,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "DeliveryId" : [
                                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                                        ],
                                                                        "RecordId" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ],
                                                                        "DataSource" : [
                                                                                "[MinKey, \"Hier bitte DataSource eintragen\")",
                                                                                "(\"Hier bitte DataSource eintragen\", MaxKey]"
                                                                        ],
                                                                        "Revision" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 101,
                                                                "seeks" : 1,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0
                                                        },
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 0,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 0,
                                                                "advanced" : 0,
                                                                "needTime" : 0,
                                                                "needYield" : 0,
                                                                "saveState" : 0,
                                                                "restoreState" : 0,
                                                                "isEOF" : 0,
                                                                "keyPattern" : {
                                                                        "RecordId" : 1,
                                                                        "DataSource" : 1
                                                                },
                                                                "indexName" : "RI_DS",
                                                                "isMultiKey" : false,
                                                                "multiKeyPaths" : {
                                                                        "RecordId" : [ ],
                                                                        "DataSource" : [ ]
                                                                },
                                                                "isUnique" : false,
                                                                "isSparse" : false,
                                                                "isPartial" : false,
                                                                "indexVersion" : 2,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "RecordId" : [
                                                                                "[MinKey, \"Hier bitte Position IDeintragen\")",
                                                                                "(\"Hier bitte Position IDeintragen\", MaxKey]"
                                                                        ],
                                                                        "DataSource" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 0,
                                                                "seeks" : 0,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0
                                                        }
                                                ]
                                        }
                                }
                        },
                        {
                                "nReturned" : 101,
                                "executionTimeMillisEstimate" : 0,
                                "totalKeysExamined" : 101,
                                "totalDocsExamined" : 101,
                                "executionStages" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "IsDeleted" : {
                                                                        "$eq" : false
                                                                }
                                                        },
                                                        {
                                                                "IsLatestRevision" : {
                                                                        "$eq" : true
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 101,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 101,
                                        "advanced" : 101,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 157518,
                                        "restoreState" : 157518,
                                        "isEOF" : 0,
                                        "docsExamined" : 101,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "OR",
                                                "nReturned" : 101,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 101,
                                                "advanced" : 101,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 157518,
                                                "restoreState" : 157518,
                                                "isEOF" : 0,
                                                "dupsTested" : 101,
                                                "dupsDropped" : 0,
                                                "inputStages" : [
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 101,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 101,
                                                                "advanced" : 101,
                                                                "needTime" : 0,
                                                                "needYield" : 0,
                                                                "saveState" : 157518,
                                                                "restoreState" : 157518,
                                                                "isEOF" : 0,
                                                                "keyPattern" : {
                                                                        "DeliveryId" : 1,
                                                                        "RecordId" : 1,
                                                                        "DataSource" : 1,
                                                                        "Revision" : 1
                                                                },
                                                                "indexName" : "DI_RI_DS_R",
                                                                "isMultiKey" : false,
                                                                "multiKeyPaths" : {
                                                                        "DeliveryId" : [ ],
                                                                        "RecordId" : [ ],
                                                                        "DataSource" : [ ],
                                                                        "Revision" : [ ]
                                                                },
                                                                "isUnique" : true,
                                                                "isSparse" : false,
                                                                "isPartial" : false,
                                                                "indexVersion" : 2,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "DeliveryId" : [
                                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                                        ],
                                                                        "RecordId" : [
                                                                                "[MinKey, \"Hier bitte Position IDeintragen\")",
                                                                                "(\"Hier bitte Position IDeintragen\", MaxKey]"
                                                                        ],
                                                                        "DataSource" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ],
                                                                        "Revision" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 101,
                                                                "seeks" : 1,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0
                                                        },
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 0,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 0,
                                                                "advanced" : 0,
                                                                "needTime" : 0,
                                                                "needYield" : 0,
                                                                "saveState" : 157518,
                                                                "restoreState" : 157518,
                                                                "isEOF" : 0,
                                                                "keyPattern" : {
                                                                        "DeliveryId" : 1,
                                                                        "RecordId" : 1,
                                                                        "DataSource" : 1,
                                                                        "Revision" : 1
                                                                },
                                                                "indexName" : "DI_RI_DS_R",
                                                                "isMultiKey" : false,
                                                                "multiKeyPaths" : {
                                                                        "DeliveryId" : [ ],
                                                                        "RecordId" : [ ],
                                                                        "DataSource" : [ ],
                                                                        "Revision" : [ ]
                                                                },
                                                                "isUnique" : true,
                                                                "isSparse" : false,
                                                                "isPartial" : false,
                                                                "indexVersion" : 2,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "DeliveryId" : [
                                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                                        ],
                                                                        "RecordId" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ],
                                                                        "DataSource" : [
                                                                                "[MinKey, \"Hier bitte DataSource eintragen\")",
                                                                                "(\"Hier bitte DataSource eintragen\", MaxKey]"
                                                                        ],
                                                                        "Revision" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 0,
                                                                "seeks" : 0,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0
                                                        }
                                                ]
                                        }
                                }
                        },

 

Comment by Daniel Hegener [ 29/Jun/21 ]

Thanks for looking into this. Here's the full story - enjoy...! Kindly note that the query looks marginally different because I simplified it in the ticket description. Also, the server version is 4.4.6 but our client is using 4.4.2 but seeing the same behavior. I will have to split the output into multiple comments as it's too long.

> db.getCollection("60d3225c31467b129cb15786.Records").explain(true).aggregate([{$match: {"DeliveryId":"60d3a73731467b129ce05b94"}}, {$match:{"$or":[{"RecordId":{"$ne":"Hier bitte Position IDeintragen"}},{"DataSource":{"$ne":"Hier bitte DataSource eintragen"}}],"IsLatestRevision":true,"IsDeleted":false}}])
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "DataHubDB_2021_2.60d3225c31467b129cb15786.Records",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "$or" : [
                                                {
                                                        "DataSource" : {
                                                                "$not" : {
                                                                        "$eq" : "Hier bitte DataSource eintragen"
                                                                }
                                                        }
                                                },
                                                {
                                                        "RecordId" : {
                                                                "$not" : {
                                                                        "$eq" : "Hier bitte Position IDeintragen"
                                                                }
                                                        }
                                                }
                                        ]
                                },
                                {
                                        "DeliveryId" : {
                                                "$eq" : "60d3a73731467b129ce05b94"
                                        }
                                },
                                {
                                        "IsDeleted" : {
                                                "$eq" : false
                                        }
                                },
                                {
                                        "IsLatestRevision" : {
                                                "$eq" : true
                                        }
                                }
                        ]
                },
                "optimizedPipeline" : true,
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "DeliveryId" : {
                                                        "$eq" : "60d3a73731467b129ce05b94"
                                                }
                                        },
                                        {
                                                "IsDeleted" : {
                                                        "$eq" : false
                                                }
                                        },
                                        {
                                                "IsLatestRevision" : {
                                                        "$eq" : true
                                                }
                                        }
                                ]
                        },
                        "inputStage" : {
                                "stage" : "OR",
                                "inputStages" : [
                                        {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "DeliveryId" : 1,
                                                        "RecordId" : 1,
                                                        "DataSource" : 1,
                                                        "Revision" : 1
                                                },
                                                "indexName" : "DI_RI_DS_R",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "DeliveryId" : [ ],
                                                        "RecordId" : [ ],
                                                        "DataSource" : [ ],
                                                        "Revision" : [ ]
                                                },
                                                "isUnique" : true,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "DeliveryId" : [
                                                                "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                        ],
                                                        "RecordId" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "DataSource" : [
                                                                "[MinKey, \"Hier bitte DataSource eintragen\")",
                                                                "(\"Hier bitte DataSource eintragen\", MaxKey]"
                                                        ],
                                                        "Revision" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                }
                                        },
                                        {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "RecordId" : 1,
                                                        "DataSource" : 1
                                                },
                                                "indexName" : "RI_DS",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "RecordId" : [ ],
                                                        "DataSource" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "RecordId" : [
                                                                "[MinKey, \"Hier bitte Position IDeintragen\")",
                                                                "(\"Hier bitte Position IDeintragen\", MaxKey]"
                                                        ],
                                                        "DataSource" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                }
                                        }
                                ]
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "IsDeleted" : {
                                                                "$eq" : false
                                                        }
                                                },
                                                {
                                                        "IsLatestRevision" : {
                                                                "$eq" : true
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "OR",
                                        "inputStages" : [
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "DeliveryId" : 1,
                                                                "RecordId" : 1,
                                                                "DataSource" : 1,
                                                                "Revision" : 1
                                                        },
                                                        "indexName" : "DI_RI_DS_R",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "DeliveryId" : [ ],
                                                                "RecordId" : [ ],
                                                                "DataSource" : [ ],
                                                                "Revision" : [ ]
                                                        },
                                                        "isUnique" : true,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "DeliveryId" : [
                                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                                ],
                                                                "RecordId" : [
                                                                        "[MinKey, \"Hier bitte Position IDeintragen\")",
                                                                        "(\"Hier bitte Position IDeintragen\", MaxKey]"
                                                                ],
                                                                "DataSource" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "Revision" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                },
                                                {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "DeliveryId" : 1,
                                                                "RecordId" : 1,
                                                                "DataSource" : 1,
                                                                "Revision" : 1
                                                        },
                                                        "indexName" : "DI_RI_DS_R",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "DeliveryId" : [ ],
                                                                "RecordId" : [ ],
                                                                "DataSource" : [ ],
                                                                "Revision" : [ ]
                                                        },
                                                        "isUnique" : true,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "DeliveryId" : [
                                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                                ],
                                                                "RecordId" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "DataSource" : [
                                                                        "[MinKey, \"Hier bitte DataSource eintragen\")",
                                                                        "(\"Hier bitte DataSource eintragen\", MaxKey]"
                                                                ],
                                                                "Revision" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                }
                                        ]
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "$or" : [
                                                                {
                                                                        "DataSource" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte DataSource eintragen"
                                                                                }
                                                                        }
                                                                },
                                                                {
                                                                        "RecordId" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte Position IDeintragen"
                                                                                }
                                                                        }
                                                                }
                                                        ]
                                                },
                                                {
                                                        "IsLatestRevision" : {
                                                                "$eq" : true
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "DeliveryId" : 1,
                                                "IsDeleted" : 1,
                                                "CreatedAt" : 1,
                                                "NextCreatedAt" : 1
                                        },
                                        "indexName" : "DI_ID_CA_NCA",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "DeliveryId" : [ ],
                                                "IsDeleted" : [ ],
                                                "CreatedAt" : [ ],
                                                "NextCreatedAt" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "DeliveryId" : [
                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                ],
                                                "IsDeleted" : [
                                                        "[false, false]"
                                                ],
                                                "CreatedAt" : [
                                                        "[MinKey, MaxKey]"
                                                ],
                                                "NextCreatedAt" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$or" : [
                                                {
                                                        "DataSource" : {
                                                                "$not" : {
                                                                        "$eq" : "Hier bitte DataSource eintragen"
                                                                }
                                                        }
                                                },
                                                {
                                                        "RecordId" : {
                                                                "$not" : {
                                                                        "$eq" : "Hier bitte Position IDeintragen"
                                                                }
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "DeliveryId" : 1,
                                                "IsLatestRevision" : 1,
                                                "IsDeleted" : 1,
                                                "Status" : 1
                                        },
                                        "indexName" : "DI_ILR_ID_S",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "DeliveryId" : [ ],
                                                "IsLatestRevision" : [ ],
                                                "IsDeleted" : [ ],
                                                "Status" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "DeliveryId" : [
                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                ],
                                                "IsLatestRevision" : [
                                                        "[true, true]"
                                                ],
                                                "IsDeleted" : [
                                                        "[false, false]"
                                                ],
                                                "Status" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "$or" : [
                                                                {
                                                                        "DataSource" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte DataSource eintragen"
                                                                                }
                                                                        }
                                                                },
                                                                {
                                                                        "RecordId" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte Position IDeintragen"
                                                                                }
                                                                        }
                                                                }
                                                        ]
                                                },
                                                {
                                                        "IsDeleted" : {
                                                                "$eq" : false
                                                        }
                                                },
                                                {
                                                        "IsLatestRevision" : {
                                                                "$eq" : true
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "DeliveryId" : 1,
                                                "RecordId" : 1,
                                                "DataSource" : 1,
                                                "Revision" : 1
                                        },
                                        "indexName" : "DI_RI_DS_R",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "DeliveryId" : [ ],
                                                "RecordId" : [ ],
                                                "DataSource" : [ ],
                                                "Revision" : [ ]
                                        },
                                        "isUnique" : true,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "DeliveryId" : [
                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                ],
                                                "RecordId" : [
                                                        "[MinKey, MaxKey]"
                                                ],
                                                "DataSource" : [
                                                        "[MinKey, MaxKey]"
                                                ],
                                                "Revision" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "$or" : [
                                                                {
                                                                        "DataSource" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte DataSource eintragen"
                                                                                }
                                                                        }
                                                                },
                                                                {
                                                                        "RecordId" : {
                                                                                "$not" : {
                                                                                        "$eq" : "Hier bitte Position IDeintragen"
                                                                                }
                                                                        }
                                                                }
                                                        ]
                                                },
                                                {
                                                        "IsDeleted" : {
                                                                "$eq" : false
                                                        }
                                                },
                                                {
                                                        "IsLatestRevision" : {
                                                                "$eq" : true
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "DeliveryId" : 1,
                                                "CreatedAt" : -1
                                        },
                                        "indexName" : "DI_CA",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "DeliveryId" : [ ],
                                                "CreatedAt" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "DeliveryId" : [
                                                        "[\"60d3a73731467b129ce05b94\", \"60d3a73731467b129ce05b94\"]"
                                                ],
                                                "CreatedAt" : [
                                                        "[MaxKey, MinKey]"
                                                ]
                                        }
                                }
                        }
                ]
        },

Comment by Edwin Zhou [ 28/Jun/21 ]

Hi daniel.hegener@gmx.net,

Thanks for your report. In order to further investigate why a specific index is being chosen from your pipeline, can you please provide the .explain(true) output for your aggregation?

db.orders.explain(true).aggregate(pipeline)

You can read more about the explain output in our documentation

Best,
Edwin

Comment by Daniel Hegener [ 25/Jun/21 ]

FWIW, this ticket seems somehow related: https://jira.mongodb.org/browse/SERVER-14519

Generated at Thu Feb 08 05:43:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.