How to enforce MongoDB to execute $group on shardPart of the execution plan?

Raghu_Yelamkonda · 2024-02-22T15:02:21.146Z

I have a sharded collection (Shard key is “key”) which contains data in the below format.
I also have a compound index defined as {key: 1, BusinessDate:-1,InsertTime:-1}

[
	{
		"key": "Key1"
		"BusinessDate": 1708387200 //(Feb 20, 2024)
		"InsertTime": 1708387200 //(Feb 20, 2024)
		"Data": "Some big bson/json data"
		"Attribute1": "value1"
		"Attribute2": "value2"
		.....
	}
	{
		"key": "Key1"
		"BusinessDate": 1708300800 //(Feb 19, 2024)
		"InsertTime": 1708300800 //(Feb 19, 2024)
		"Data": "Some big bson/json data"
		"Attribute1": "value1"
		"Attribute2": "value2"
		.....
	}
	....
	.....
	
	{
		"key": "Key2"
		"BusinessDate": 1708387200 //(Feb 20, 2024)
		"InsertTime": 1708387200 //(Feb 20, 2024)
		"Data": "Some big bson/json data"
		"Attribute1": "value1"
		"Attribute2": "value2"
		.....
	}
	{
		"key": "Key2"
		"BusinessDate": 1708300800 //(Feb 19, 2024)
		"InsertTime": 1708300800 //(Feb 19, 2024)
		"Data": "Some big bson/json data"
		"Attribute1": "value1"
		"Attribute2": "value2"
		.....
	}
	....
	....
]

I am trying to find the latest record for each key with few other constraints (like businessDate <= 1708300800 and businessDate > 1705622400 …etc)

I am trying with below aggregation pipeline

[
  {
    '$match': {
      'businessDate': {
        '$gt': 1705622400, 
        '$lte': 1708300800
      }
      'InsertTime': {
        '$gt': 1705622400, 
        '$lte': 1708300800
      }
    }
  }, {
    '$sort': {
      'key': 1, 
      'businessDate': -1, 
      'InsertTime': -1
    }
  }, {
    '$group': {
      '_id': {
        'key': '$key'
      }, 
      'doc': {
        '$first': '$$ROOT'
      }
    }
  }, {
    '$replaceRoot': {
      'newRoot': '$doc'
    }
  }
]

The query plan which I got from mongo is splitting the execution into shard part and merge part. I was expecting $group step will get executed in shardPart, but it is going in merge part. Which eventually move all the data to single shard which is ineficiant. Since my shardding is based on “key”, functionally it should return correct results even if the group runs on each shard. Is there any way I can achieve the $group to execute in shardPart (so that my query execution runs parallelly for $group stage)

Below is the snippet of query execution plan

  "mergeType": "anyShard",
  "splitPipeline": {
    "shardsPart": [
      {
        "$match": {
          "$and": [
            {
              "asOf": {
                "$lte": 9223372036854775807
              }
            },
            {
              "asOf": {
                "$gte": -9223372036854775808
              }
            },
            {
              "asAt": {
                "$gte": -9223372036854775808
              }
            },
            {
              "asAt": {
                "$lte": 9223372036854775807
              }
            }
          ]
        }
      },
      {
        "$sort": {
          "sortKey": {
            "key": 1,
            "asOf": -1,
            "asAt": -1
          }
        }
      }
    ],
    "mergerPart": [
      {
        "$group": {
          "_id": "$key",
          "doc": {
            "$first": "$$ROOT"
          }
        }
      },
      {
        "$replaceRoot": {
          "newRoot": "$doc"
        }
      },
      {
        "$sort": {
          "sortKey": {
            "key": 1,
            "asOf": -1,
            "asAt": -1
          }
        }
      }
    ]
  }

Luca_Nardelli2 · 2025-02-04T14:05:42.533Z

Upvoting this, we’re running into the same issue

Raghu_Yelamkonda · 2025-02-09T09:56:25.464Z

[
  {
    $match: {
      key: /EUR_ESTR/,
      asOf: {
        $gte: 1128176289000,
        $lte: 1928376289000,
      },
      asAt: {
        $gte: 1128176289000,
        $lte: 1928376289000,
      },
    },
  },
  {
    $sort: {
      key: 1,
      asOf: -1,
      asAt: -1,
    },
  },
  {
    $group: {
      _id: {
        key: "$key",
      },
      doc: {
        $first: "$$ROOT",
      },
    },
  },
  {
    $replaceRoot: {
      newRoot: "$doc",
    },
  },
  {
    $sort: {
      key: 1,
    },
  },
]