Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-42160

$group stages that use a DISTINCT_SCAN do not use a SHARDING_FILTER on sharded collections

    • Query Optimization
    • ALL
    • Hide

      Assuming d.data is a sharded collection with orphan documents 

      db.data.explain().aggregate({$group: {_id: '$_id', i: {$sum: '$i'}}})
      

      The query plan returned here uses a sharding filter stage as this group cannot leverage a distinct scan 

      db.data.explain().aggregate({$group: {_id: '$_id', i: {$first: '$i'}}})
      

      The query plan returned here does not use a sharding filter stage as this group can leverage a distinct scan 

      This was determined mostly by code/query plan inspection as reproducing an error resulting from this is timing-dependent. 

      Show
      Assuming d.data is a sharded collection with orphan documents  db.data.explain().aggregate({$group: {_id: '$_id' , i: {$sum: '$i' }}}) The query plan returned here uses a sharding filter stage as this group cannot leverage a distinct scan   db.data.explain().aggregate({$group: {_id: '$_id' , i: {$first: '$i' }}}) The query plan returned here does not use a sharding filter stage as this group can leverage a distinct scan   This was determined mostly by code/query plan inspection as reproducing an error resulting from this is timing-dependent. 
    • Query 2019-07-29, Query 2019-08-12, Query 2019-08-26, Query 2019-09-09, Query 2019-09-23, Query 2019-10-07, Query 2019-10-21, Query 2019-12-30, Query 2020-03-23, Query 2020-04-06, QE 2021-10-18, QE 2021-11-01, QE 2021-11-15, QO 2022-02-21, QO 2022-03-07

      $group stages that leverage a DISTINCT_SCAN in their execution do not produce a SHARDING_FILTER stage in their query plans. This presents a problem when orphan documents persist at entry to such a stage and potentially effect the result of the $group.

      Interestingly, because $groups using a DISTINCT_SCAN need to examine only one document for each value of the group key to execute, the problem is generally hidden; such $group's naturally "deduplicate" the orphan documents by selecting only one document for each value of the group key. However, there are (at least) three cases where this still presents problems:
      1) If documents are updated before a $group using a DISTINCT_SCAN runs, and before orphan documents are otherwise purged, there is a potential for the $group to use and pass forward stale values that it takes from the orphan documents rather than the "live", updated ones.
      2) If https://jira.mongodb.org/browse/SERVER-5477 is merged, then $groups using DISTINCT_SCANS will run exclusively on the shards when the $group keys superset the shard keys. This will exacerbate this issue because it means orphans won't be deduplicated "accidentally" by the $group, as in this case the $group runs under the assumption that all documents with the same value for the group keys are on the same shard.

      3) If orphan documents persist after their "parent" documents have been deleted, the distinct scan can return stale values from these documents. 

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            george.wangensteen@mongodb.com George Wangensteen
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated: