DEV Community

Franck Pachot
Franck Pachot

Posted on

DocumentDB 0.116: $group distinct scan

#documentdb #opensource #mongodb #postgres

I previously covered MongoDB’s DISTINCT_SCAN for first/last-per-group queries. DocumentDB 0.116 (August 20, 2026) extends the same loose-index-scan principle to $group queries that return one row per distinct grouping key, combining it with the index-only access path described in the previous post of this series.

DocumentDB implements the MongoDB API as a fully open source PostgreSQL extension. This makes it possible to run MongoDB applications on the most popular open source relational database without reducing the MongoDB language to simple document filtering. In my opinion, DocumentDB is the only MongoDB emulation that translates MongoDB operators into native SQL access paths. Microsoft is the main contributor, improving the extension from en enterprise customer feedback on Azure DocumentDB.

To demonstrate this optimization, I create 50,000 documents with only 100 distinct values in a, and an ordered a_1 index. The aggregation groups by a without an accumulator:

[
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
]
Enter fullscreen mode Exit fullscreen mode

The result contains 100 groups. A normal index scan reads all 50,000 index entries and lets the aggregate remove duplicates. A distinct scan can jump from one value to the next and read only 100 index entries.

I use MongoDB 8.0.28 as the reference, DocumentDB 0.114-0 as the previous version, and DocumentDB 0.116-0 with enableGroupByDistinctScan explicitly enabled. VACUUM (ANALYZE) is run on both DocumentDB versions so the visibility state is identical.

MongoDB 8.0 reference

This script creates the collection, builds the index, returns the number of groups, and runs the complete explain("executionStats"):

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

const batch = [];
for (let i = 0; i < 50000; i++) {
  batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
}
db.distinct_group.insertMany(batch);
db.distinct_group.createIndex({a: 1}, {name: "a_1"});

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));
Enter fullscreen mode Exit fullscreen mode

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": "1",
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "namespace": "distinct116.distinct_group",
            "parsedQuery": {},
            "indexFilterSet": false,
            "queryHash": "C46B0559",
            "planCacheShapeHash": "C46B0559",
            "planCacheKey": "C66CA7DD",
            "optimizationTimeMillis": 0,
            "maxIndexedOrSolutionsReached": false,
            "maxIndexedAndSolutionsReached": false,
            "maxScansToExplodeReached": false,
            "prunedSimilarIndexes": false,
            "winningPlan": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "transformBy": {
                "a": 1,
                "_id": 0
              },
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                }
              }
            },
            "rejectedPlans": []
          },
          "executionStats": {
            "executionSuccess": true,
            "nReturned": 100,
            "executionTimeMillis": 2,
            "totalKeysExamined": 100,
            "totalDocsExamined": 0,
            "executionStages": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "nReturned": 100,
              "executionTimeMillisEstimate": 0,
              "works": 101,
              "advanced": 100,
              "needTime": 0,
              "needYield": 0,
              "saveState": 3,
              "restoreState": 3,
              "isEOF": 1,
              "transformBy": {
                "a": 1,
                "_id": 0
              },
              "inputStage": {
                "stage": "DISTINCT_SCAN",
                "nReturned": 100,
                "executionTimeMillisEstimate": 0,
                "works": 101,
                "advanced": 100,
                "needTime": 0,
                "needYield": 0,
                "saveState": 3,
                "restoreState": 3,
                "isEOF": 1,
                "keyPattern": {
                  "a": 1
                },
                "indexName": "a_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "a": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "a": [
                    "[MinKey, MaxKey]"
                  ]
                },
                "keysExamined": 100
              }
            }
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      },
      {
        "$groupByDistinctScan": {
          "newRoot": {
            "_id": "$a"
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      },
      {
        "$sort": {
          "sortKey": {
            "_id": 1
          }
        },
        "totalDataSizeSortedBytesEstimate": 22100,
        "usedDisk": false,
        "spills": 0,
        "spilledDataStorageSize": 0,
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      }
    ],
Enter fullscreen mode Exit fullscreen mode

MongoDB recognizes that the pipeline needs only one result per distinct indexed value. The winning plan is a covered DISTINCT_SCAN. It examines 100 keys, reads no documents, and returns 100 values to $groupByDistinctScan. This is the ideal access path for this query.

DocumentDB 0.114-0 (before this optimization)

I load exactly the same 50,000 documents in DocumentDB 0.114-0 on PostgreSQL through the MongoDB-compatible gateway:

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

for (let start = 0; start < 50000; start += 1000) {
  const batch = [];
  for (let i = start; i < start + 1000; i++) {
    batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
  }
  db.distinct_group.insertMany(batch);
}

db.distinct_group.createIndex({a: 1}, {name: "a_1"});
Enter fullscreen mode Exit fullscreen mode

Before running the plans, I vacuum the physical PostgreSQL table, from psql:

\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'distinct116' AND collection_name = 'distinct_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;
Enter fullscreen mode Exit fullscreen mode

In real life, this runs automatically by the background auto-vacuum of PostgreSQL but I don't want to wait and prefer a deterministic test.

The 0.114-0 output identifies the extension and the collection that was vacuumed:

Pager usage is off.
SET
 documentdb_version 
--------------------
 0.114-0
(1 row)

VACUUM
 vacuumed_collection_id 
------------------------
 4
(1 row)
Enter fullscreen mode Exit fullscreen mode

I then run the same aggregation through the MongoDB API:

db = db.getSiblingDB("distinct116");

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));
Enter fullscreen mode Exit fullscreen mode

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 3.197,
    "explainCommandExecTimeMillis": 179.281,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "IXSCAN",
              "indexName": "a_1",
              "direction": "Forward",
              "isIndexOnlyScan": true,
              "startupCost": 0,
              "totalCost": 13.89,
              "indexFilterSet": [
                {
                  "a": {
                    "$range": {
                      "orderByScan": 1
                    }
                  }
                }
              ],
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {

            "nReturned": 50000,
            "executionTimeMillis": 109.417,
            "executionStartAtTimeMillis": 0.09,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "IXSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 109.417,
              "executionStartAtTimeMillis": 0.09,
              "indexName": "a_1",
              "totalDocsAnalyzed": 0,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 176.999,
            "executionStartAtTimeMillis": 1.335,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 176.999,
              "executionStartAtTimeMillis": 1.335,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 179.079,
            "executionStartAtTimeMillis": 178.996,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 179.079,
              "executionStartAtTimeMillis": 178.996,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 32,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 178.606,
                "executionStartAtTimeMillis": 1.343,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}
Enter fullscreen mode Exit fullscreen mode

The gateway reports an index-only IXSCAN, but its cursor returns all 50,000 index entries. totalDocsAnalyzed: 0 confirms that the table is not visited, while totalKeysExamined: 50000 shows the remaining work. The GROUP stage reduces these entries to 100 groups.

The native PostgreSQL call uses the same database, collection, pipeline, hint, planner settings, and post-vacuum state:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);
Enter fullscreen mode Exit fullscreen mode

The PostgreSQL execution plan is:

                                                                                                                                                                     QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson)
               Buffers: shared hit=24
               ->  Index Only Scan using a_1 on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson), collection.document
                     Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                     Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                     Heap Fetches: 0
                     Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(20 rows)
Enter fullscreen mode Exit fullscreen mode

The PostgreSQL plan makes the old behavior explicit. Index Only Scan using a_1 reads 50,000 rows before GroupAggregate returns 100. Heap Fetches: 0 is good, but the executor still visits every duplicate index entry.

DocumentDB 0.116-0 (after this optimization)

The new distinct-scan path is controlled by documentdb.enableGroupByDistinctScan. It is disabled by default in this image, so I enable it explicitly. ALTER SYSTEM is used from psql because the MongoDB gateway opens independent PostgreSQL sessions:

\pset pager off
ALTER SYSTEM SET documentdb.enableGroupByDistinctScan TO 'on';
SELECT pg_reload_conf();
SHOW documentdb.enableGroupByDistinctScan;
Enter fullscreen mode Exit fullscreen mode

