-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
ALL
$group used on partial _id field does not use index.
I have a collection with an compound index field. For example like this one :
db.test_group_index.insert([
{
_id: {
attr1: "value1",
attr2: "value2",
attr3: "value3.1",
},
attr4: "value4.1"
},
{
_id: {
attr1: "value1",
attr2: "value2",
attr3: "value3.2",
},
attr4: "value4.2"
},
{
_id: {
attr1: "value1",
attr2: "value2",
attr3: "value3.3",
},
attr4: "value4.3"
}
]);
I want to group over two _id field to work only on distinct values for keys attr1 & attr2.
I do this :
db.test_group_index.explain().aggregate([
{
$group: {
_id: {
attr1: "$_id.attr1",
attr2: "$_id.attr2"
},
attr4: {
$first: "$attr4"
}
}
}
])
The result of this explain query shows that a COLLSCAN is used.
{
"stages" : [
{
"$cursor" : {
"query" : {
},
"fields" : {
"attr4" : 1,
"_id" : 1
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "ref.test_group_index",
"indexFilterSet" : false,
"parsedQuery" : {
},
"queryHash" : "8B3D4AB8",
"planCacheKey" : "8B3D4AB8",
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : {
"attr1" : "$_id.attr1",
"attr2" : "$_id.attr2"
},
"attr4" : {
"$first" : "$attr4"
}
}
}
],
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1590759417, 4),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : 0
}
},
"operationTime" : Timestamp(1590759414, 1)
}