|
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.
|
|
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...
|
|
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"
|
}
|
|
|
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
|
|
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"}}]}}]
|
|
|
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
|
{
|
"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
|
}
|
|
|
"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
|
}
|
]
|
}
|
}
|
},
|
|
|
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]"
|
]
|
}
|
}
|
}
|
]
|
},
|
|
|
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
|
|
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.