After restarting the local DocumentDB container, I run the same gateway setup script, I vacuum the new physical collection with the same script, and run the query unchanged. Here is the complete MongoDB-compatible gateway output from DocumentDB 0.116-0:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 1.482,
    "explainCommandExecTimeMillis": 1.631,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "DISTINCT_SCAN",
              "ns": "distinct116.distinct_group",
              "startupCost": 0,
              "totalCost": 0,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "indexName": "a_1",
                "direction": "Forward",
                "isIndexOnlyScan": true,
                "indexUsage": {
                  "indexKeyString": "{\"a\": 1}",
                  "isMultiKey": false,
                  "bounds": [
                    "[\"a\": (MinKey, MaxKey)]"
                  ]
                },
                "startupCost": 0,
                "totalCost": 0,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            },
            "indexCosts": [
              {
                "namespace": "distinct116.distinct_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 0.696,
            "executionStartAtTimeMillis": 0.042,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "DISTINCT_SCAN",
              "nReturned": 100,
              "executionTimeMillis": 0.696,
              "executionStartAtTimeMillis": 0.042,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 100,
                "executionTimeMillis": 0.531,
                "executionStartAtTimeMillis": 0.038,
                "indexName": "a_1",
                "totalDocsAnalyzed": 0,
                "indexUsage": {
                  "scanLoops": 100,
                  "scanType": "ordered",
                  "scanKeys": [
                    "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                  ]
                },
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.085,
            "executionStartAtTimeMillis": 0.07,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 1.085,
              "executionStartAtTimeMillis": 0.07,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.475,
            "executionStartAtTimeMillis": 1.39,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 1.475,
              "executionStartAtTimeMillis": 1.39,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 29,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 1.273,
                "executionStartAtTimeMillis": 0.074,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}
Enter fullscreen mode Exit fullscreen mode

The winning cursor stage is now DISTINCT_SCAN over the index-only IXSCAN. It returns 100 entries instead of 50,000. The gateway reports totalKeysExamined: 100, totalDocsAnalyzed: 0, and 100 scan loops: one ordered index probe per distinct value.

The native PostgreSQL script verifies the physical operator:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByDistinctScan TO on;

SHOW documentdb.enableGroupByDistinctScan;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);
Enter fullscreen mode Exit fullscreen mode

Here is the PostgreSQL execution plan:

QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson)
               Buffers: shared hit=24
               ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=100 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson), collection.document
                     namespaceName: distinct116.distinct_group
                     indexName: a_1
                     indexKey: {"a": 1}
                     isMultiKey: false
                     indexBounds: ["a": (MinKey, MaxKey)]
                     innerScanLoops: 100 loops
                     scanType: ordered
                     scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
                     _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
                     Buffers: shared hit=24
                     ->  Custom Scan (DocumentDBApiDistinctQueryScan) (actual rows=100 loops=1)
                           Output: collection.document
                           Buffers: shared hit=24
                           ->  Index Only Scan using a_1 on documentdb_data.documents_10 collection (actual rows=100 loops=1)
                                 Output: collection.document
                                 Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                                 Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                                 Heap Fetches: 0
                                 Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(35 rows)
Enter fullscreen mode Exit fullscreen mode

DocumentDBApiDistinctQueryScan wraps the index-only scan. The inner Index Only Scan now reports actual rows=100 instead of 50,000, with Heap Fetches: 0. This is not only a different plan name: 49,900 duplicate index entries no longer pass through the aggregate.

Conclusion

Here is a summary of the result on this 50,000-document example:

Engine Access path Index entries read Heap/document reads Groups
MongoDB 8.0.28 covered DISTINCT_SCAN 100 0 100
DocumentDB 0.114-0 gateway index-only IXSCAN 50,000 0 analyzed 100
DocumentDB 0.114-0 native Index Only Scan 50,000 0 heap fetches 100
DocumentDB 0.116-0 gateway, feature on DISTINCT_SCAN over index-only IXSCAN 100 0 analyzed 100
DocumentDB 0.116-0 native, feature on DocumentDBApiDistinctQueryScan over Index Only Scan 100 0 heap fetches 100

On this query, DocumentDB 0.116-0 reaches the same physical efficiency as MongoDB: both read one covered index entry per group and no documents. Compared with DocumentDB 0.114-0, the new access path reduces index entries flowing into the aggregate from 50,000 to 100, a 500-fold reduction. This is the kind of MongoDB compatibility that matters for performance: the same query is translated into the same efficient access strategy.

Top comments (0)