Thanks for your quick response !! it was really helpful.
regarding what you said about
the new index {“container”:1,“path”:1,"_id":1,“size”:1}
Today without the new index the query execution time for a large collection is 6 sec.
this is the queryPlanner:
/* 1 */
{
“serverInfo” : {
“host” : “TLVR-00018-D”,
“port” : 27017,
“version” : “3.6.18”,
“gitVersion” : “2005f25eed7ed88fa698d9b800fe536bb0410ba4”
},
“stages” : [
{
“$cursor” : {
“query” : {
“container” : “bbbf087e-6d2d-4941-b812-f035407becbe:storage”,
“path” : {
“$ne” : “.”
}
},
“fields” : {
“size” : 1,
“_id” : 0
},
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “storagedb.files_v2”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“container” : {
“$eq” : “bbbf087e-6d2d-4941-b812-f035407becbe:storage”
}
},
{
“$nor” : [
{
“path” : {
“$eq” : “.”
}
}
]
}
]
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“container” : 1.0,
“path” : 1.0,
“_id” : 1.0
},
“indexName” : “container_1_path_1__id_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“container” : ,
“path” : ,
“_id” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“container” : [
“[“bbbf087e-6d2d-4941-b812-f035407becbe:storage”, “bbbf087e-6d2d-4941-b812-f035407becbe:storage”]”
],
“path” : [
“[MinKey, “.”)”,
“(”.", MaxKey]"
],
“_id” : [
“[MinKey, MaxKey]”
]
}
}
},
“rejectedPlans” : [
{
“stage” : “FETCH”,
“filter” : {
“$nor” : [
{
“path” : {
“$eq” : “.”
}
}
]
},
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“container” : 1.0,
“_id” : 1.0
},
“indexName” : “container_1__id_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“container” : ,
“_id” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“container” : [
“[“bbbf087e-6d2d-4941-b812-f035407becbe:storage”, “bbbf087e-6d2d-4941-b812-f035407becbe:storage”]”
],
“_id” : [
“[MinKey, MaxKey]”
]
}
}
},
{
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“container” : 1,
“path” : 1,
“fileNameLower” : 1
},
“indexName” : “container_1_path_1_fileNameLower_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“container” : ,
“path” : ,
“fileNameLower” :
},
“isUnique” : true,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“container” : [
“[“bbbf087e-6d2d-4941-b812-f035407becbe:storage”, “bbbf087e-6d2d-4941-b812-f035407becbe:storage”]”
],
“path” : [
“[MinKey, “.”)”,
“(”.", MaxKey]"
],
“fileNameLower” : [
“[MinKey, MaxKey]”
]
}
}
}
]
}
}
},
{
“$group” : {
“_id” : {
“$const” : “”
},
“sum” : {
“$sum” : “$size”
},
“count” : {
“$sum” : {
“$const” : 1.0
}
}
}
}
],
“ok” : 1.0
}
How ever with the new index the execution time takes 2 sec and this is the queryPlanner
/* 1 */
{
“serverInfo” : {
“host” : “TLVR-00018-D”,
“port” : 27017,
“version” : “3.6.18”,
“gitVersion” : “2005f25eed7ed88fa698d9b800fe536bb0410ba4”
},
“stages” : [
{
“$cursor” : {
“query” : {
“container” : “bbbf087e-6d2d-4941-b812-f035407becbe:storage”,
“path” : {
“$ne” : “.”
}
},
“fields” : {
“size” : 1,
“_id” : 0
},
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “storagedb.files_v2”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“container” : {
“$eq” : “bbbf087e-6d2d-4941-b812-f035407becbe:storage”
}
},
{
“$nor” : [
{
“path” : {
“$eq” : “.”
}
}
]
}
]
},
“winningPlan” : {
“stage” : “PROJECTION”,
“transformBy” : {
“size” : 1,
“_id” : 0
},
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“container” : 1.0,
“path” : 1.0,
“_id” : 1.0,
“size” : 1.0
},
“indexName” : “container_1_path_1__id_1_size_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“container” : ,
“path” : ,
“_id” : ,
“size” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“container” : [
“[“bbbf087e-6d2d-4941-b812-f035407becbe:storage”, “bbbf087e-6d2d-4941-b812-f035407becbe:storage”]”
],
“path” : [
“[MinKey, “.”)”,
“(”.", MaxKey]"
],
“_id” : [
“[MinKey, MaxKey]”
],
“size” : [
“[MinKey, MaxKey]”
]
}
}
},
“rejectedPlans” :
}
}
},
{
“$group” : {
“_id” : {
“$const” : “”
},
“sum” : {
“$sum” : “$size”
},
“count” : {
“$sum” : {
“$const” : 1.0
}
}
}
}
],
“ok” : 1.0
}
Are you sure he doesn’t use the “_id” field inside the index because from run time preceptive its looks like it using the index? I’m using Mongo 3.6