Aggregation query optimization

Working with Data
1

I have a query like this:

db.spinHistory.explain("executionStats").aggregate([
  {
    $match: {
      "gameRef": "6047a10c58ed573e490b8f54"
    }
  },
  {
    $project: {
      "year": {
        "$year": [
          "$created"
        ]
      },
      "day": {
        "$dayOfYear": [
          "$created"
        ]
      },
      "bet": 1,
      "win": 1,
      "bonus": "$data.bonusWin",
      "gameRef": 1,
      "platformRef": 1,
      "currency": 1
    }
  },
  {
    $group: {
      "_id": {
        "gameRef": "$gameRef",
        "platformRef": "$platformRef",
        "currency": "$currency",
        "year": "$year",
        "day": "$day"
      },
      "bet": {
        "$sum": "$bet"
      },
      "win": {
        "$sum": "$win"
      },
      "bonus": {
        "$sum": "$bonus"
      },
      "count": {
        "$sum": 1
      }
    }
  }
])

Without any indexes the query executes in ~5s.

{
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "plannerVersion": 1,
          "namespace": "oak9e_rgs_temp.spinHistory",
          "indexFilterSet": false,
          "parsedQuery": {
            "gameRef": {
              "$eq": "6047a10c58ed573e490b8f54"
            }
          },
          "queryHash": "E16C3E41",
          "planCacheKey": "E16C3E41",
          "winningPlan": {
            "stage": "PROJECTION_DEFAULT",
            "transformBy": {
              "_id": true,
              "platformRef": true,
              "win": true,
              "gameRef": true,
              "currency": true,
              "bet": true,
              "year": {
                "$year": {
                  "date": "$created"
                }
              },
              "day": {
                "$dayOfYear": {
                  "date": "$created"
                }
              },
              "bonus": "$data.bonusWin"
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "filter": {
                "gameRef": {
                  "$eq": "6047a10c58ed573e490b8f54"
                }
              },
              "direction": "forward"
            }
          },
          "rejectedPlans": []
        },
        "executionStats": {
          "executionSuccess": true,
          "nReturned": 1145434,
          "executionTimeMillis": 5237,
          "totalKeysExamined": 0,
          "totalDocsExamined": 1145454,
          "executionStages": {
            "stage": "PROJECTION_DEFAULT",
            "nReturned": 1145434,
            "executionTimeMillisEstimate": 820,
            "works": 1145456,
            "advanced": 1145434,
            "needTime": 21,
            "needYield": 0,
            "saveState": 1330,
            "restoreState": 1330,
            "isEOF": 1,
            "transformBy": {
              "_id": true,
              "platformRef": true,
              "win": true,
              "gameRef": true,
              "currency": true,
              "bet": true,
              "year": {
                "$year": {
                  "date": "$created"
                }
              },
              "day": {
                "$dayOfYear": {
                  "date": "$created"
                }
              },
              "bonus": "$data.bonusWin"
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "filter": {
                "gameRef": {
                  "$eq": "6047a10c58ed573e490b8f54"
                }
              },
              "nReturned": 1145434,
              "executionTimeMillisEstimate": 154,
              "works": 1145456,
              "advanced": 1145434,
              "needTime": 21,
              "needYield": 0,
              "saveState": 1330,
              "restoreState": 1330,
              "isEOF": 1,
              "direction": "forward",
              "docsExamined": 1145454
            }
          }
        }
      },
      "nReturned": NumberLong(1145434),
      "executionTimeMillisEstimate": NumberLong(4764)
    },
    {
      "$group": {
        "_id": {
          "gameRef": "$gameRef",
          "platformRef": "$platformRef",
          "currency": "$currency",
          "year": "$year",
          "day": "$day"
        },
        "bet": {
          "$sum": "$bet"
        },
        "win": {
          "$sum": "$win"
        },
        "bonus": {
          "$sum": "$bonus"
        },
        "count": {
          "$sum": {
            "$const": 1
          }
        }
      },
      "nReturned": NumberLong(7),
      "executionTimeMillisEstimate": NumberLong(5231)
    }
  ],
  "serverInfo": {
    "host": "DESKTOP-V3NTFPM",
    "port": 27017,
    "version": "4.4.3",
    "gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
  },
  "ok": 1
}

If I add an index for all fields it takes even longer to execute - ~6.5s

{
  "stages": [
{
  "$cursor": {
    "queryPlanner": {
      "plannerVersion": 1,
      "namespace": "oak9e_rgs_temp.spinHistory",
      "indexFilterSet": false,
      "parsedQuery": {
        "gameRef": {
          "$eq": "6047a10c58ed573e490b8f54"
        }
      },
      "queryHash": "E16C3E41",
      "planCacheKey": "B0757629",
      "winningPlan": {
        "stage": "PROJECTION_DEFAULT",
        "transformBy": {
          "_id": true,
          "platformRef": true,
          "win": true,
          "gameRef": true,
          "currency": true,
          "bet": true,
          "year": {
            "$year": {
              "date": "$created"
            }
          },
          "day": {
            "$dayOfYear": {
              "date": "$created"
            }
          },
          "bonus": "$data.bonusWin"
        },
        "inputStage": {
          "stage": "FETCH",
          "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": {
              "gameRef": 1,
              "platformRef": 1,
              "currency": 1,
              "created": 1,
              "bet": 1,
              "win": 1,
              "data.bonusWin": 1
            },
            "indexName": "gameRef_1_platformRef_1_currency_1_created_1_bet_1_win_1_data.bonusWin_1",
            "isMultiKey": false,
            "multiKeyPaths": {
              "gameRef": [],
              "platformRef": [],
              "currency": [],
              "created": [],
              "bet": [],
              "win": [],
              "data.bonusWin": []
            },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2,
            "direction": "forward",
            "indexBounds": {
              "gameRef": [
                "[\"6047a10c58ed573e490b8f54\", \"6047a10c58ed573e490b8f54\"]"
              ],
              "platformRef": [
                "[MinKey, MaxKey]"
              ],
              "currency": [
                "[MinKey, MaxKey]"
              ],
              "created": [
                "[MinKey, MaxKey]"
              ],
              "bet": [
                "[MinKey, MaxKey]"
              ],
              "win": [
                "[MinKey, MaxKey]"
              ],
              "data.bonusWin": [
                "[MinKey, MaxKey]"
              ]
            }
          }
        }
      },
      "rejectedPlans": []
    },
    "executionStats": {
      "executionSuccess": true,
      "nReturned": 1145434,
      "executionTimeMillis": 6431,
      "totalKeysExamined": 1145434,
      "totalDocsExamined": 1145434,
      "executionStages": {
        "stage": "PROJECTION_DEFAULT",
        "nReturned": 1145434,
        "executionTimeMillisEstimate": 1298,
        "works": 1145435,
        "advanced": 1145434,
        "needTime": 0,
        "needYield": 0,
        "saveState": 1330,
        "restoreState": 1330,
        "isEOF": 1,
        "transformBy": {
          "_id": true,
          "platformRef": true,
          "win": true,
          "gameRef": true,
          "currency": true,
          "bet": true,
          "year": {
            "$year": {
              "date": "$created"
            }
          },
          "day": {
            "$dayOfYear": {
              "date": "$created"
            }
          },
          "bonus": "$data.bonusWin"
        },
        "inputStage": {
          "stage": "FETCH",
          "nReturned": 1145434,
          "executionTimeMillisEstimate": 377,
          "works": 1145435,
          "advanced": 1145434,
          "needTime": 0,
          "needYield": 0,
          "saveState": 1330,
          "restoreState": 1330,
          "isEOF": 1,
          "docsExamined": 1145434,
          "alreadyHasObj": 0,
          "inputStage": {
            "stage": "IXSCAN",
            "nReturned": 1145434,
            "executionTimeMillisEstimate": 223,
            "works": 1145435,
            "advanced": 1145434,
            "needTime": 0,
            "needYield": 0,
            "saveState": 1330,
            "restoreState": 1330,
            "isEOF": 1,
            "keyPattern": {
              "gameRef": 1,
              "platformRef": 1,
              "currency": 1,
              "created": 1,
              "bet": 1,
              "win": 1,
              "data.bonusWin": 1
            },
            "indexName": "gameRef_1_platformRef_1_currency_1_created_1_bet_1_win_1_data.bonusWin_1",
            "isMultiKey": false,
            "multiKeyPaths": {
              "gameRef": [],
              "platformRef": [],
              "currency": [],
              "created": [],
              "bet": [],
              "win": [],
              "data.bonusWin": []
            },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2,
            "direction": "forward",
            "indexBounds": {
              "gameRef": [
                "[\"6047a10c58ed573e490b8f54\", \"6047a10c58ed573e490b8f54\"]"
              ],
              "platformRef": [
                "[MinKey, MaxKey]"
              ],
              "currency": [
                "[MinKey, MaxKey]"
              ],
              "created": [
                "[MinKey, MaxKey]"
              ],
              "bet": [
                "[MinKey, MaxKey]"
              ],
              "win": [
                "[MinKey, MaxKey]"
              ],
              "data.bonusWin": [
                "[MinKey, MaxKey]"
              ]
            },
            "keysExamined": 1145434,
            "seeks": 1,
            "dupsTested": 0,
            "dupsDropped": 0
          }
        }
      }
    }
  },
  "nReturned": NumberLong(1145434),
  "executionTimeMillisEstimate": NumberLong(5950)
},
{
  "$group": {
    "_id": {
      "gameRef": "$gameRef",
      "platformRef": "$platformRef",
      "currency": "$currency",
      "year": "$year",
      "day": "$day"
    },
    "bet": {
      "$sum": "$bet"
    },
    "win": {
      "$sum": "$win"
    },
    "bonus": {
      "$sum": "$bonus"
    },
    "count": {
      "$sum": {
        "$const": 1
      }
    }
  },
  "nReturned": NumberLong(7),
  "executionTimeMillisEstimate": NumberLong(6425)
}
  ],
  "serverInfo": {
"host": "DESKTOP-V3NTFPM",
"port": 27017,
"version": "4.4.3",
"gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
  },
  "ok": 1
}

The index itself looks like this:

{
  "v": 2,
  "key": {
    "gameRef": 1,
    "platformRef": 1,
    "currency": 1,
    "created": 1,
    "bet": 1,
    "win": 1,
    "data.bonusWin": 1
  },
  "name": "gameRef_1_platformRef_1_currency_1_created_1_bet_1_win_1_data.bonusWin_1",
  "background": false
}

I can see that’s it’s adding a FETCH stage, but I am not sure why as all the fields are included in the index.
This query is being executed over 1.1M documents.

Any idea how to make this work faster and what should be the correct index?

2

Hello @Paulius_Matulionis, you can change the aggregation’s $group stage to the following and remove the $project stage. Then try the query with the index and then without the index - and tell what happened.

  {
    $group: {
      "_id": {
        "gameRef": "$gameRef",
        "platformRef": "$platformRef",
        "currency": "$currency",
        "year": { $year: "$created" },
        "day": { "$dayOfYear": "$created" }
      },
      "bet": {
        "$sum": "$bet"
      },
      "win": {
        "$sum": "$win"
      },
      "bonus": {
        "$sum": "$data.bonusWin"
      },
      "count": {
        "$sum": 1
      }
    }
  }
3

Hello @Prasad_Saya, thanks for your answer, I removed projection stage and tried explain plan with stats.
This is without index:

{
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "plannerVersion": 1,
          "namespace": "oak9e_rgs_temp.spinHistory",
          "indexFilterSet": false,
          "parsedQuery": {
            "gameRef": {
              "$eq": "6047a10c58ed573e490b8f54"
            }
          },
          "queryHash": "52F3056F",
          "planCacheKey": "52F3056F",
          "winningPlan": {
            "stage": "PROJECTION_DEFAULT",
            "transformBy": {
              "bet": 1,
              "created": 1,
              "currency": 1,
              "data.bonusWin": 1,
              "gameRef": 1,
              "platformRef": 1,
              "win": 1,
              "_id": 0
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "filter": {
                "gameRef": {
                  "$eq": "6047a10c58ed573e490b8f54"
                }
              },
              "direction": "forward"
            }
          },
          "rejectedPlans": []
        },
        "executionStats": {
          "executionSuccess": true,
          "nReturned": 1145434,
          "executionTimeMillis": 4108,
          "totalKeysExamined": 0,
          "totalDocsExamined": 1215455,
          "executionStages": {
            "stage": "PROJECTION_DEFAULT",
            "nReturned": 1145434,
            "executionTimeMillisEstimate": 13,
            "works": 1215457,
            "advanced": 1145434,
            "needTime": 70022,
            "needYield": 0,
            "saveState": 1359,
            "restoreState": 1359,
            "isEOF": 1,
            "transformBy": {
              "bet": 1,
              "created": 1,
              "currency": 1,
              "data.bonusWin": 1,
              "gameRef": 1,
              "platformRef": 1,
              "win": 1,
              "_id": 0
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "filter": {
                "gameRef": {
                  "$eq": "6047a10c58ed573e490b8f54"
                }
              },
              "nReturned": 1145434,
              "executionTimeMillisEstimate": 6,
              "works": 1215457,
              "advanced": 1145434,
              "needTime": 70022,
              "needYield": 0,
              "saveState": 1359,
              "restoreState": 1359,
              "isEOF": 1,
              "direction": "forward",
              "docsExamined": 1215455
            }
          }
        }
      },
      "nReturned": NumberLong(1145434),
      "executionTimeMillisEstimate": NumberLong(1559)
    },
    {
      "$group": {
        "_id": {
          "gameRef": "$gameRef",
          "platformRef": "$platformRef",
          "currency": "$currency",
          "year": {
            "$year": {
              "date": "$created"
            }
          },
          "day": {
            "$dayOfYear": {
              "date": "$created"
            }
          }
        },
        "bet": {
          "$sum": "$bet"
        },
        "win": {
          "$sum": "$win"
        },
        "bonus": {
          "$sum": "$data.bonusWin"
        },
        "count": {
          "$sum": {
            "$const": 1
          }
        }
      },
      "nReturned": NumberLong(7),
      "executionTimeMillisEstimate": NumberLong(4102)
    }
  ],
  "serverInfo": {
    "host": "DESKTOP-V3NTFPM",
    "port": 27017,
    "version": "4.4.3",
    "gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
  },
  "ok": 1
}

And this is with index:

{
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "plannerVersion": 1,
          "namespace": "oak9e_rgs_temp.spinHistory",
          "indexFilterSet": false,
          "parsedQuery": {
            "gameRef": {
              "$eq": "6047a10c58ed573e490b8f54"
            }
          },
          "queryHash": "52F3056F",
          "planCacheKey": "62071DAB",
          "winningPlan": {
            "stage": "PROJECTION_DEFAULT",
            "transformBy": {
              "bet": 1,
              "created": 1,
              "currency": 1,
              "data.bonusWin": 1,
              "gameRef": 1,
              "platformRef": 1,
              "win": 1,
              "_id": 0
            },
            "inputStage": {
              "stage": "IXSCAN",
              "keyPattern": {
                "gameRef": 1,
                "platformRef": 1,
                "currency": 1,
                "created": 1,
                "bet": 1,
                "win": 1,
                "data.bonusWin": 1
              },
              "indexName": "idx_all",
              "isMultiKey": false,
              "multiKeyPaths": {
                "gameRef": [],
                "platformRef": [],
                "currency": [],
                "created": [],
                "bet": [],
                "win": [],
                "data.bonusWin": []
              },
              "isUnique": false,
              "isSparse": false,
              "isPartial": false,
              "indexVersion": 2,
              "direction": "forward",
              "indexBounds": {
                "gameRef": [
                  "[\"6047a10c58ed573e490b8f54\", \"6047a10c58ed573e490b8f54\"]"
                ],
                "platformRef": [
                  "[MinKey, MaxKey]"
                ],
                "currency": [
                  "[MinKey, MaxKey]"
                ],
                "created": [
                  "[MinKey, MaxKey]"
                ],
                "bet": [
                  "[MinKey, MaxKey]"
                ],
                "win": [
                  "[MinKey, MaxKey]"
                ],
                "data.bonusWin": [
                  "[MinKey, MaxKey]"
                ]
              }
            }
          },
          "rejectedPlans": []
        },
        "executionStats": {
          "executionSuccess": true,
          "nReturned": 1145434,
          "executionTimeMillis": 6199,
          "totalKeysExamined": 1145434,
          "totalDocsExamined": 0,
          "executionStages": {
            "stage": "PROJECTION_DEFAULT",
            "nReturned": 1145434,
            "executionTimeMillisEstimate": 930,
            "works": 1145435,
            "advanced": 1145434,
            "needTime": 0,
            "needYield": 0,
            "saveState": 1399,
            "restoreState": 1399,
            "isEOF": 1,
            "transformBy": {
              "bet": 1,
              "created": 1,
              "currency": 1,
              "data.bonusWin": 1,
              "gameRef": 1,
              "platformRef": 1,
              "win": 1,
              "_id": 0
            },
            "inputStage": {
              "stage": "IXSCAN",
              "nReturned": 1145434,
              "executionTimeMillisEstimate": 188,
              "works": 1145435,
              "advanced": 1145434,
              "needTime": 0,
              "needYield": 0,
              "saveState": 1399,
              "restoreState": 1399,
              "isEOF": 1,
              "keyPattern": {
                "gameRef": 1,
                "platformRef": 1,
                "currency": 1,
                "created": 1,
                "bet": 1,
                "win": 1,
                "data.bonusWin": 1
              },
              "indexName": "idx_all",
              "isMultiKey": false,
              "multiKeyPaths": {
                "gameRef": [],
                "platformRef": [],
                "currency": [],
                "created": [],
                "bet": [],
                "win": [],
                "data.bonusWin": []
              },
              "isUnique": false,
              "isSparse": false,
              "isPartial": false,
              "indexVersion": 2,
              "direction": "forward",
              "indexBounds": {
                "gameRef": [
                  "[\"6047a10c58ed573e490b8f54\", \"6047a10c58ed573e490b8f54\"]"
                ],
                "platformRef": [
                  "[MinKey, MaxKey]"
                ],
                "currency": [
                  "[MinKey, MaxKey]"
                ],
                "created": [
                  "[MinKey, MaxKey]"
                ],
                "bet": [
                  "[MinKey, MaxKey]"
                ],
                "win": [
                  "[MinKey, MaxKey]"
                ],
                "data.bonusWin": [
                  "[MinKey, MaxKey]"
                ]
              },
              "keysExamined": 1145434,
              "seeks": 1,
              "dupsTested": 0,
              "dupsDropped": 0
            }
          }
        }
      },
      "nReturned": NumberLong(1145434),
      "executionTimeMillisEstimate": NumberLong(4988)
    },
    {
      "$group": {
        "_id": {
          "gameRef": "$gameRef",
          "platformRef": "$platformRef",
          "currency": "$currency",
          "year": {
            "$year": {
              "date": "$created"
            }
          },
          "day": {
            "$dayOfYear": {
              "date": "$created"
            }
          }
        },
        "bet": {
          "$sum": "$bet"
        },
        "win": {
          "$sum": "$win"
        },
        "bonus": {
          "$sum": "$data.bonusWin"
        },
        "count": {
          "$sum": {
            "$const": 1
          }
        }
      },
      "nReturned": NumberLong(7),
      "executionTimeMillisEstimate": NumberLong(6199)
    }
  ],
  "serverInfo": {
    "host": "DESKTOP-V3NTFPM",
    "port": 27017,
    "version": "4.4.3",
    "gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
  },
  "ok": 1

As you can see, with index, takes 2 seconds longer. Any idea?

4

Hello @Paulius_Matulionis,

In an aggregation query the $group stage doesn’t use an index to optimize this query. See Aggregation Pipeline Optimization. The compound index you had created has a index prefix of gameRef field, and so the index is applied at the initial $match stage.

5

This is about the query from the first post. The projection need to exclude the _id field. Note that in a $project stage the _id field is included, by default - and you need to exclude it explicitly. This is the reason for the FETCH stage. By excluding the _id, the query may be covered.

6

2 posts were split to a new topic: I want to find the sum of Primary Type in every Year in descending order