I have already seen that before. Therefore the “LGTM”.
But thing is, I can’t reproduce this behaviour in 5.0.8. So maybe it’s the version diff, or maybe it’s something else…
db.coll.drop()
db.coll.createIndex({n:1})
db.coll.insertMany([{n:1},{n:2},{n:3},{n:4},{n:5},{n:6},{n:7},{n:8},{n:9},{n:10}])
db.coll.find({n:{$gt: 4, $lte: 8}}).explain(true)
Output:
{
explainVersion: '1',
queryPlanner: {
namespace: 'test.coll',
indexFilterSet: false,
parsedQuery: {
'$and': [ { n: { '$lte': 8 } }, { n: { '$gt': 4 } } ]
},
maxIndexedOrSolutionsReached: false,
maxIndexedAndSolutionsReached: false,
maxScansToExplodeReached: false,
winningPlan: {
stage: 'FETCH',
inputStage: {
stage: 'IXSCAN',
keyPattern: { n: 1 },
indexName: 'n_1',
isMultiKey: false,
multiKeyPaths: { n: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { n: [ '(4, 8]' ] }
}
},
rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 4,
executionTimeMillis: 0,
totalKeysExamined: 4,
totalDocsExamined: 4,
executionStages: {
stage: 'FETCH',
nReturned: 4,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 4,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 4,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 4,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 4,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { n: 1 },
indexName: 'n_1',
isMultiKey: false,
multiKeyPaths: { n: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { n: [ '(4, 8]' ] },
keysExamined: 4,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
},
allPlansExecution: []
},
command: {
find: 'coll',
filter: { n: { '$gt': 4, '$lte': 8 } },
'$db': 'test'
},
serverInfo: {
host: 'hafx',
port: 27017,
version: '5.0.8',
gitVersion: 'c87e1c23421bf79614baf500fda6622bd90f674e'
},
serverParameters: {
internalQueryFacetBufferSizeBytes: 104857600,
internalQueryFacetMaxOutputDocSizeBytes: 104857600,
internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
internalDocumentSourceGroupMaxMemoryBytes: 104857600,
internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
internalQueryProhibitBlockingMergeOnMongoS: 0,
internalQueryMaxAddToSetBytes: 104857600,
internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
},
ok: 1,
'$clusterTime': {
clusterTime: Timestamp({ t: 1653497779, i: 1 }),
signature: {
hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
keyId: Long("0")
}
},
operationTime: Timestamp({ t: 1653497779, i: 1 })
}
Can you test the same query in 5.0.8 in the same conditions (more or less) and can you reproduce this behaviour?
Note: the fact that this is a multikey index and I’m not using one might be part of the problem here.
Cheers,
Maxime.