Thanks for your reply @Prasad_Saya,
I created a compound index data.summary.elecScore_1_updated_at_1
and tried to do this simple case :
db.getCollection('test')
.explain('executionStats')
.aggregate([
{
$match: { "data.summary.elecScore": { $gte: 10 } }
},
{
$sort: { "updated_at": -1 }
},
{
allowDiskUse: true,
explain: true
}
])
and I got
{
"stages" : [
{
"$cursor" : {
"query" : {
"data.summary.elecScore" : {
"$gte" : 10
}
},
"sort" : {
"updated_at" : -1
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kazadata.test",
"indexFilterSet" : false,
"parsedQuery" : {
"data.summary.elecScore" : {
"$gte" : 10
}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"data.summary.elecScore" : {
"$gte" : 10
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"updated_at" : 1
},
"indexName" : "updated_at_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"updated_at" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"updated_at" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
}
}
}
],
"serverInfo" : {
"host" : "kdt-0-shard-00-00-xoqdb.gcp.mongodb.net",
"port" : 27017,
"version" : "4.0.18",
"gitVersion" : "6883bdfb8b8cff32176b1fd176df04da9165fd67"
},
"ok" : 1,
"operationTime" : Timestamp(1592574905, 1),
"$clusterTime" : {
"clusterTime" : Timestamp(1592574905, 1),
"signature" : {
"hash" : BinData(0,"hZpCbjUQMejIHMoEY5EddyhKrmU="),
"keyId" : NumberLong("6790057799909900289")
}
}
}
This is a sample document:
{
"_id" : ObjectId("5eeb1e90bf7d06673af6cad9"),
"plan_id" : NumberInt(11153473),
"partner_id" : NumberInt(8),
"created_at" : ISODate("2020-06-18T07:57:43.000+0000"),
"updated_at" : ISODate("2020-06-18T07:57:43.000+0000"),
"deleted_at" : null,
"data" : {
"floors" : [
{
"index" : NumberInt(0),
"customName" : null
},
{
"index" : NumberInt(1),
"customName" : "Comble"
}
],
"rooms" : [
{
"floorIndex" : NumberInt(0),
"roomIndex" : NumberInt(0),
"customName" : null,
"area" : 5.58,
"habitableArea" : 5.58,
"kind" : "terraceext",
"id" : "ac1ff9b3-6861-4e8a-907c-5017764b823c",
"surfaceType" : "outside",
"isInside" : false,
"isHabitable" : false,
"isMain" : false,
"isOutside" : true,
"isAnnex" : false
},
{
"floorIndex" : NumberInt(0),
"roomIndex" : NumberInt(1),
"customName" : null,
"area" : 5.05,
"habitableArea" : 5.05,
"kind" : "bathRoom",
"id" : "4927dd48-527c-4c9c-810b-f2eac81de59b",
"surfaceType" : "habitable",
"isInside" : true,
"isHabitable" : true,
"isMain" : false,
"isOutside" : false,
"isAnnex" : false
},
{
"floorIndex" : NumberInt(0),
"roomIndex" : NumberInt(2),
"customName" : null,
"area" : 15.91,
"habitableArea" : 15.91,
"kind" : "livingdiningRoom",
"id" : "74f7d362-d9fb-4195-aa92-1cd97331bb0c",
"surfaceType" : "habitable",
"isInside" : true,
"isHabitable" : true,
"isMain" : true,
"isOutside" : false,
"isAnnex" : false
},
{
"floorIndex" : NumberInt(0),
"roomIndex" : NumberInt(3),
"customName" : null,
"area" : 16.43,
"habitableArea" : 16.43,
"kind" : "kitchen",
"id" : "54ac840d-053c-48a2-83d0-084f440695bd",
"surfaceType" : "habitable",
"isInside" : true,
"isHabitable" : true,
"isMain" : false,
"isOutside" : false,
"isAnnex" : false
},
{
"floorIndex" : NumberInt(0),
"roomIndex" : NumberInt(4),
"customName" : null,
"area" : 28.88,
"habitableArea" : 28.88,
"kind" : "garage",
"id" : "e50cadf4-878d-425e-8bb1-6713f490c6ea",
"surfaceType" : "annex",
"isInside" : true,
"isHabitable" : false,
"isMain" : false,
"isOutside" : false,
"isAnnex" : true
},
{
"floorIndex" : NumberInt(1),
"roomIndex" : NumberInt(5),
"customName" : null,
"area" : 28.64,
"habitableArea" : 28.64,
"kind" : "bedroom",
"id" : "05cdd678-9747-4285-8b0e-be8e68b5ff93",
"surfaceType" : "habitable",
"isInside" : true,
"isHabitable" : true,
"isMain" : true,
"isOutside" : false,
"isAnnex" : false
},
{
"floorIndex" : NumberInt(1),
"roomIndex" : NumberInt(6),
"customName" : null,
"area" : 3.7,
"habitableArea" : 3.7,
"kind" : "hopper",
"id" : "78245c6e-2cba-4f80-855f-6abcd8940df2",
"surfaceType" : "building",
"isInside" : false,
"isHabitable" : false,
"isMain" : false,
"isOutside" : false,
"isAnnex" : false
}
],
"models" : [
{
"catalogId" : "model-6926",
"fromSplitGroupId" : null,
"addedAt" : ISODate("2020-06-13T14:37:24.753+0000"),
"width" : 138.5,
"height" : NumberInt(42),
"depth" : NumberInt(45),
"isDimensionEdited" : true,
"isMaterialEdited" : true,
"updatedAt" : ISODate("2020-06-18T07:17:46.693+0000"),
"updatedMaterials" : [
"material-701",
"material-painting-component-Luxens-WHITE-2"
],
"position" : [
147.24388122558594,
-577.5023803710938
],
"rotation" : {
"x" : NumberInt(0),
"y" : -0.00000000000000024492937051703357,
"z" : NumberInt(0)
},
"roomIndex" : NumberInt(2),
"roomKind" : "livingdiningRoom"
},
...
],
"materials" : [
{
"isModified" : false,
"typeOfMerge" : null,
"orig" : "catalog",
"catalogId" : "material-559",
"area" : 5.58,
"surface" : "floor",
"addedAt" : ISODate("2020-06-14T09:02:13.221+0000"),
"roomIndex" : NumberInt(0),
"serializations" : [
],
"roomKind" : "terraceext"
},
...
],
"elecs" : [
{
"products" : [
],
"quantities" : [
],
"firstRoomIndex" : NumberInt(0),
"roomIdsByFloor" : [
"ac1ff9b3-6861-4e8a-907c-5017764b823c",
"4927dd48-527c-4c9c-810b-f2eac81de59b",
"74f7d362-d9fb-4195-aa92-1cd97331bb0c",
"54ac840d-053c-48a2-83d0-084f440695bd",
"e50cadf4-878d-425e-8bb1-6713f490c6ea"
],
"roomKind" : null
},
{
"products" : [
],
"quantities" : [
],
"firstRoomIndex" : NumberInt(5),
"roomIdsByFloor" : [
"05cdd678-9747-4285-8b0e-be8e68b5ff93",
"78245c6e-2cba-4f80-855f-6abcd8940df2"
],
"roomKind" : null
}
],
"buildingBlocks" : {
"openings" : [
{
"kind" : "opening.casementwindow",
"addedAt" : ISODate("2020-06-13T14:26:08.046+0000"),
"updatedAt" : ISODate("2020-06-18T07:23:03.017+0000"),
"width" : NumberInt(80),
"height" : NumberInt(95),
"nbCasement" : NumberInt(2),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.door",
"addedAt" : ISODate("2020-06-14T08:16:45.601+0000"),
"updatedAt" : ISODate("2020-06-18T07:22:09.224+0000"),
"width" : NumberInt(90),
"height" : NumberInt(215),
"nbCasement" : NumberInt(1),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.door",
"addedAt" : ISODate("2020-06-14T11:11:26.153+0000"),
"updatedAt" : ISODate("2020-06-18T07:22:14.704+0000"),
"width" : NumberInt(90),
"height" : NumberInt(215),
"nbCasement" : NumberInt(1),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.door",
"addedAt" : ISODate("2020-06-14T13:06:50.586+0000"),
"updatedAt" : ISODate("2020-06-18T07:22:21.611+0000"),
"width" : NumberInt(90),
"height" : NumberInt(215),
"nbCasement" : NumberInt(1),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.fixedwindow",
"addedAt" : ISODate("2020-06-15T08:28:50.610+0000"),
"updatedAt" : ISODate("2020-06-18T07:47:18.607+0000"),
"width" : NumberInt(60),
"height" : NumberInt(60),
"nbCasement" : NumberInt(1),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.door",
"addedAt" : ISODate("2020-06-17T09:07:57.934+0000"),
"updatedAt" : ISODate("2020-06-17T09:55:30.324+0000"),
"width" : NumberInt(240),
"height" : NumberInt(200),
"nbCasement" : NumberInt(2),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.door",
"addedAt" : ISODate("2020-06-14T08:16:45.601+0000"),
"updatedAt" : ISODate("2020-06-18T07:21:51.487+0000"),
"width" : NumberInt(90),
"height" : NumberInt(215),
"nbCasement" : NumberInt(1),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.casementwindow",
"addedAt" : ISODate("2020-06-13T14:26:08.046+0000"),
"updatedAt" : ISODate("2020-06-18T07:23:32.771+0000"),
"width" : NumberInt(80),
"height" : NumberInt(95),
"nbCasement" : NumberInt(2),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.casementwindow",
"addedAt" : ISODate("2020-06-14T08:38:39.199+0000"),
"updatedAt" : ISODate("2020-06-18T07:48:34.485+0000"),
"width" : NumberInt(100),
"height" : NumberInt(60),
"nbCasement" : NumberInt(2),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
},
{
"kind" : "opening.casementwindow",
"addedAt" : ISODate("2020-06-14T08:38:39.199+0000"),
"updatedAt" : ISODate("2020-06-18T07:49:31.403+0000"),
"width" : NumberInt(100),
"height" : NumberInt(60),
"nbCasement" : NumberInt(2),
"hasWindow" : null,
"thirdFixed" : null,
"atelierStyle" : false
}
],
"walls" : {
"2" : 15.955894497658939,
"7" : 16.42514439746712,
"8" : 6.761795964841681,
"20" : 47.59999994721013
},
"swimmingPools" : [
],
"stairways" : [
{
"addedAt" : ISODate("2020-06-14T12:22:36.854+0000"),
"bearing" : true,
"height" : NumberInt(270),
"position" : [
282.6345520019531,
-29.525814056396484
],
"railA" : false,
"railB" : false,
"stairDepth" : NumberInt(25),
"stairOffset" : NumberInt(1),
"stairThickness" : NumberInt(3),
"stairWidth" : NumberInt(90),
"stickSpacement" : NumberInt(11),
"type" : "stairway.stairway-straight",
"updatedAt" : ISODate("2020-06-17T09:05:31.072+0000")
}
],
"roofV1" : NumberInt(0),
"roofV2" : NumberInt(8),
"elecScore" : NumberInt(3)
},
"stats" : {
"hasCatalogEvents" : true,
"hasShoppingList" : true,
"models" : [
{
"click" : NumberInt(1),
"contextBrandClick" : NumberInt(0),
"catalogBrandClick" : NumberInt(0),
"shoppingView" : NumberInt(0),
"shoppingClick" : NumberInt(0),
"shoppingPrint" : NumberInt(0),
"catalogId" : "model-6926"
},
...
],
"modelTags" : [
{
"click" : NumberInt(2),
"tagId" : "model-tags-78"
},
...a
]
},
"id" : null,
"title" : "PROJET 2502",
"summary" : {
"nbModel" : NumberInt(32),
"nbMaterial" : NumberInt(18),
"nbFloor" : NumberInt(2),
"nbRoom" : NumberInt(5),
"nbMainRoom" : NumberInt(2),
"totalArea" : 94.91,
"outsideArea" : 5.58,
"habitableArea" : 66.03,
"nbStairway" : NumberInt(1),
"nbSwimmingPool" : NumberInt(0),
"elecScore" : NumberInt(3),
"nbRoofV2" : NumberInt(8),
"nbRoofV1" : NumberInt(0),
"hasShopping" : true
},
"geolocation" : null,
"metadata" : {
"uuid" : "5b600380-203b-4c03-bd2b-1b6bb93aeb9e",
"kazaplanVersion" : "3.58.8",
"version" : "3.58.8",
"creationDate" : ISODate("2020-06-13T14:23:18.932+0000"),
"lastUpdate" : ISODate("2020-06-18T07:57:44.478+0000")
}
},
"user" : {
"user_id" : NumberInt(2860431),
"email" : "daniels.shopping@outlook.com",
"first_name" : null,
"last_name" : null,
"country" : "fr",
"created_at" : ISODate("2020-06-10T06:56:33.000+0000"),
"updated_at" : ISODate("2020-06-10T06:56:33.000+0000")
}
}
So, I tried to remove index I created previously updated_at_1
and now it works, it use compound index.
My question is: Should I force index that I want to use with hint()
in this case ?
but this aggregate above, is still, very very very slow…
Thanks !