PartialFilterExpression Index for $exists query

35 views
Skip to first unread message

pnair

unread,
Aug 9, 2019, 10:13:57 PM8/9/19
to mongodb-user
hi -

I am trying to use partial filter expression to create an index to check if there are more than 30 entries in an array in a collection.

I) My Index looks like this

mongos> db.item.getIndexes()

[


{

"v" : 2,

"key" : {

"colors" : 1

},

"name" : "colorsidx",

"ns" : "test.item",

"background" : true,

"partialFilterExpression" : {

"colors.30" : {

"$exists" : 1

}

}

}

]

mongos> 



II)  and my query is this.

db.item.find({"colors.30": {$exists:1}}).hint({"colors":1}).explain(true)

mongos> db.item.find(colorsidx{"colors.30": {$exists:1}}).hint({"colors":1}).explain(true)
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "....",
"connectionString" : "....",
"serverInfo" : {
"host" : "....",
"port" : 27018,
"version" : "4.0.9",
"gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
},
"plannerVersion" : 1,
"namespace" : ".....",
"indexFilterSet" : false,
"parsedQuery" : {
"colors.30" : {
"$exists" : true
}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"colors.30" : {
"$exists" : true
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"colors" : 1
},
"indexName" : "colorsidx",
"isMultiKey" : true,
"multiKeyPaths" : {
"colors" : [
"colors"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"colors" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
}
]
}
},
"executionStats" : {
"nReturned" : 2,
"executionTimeMillis" : 110,
"totalKeysExamined" : 103308,
"totalDocsExamined" : 2,
"executionStages" : {
"stage" : "SINGLE_SHARD",
"nReturned" : 2,
"executionTimeMillis" : 110,
"totalKeysExamined" : 103308,
"totalDocsExamined" : 2,
"totalChildMillis" : NumberLong(109),
"shards" : [
{
"shardName" : "......",
"executionSuccess" : true,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"colors.30" : {
"$exists" : true
}
},
"nReturned" : 2,
"executionTimeMillisEstimate" : 40,
"works" : 103309,
"advanced" : 2,
"needTime" : 103306,
"needYield" : 0,
"saveState" : 807,
"restoreState" : 807,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 30,
"works" : 103309,
"advanced" : 2,
"needTime" : 103306,
"needYield" : 0,
"saveState" : 807,
"restoreState" : 807,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"colors" : 1
},
"indexName" : "colorsidx",
"isMultiKey" : true,
"multiKeyPaths" : {
"colors" : [
"colors"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"colors" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 103308,
"seeks" : 1,
"dupsTested" : 103308,
"dupsDropped" : 103306,
"seenInvalidated" : 0
}
}
}
]
},
"allPlansExecution" : [
{
"shardName" : "....",
"allPlans" : [ ]
}
]
},
"ok" : 1,
"operationTime" : Timestamp(1565402332, 11),
"$clusterTime" : {
"clusterTime" : Timestamp(1565402333, 1),
"signature" : {
"hash" : BinData(0,"acmozUKxEwwMk1X3huQUJWq3vgw="),
"keyId" : NumberLong("6684052015565242425")
}
}
}
mongos>

III)  I am using 4.0.9 mongodb version.

IV) The total number of documents in the collection item is only 7840

mongos> db.item.count()

7840

mongos> 


V) 
Question1) Without giving a hint, the above query is doing COLLSCAN and not INDEX SCAN. Why is not using the partial filter expression query I created?
Question 2) Even when I am using a hint , I am seeing this 
"keysExamined" : 103308,
"seeks" : 1,
"dupsTested" : 103308,
"dupsDropped" : 103306,
"seenInvalidated" : 0

What does this mean to have such large number of keysExamined, dupsTested, and dupsDropped? How can make this query better?


Thanks,
Parvathi.



Reply all
Reply to author
Forward
0 new messages