On a pretty large collection of 150M+ documents, I added a partial index (using the cmd line in Compass). Deployment is in a 3-member replicaset.
After quite some time, the prompt returned (ie it’s done).
Checking db.transactions.getIndexes():
{
v: 2,
key: { recognized_sender_id: 1 },
name: 'recognized_sender_id_1',
partialFilterExpression: { recognized_sender_id: [Object] }
}
This makes me believe the index is present in the collection.
However, the following query (to test if the index is used):
[
{
$match: {
recognized_sender_id: {
$exists: false
}
}
},
{
$count: "count"
}
]
Gives the following explain() result:
No index available for this query.
with raw output:
{
"explainVersion": "2",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "db.transactions",
"indexFilterSet": false,
"parsedQuery": {
"recognized_sender_id": {
"$not": { "$exists": true }
}
},
"queryHash": "2261E7A1",
"planCacheKey": "988D5B54",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"queryPlan": {
"stage": "GROUP",
"planNodeId": 2,
"inputStage": {
"stage": "COLLSCAN",
"planNodeId": 1,
"filter": {
"recognized_sender_id": {
"$not": { "$exists": true }
}
},
"direction": "forward"
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s11 env: { s4 = 1726317116455 (NOW), s3 = Timestamp(1726317115, 5) (CLUSTER_TIME), s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(America/Godthab...America/Creston) (timeZoneDB) }",
"stages": "[2] project [s11 = newObj("_id", s10, "count", s8)] n[2] project [s10 = null] n[2] group [] [s8 = sum(1)] spillSlots[s9] mergingExprs[sum(s9)] n[1] filter {!(n let [n l1.0 = s5 n ] n in exists(l1.0) n)} n[1] scan s6 s7 none none none none lowPriority [s5 = recognized_sender_id] @"5705dc95-94eb-42f8-aa84-dfd73e12a659" true false "
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 397355,
"totalKeysExamined": 0,
"totalDocsExamined": 150648462,
"executionStages": {
"stage": "project",
"planNodeId": 2,
"nReturned": 1,
"executionTimeMillisEstimate": 397284,
"opens": 1,
"closes": 1,
"saveState": 151313,
"restoreState": 151313,
"isEOF": 1,
"projections": {
"11": "newObj("_id", s10, "count", s8) "
},
"inputStage": {
"stage": "project",
"planNodeId": 2,
"nReturned": 1,
"executionTimeMillisEstimate": 397284,
"opens": 1,
"closes": 1,
"saveState": 151313,
"restoreState": 151313,
"isEOF": 1,
"projections": { "10": "null " },
"inputStage": {
"stage": "group",
"planNodeId": 2,
"nReturned": 1,
"executionTimeMillisEstimate": 397284,
"opens": 1,
"closes": 1,
"saveState": 151313,
"restoreState": 151313,
"isEOF": 1,
"groupBySlots": [],
"expressions": {
"8": "sum(1) ",
"initExprs": { "8": null }
},
"mergingExprs": {
"9": "sum(s9) "
},
"usedDisk": false,
"spills": 0,
"spilledBytes": 0,
"spilledRecords": 0,
"spilledDataStorageSize": 0,
"inputStage": {
"stage": "filter",
"planNodeId": 1,
"nReturned": 16458274,
"executionTimeMillisEstimate": 396086,
"opens": 1,
"closes": 1,
"saveState": 151313,
"restoreState": 151313,
"isEOF": 1,
"numTested": 150648462,
"filter": "!(n let [n l1.0 = s5 n ] n in exists(l1.0) n) ",
"inputStage": {
"stage": "scan",
"planNodeId": 1,
"nReturned": 150648462,
"executionTimeMillisEstimate": 390213,
"opens": 1,
"closes": 1,
"saveState": 151313,
"restoreState": 151313,
"isEOF": 1,
"numReads": 150648462,
"recordSlot": 6,
"recordIdSlot": 7,
"fields": [
"recognized_sender_id"
],
"outputSlots": [5]
}
}
}
}
},
"allPlansExecution": []
}
},
"nReturned": 1,
"executionTimeMillisEstimate": 397284
},
{
"$project": { "count": true, "_id": false },
"nReturned": 1,
"executionTimeMillisEstimate": 397284
}
],
"serverInfo": {
"host": "219a25ee14ca",
"port": 27017,
"version": "7.0.14",
"gitVersion": "ce59cfc6a3c5e5c067dca0d30697edd68d4f5188"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted"
},
"command": {
"aggregate": "transactions",
"pipeline": [
{
"$match": {
"recognized_sender_id": {
"$exists": false
}
}
},
{ "$count": "count" }
],
"cursor": {},
"maxTimeMS": 60000,
"$db": "db"
},
"ok": 1,
"$clusterTime": {
"clusterTime": {
"$timestamp": "7414477260847054896"
},
"signature": {
"hash": "EjsFtnU/ZH/DQKxb8I6ZYkdqbFU=",
"keyId": {
"low": 1,
"high": 1712009831,
"unsigned": false
}
}
},
"operationTime": {
"$timestamp": "7414477260847054896"
}
}
Pretty stumped about this? I have a few other indices on this collection that work perfectly. Any pointers?
3