DEV Community

Franck Pachot
Franck Pachot

Posted on

DocumentDB 0.113: $group covering index

#documentdb #opensource #mongodb #postgres

This builds on my previous article, Covering Index for $group/$sum in MongoDB Aggregation, which showed that a hinted covering index can make hash-based grouping read index entries instead of documents. DocumentDB 0.113 (June 22, 2026) adds the corresponding index-only access path to its PostgreSQL execution engine.

DocumentDB is a fully open source PostgreSQL extension that implements the MongoDB API, giving MongoDB applications an open alternative on PostgreSQL. What matters to me is that it goes beyond parsing MongoDB syntax: operators become real PostgreSQL access paths and executor operations. Microsoft is the main contributor, improving the extension from the enterprise customer feedback on Azure DocumentDB.

To demonstrate the covered aggregate, I load 10,000 sales with ten categories, amount modulo 17, and a 200-byte payload, then group by category and sum the amount. Those fields are covered by an index on { category: 1, amount: 1 }.

I compare DocumentDB 0.112 with 0.113. Both runs load the same rows, create the same explicitly ordered index, run the same VACUUM (ANALYZE), disable the same PostgreSQL scan alternatives, and execute the same pipeline. The release is the only variable.

MongoDB 8.0 reference

I've run the following on MongoDB Atlas:

use test;
db.sales.drop();
db.sales.insertMany(Array.from({ length: 10000 }, (_, i) => ({
  _id: i + 1,
  category: (i + 1) % 10,
  amount: (i + 1) % 17,
  payload: "x".repeat(200)
})));

db.sales.createIndex(
    { category: 1, amount: 1 }
);

const pipeline = [
  { $group: { _id: "$category", total: { $sum: "$amount" } } }
];

db.sales.aggregate(pipeline, {
  hint: "category_1_amount_1"
});

db.sales.explain("executionStats").aggregate(pipeline, {
  hint: "category_1_amount_1"
});
Enter fullscreen mode Exit fullscreen mode

Here is the queryPlanner.winningPlan:

{
  isCached: false,
  queryPlan: {
    stage: 'GROUP',
    planNodeId: 3,
    inputStage: {
      stage: 'PROJECTION_COVERED',
      planNodeId: 2,
      transformBy: { amount: true, category: true, _id: false },
      inputStage: {
        stage: 'IXSCAN',
        planNodeId: 1,
        keyPattern: { category: 1, amount: 1 },
        indexName: 'category_1_amount_1',
        isMultiKey: false,
        multiKeyPaths: { category: [], amount: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          category: [ '[MinKey, MaxKey]' ],
          amount: [ '[MinKey, MaxKey]' ]
        }
      }
    }
  },
  slotBasedPlan: {
    slots: '$$RESULT=s8 env: {  }',
    stages: '[3] project [s8 = newBsonObj("_id", s5, "total", s7)] \n' +
      '[3] project [s7 = doubleDoubleSumFinalize(s6)] \n' +
      '[3] group [s5] [s6 = aggDoubleDoubleSum(s2)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n' +
      '[3] project [s5 = (s1 ?: null)] \n' +
      '[1] ixseek KS(0A0A0104) KS(F0F0FE04) none s3 none none lowPriority [s1 = 0, s2 = 1] @"574c1bf9-12e8-4b16-8bb7-e5c60d523460" @"category_1_amount_1" true '
  }
}
Enter fullscreen mode Exit fullscreen mode

Here is the executionStats:

  executionStats: {
    executionSuccess: true,
    nReturned: 10,
    executionTimeMillis: 6,
    totalKeysExamined: 10000,
    totalDocsExamined: 0,
    executionStages: {
...
          inputStage: {
            stage: 'project',
            planNodeId: 3,
            nReturned: 10000,
            executionTimeMillisEstimate: 2,
            opens: 1,
            closes: 1,
            saveState: 0,
            restoreState: 0,
            isEOF: 1,
            projections: { '5': '(s1 ?: null) ' },
            inputStage: {
              stage: 'ixseek',
              planNodeId: 1,
              nReturned: 10000,
              executionTimeMillisEstimate: 2,
              opens: 1,
              closes: 1,
              saveState: 0,
              restoreState: 0,
              isEOF: 1,
              indexName: 'category_1_amount_1',
              keysExamined: 10000,
              seeks: 1,
              numReads: 10001,
              recordIdSlot: 3,
              outputSlots: [ Long('1'), Long('2') ],
              indexKeysToInclude: '00000000000000000000000000000011',
              seekKeyLow: 'KS(0A0A0104) ',
              seekKeyHigh: 'KS(F0F0FE04) '
            }
          }
        }
      }
    }
  },
Enter fullscreen mode Exit fullscreen mode

MongoDB uses PROJECTION_COVERED over IXSCAN: it examines 10,000 index keys (totalKeysExamined: 10000), fetches zero documents (totalDocsExamined: 0), and produces the ten groups (nReturned: 10). That is the useful reference behavior because the aggregate is answered from index values without reading collection documents (no stage: 'FETCH').

DocumentDB 0.112 (before this optimization)

When running the same on DocumentDB 0.112 we observe an IXSCAN but a FETCH above it, reading all documents - the aggregation is not visible in this execution plan:

        "executionStats": {
          "nReturned": 10000,
          "executionTimeMillis": 36.895,
          "executionStartAtTimeMillis": 0.048,
          "totalDocsExamined": 10000,
          "totalKeysExamined": 10000,
          "executionStages": {
            "stage": "FETCH",
            "nReturned": 10000,
            "executionTimeMillis": 36.895,
            "executionStartAtTimeMillis": 0.048,
            "totalKeysExamined": 10000,
            "numBlocksFromCache": 10008,
            "inputStage": {
              "stage": "IXSCAN",
              "nReturned": 10000,
              "executionTimeMillis": 36.895,
              "executionStartAtTimeMillis": 0.048,
              "indexName": "category_1_amount_1",
              "totalKeysExamined": 10000,
              "numBlocksFromCache": 10008
            }
          }
Enter fullscreen mode Exit fullscreen mode

Obviously, the aggregation was not pushed down to the MongoDB-compatible scan. I run the same on PostgreSQL with the DocumentDB API functions to understand the full execution:

\pset pager off
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'test',
  '{"aggregate":"sales","hint":"category_1_amount_1","pipeline":[{"$group":{"_id":"$category","total":{"$sum":"$amount"}}}],"cursor":{}}'
);

QUERY PLAN                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson)), 'total'::text, bsonsum(bson_expression_get(document, 'BSONHEX1300000002000800000024616d6f756e740000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson))), (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson)
   Buffers: shared hit=10008
   ->  Index Scan using category_1_amount_1 on documentdb_data.documents_6 collection (actual rows=10000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f77001e9afecaa001000000'::bson), document
         Index Cond: (collection.document @<> 'BSONHEX250000000363617465676f72790016000000106f7264657242795363616e00010000000000'::bson)
         Order By: (collection.document |-<> 'BSONHEX130000001063617465676f7279000100000000'::bson)
         Buffers: shared hit=10008
 Planning:
   Buffers: shared hit=430
(11 rows)
Enter fullscreen mode Exit fullscreen mode

With VERBOSE, the GroupAggregate output shows the category expression used for _id and the amount expression passed to bsonsum. The scan below outputs the category expression and document, but in 0.112 it is a regular Index Scan: PostgreSQL still visits the table and reports 10,008 shared-buffer hits.

DocumentDB 0.113 (after this optimization)

I run the same with DocumentDB 0.113 and the MongoDB-compatible execution plan shows no FETCH stage:

        "queryPlanner": {
          "winningPlan": {
            "stage": "IXSCAN",
            "indexName": "category_1_amount_1",
            "direction": "Forward",
            "isIndexOnlyScan": true,
            "startupCost": 0,
            "totalCost": 0.01,
            "indexFilterSet": [
              {
                "$range": {
                  "category": {
                    "orderByScan": 1
                  }
                }
              }
            ],
            "estimatedTotalKeysExamined": 6
          }
        },
Enter fullscreen mode Exit fullscreen mode

The execution statistics show "totalDocsAnalyzed": 0

        "executionStats": {
          "nReturned": 10000,
          "executionTimeMillis": 12.757,
          "executionStartAtTimeMillis": 0.065,
          "totalDocsExamined": 10000,
          "totalKeysExamined": 10000,
          "executionStages": {
            "stage": "IXSCAN",
            "nReturned": 10000,
            "executionTimeMillis": 12.757,
            "executionStartAtTimeMillis": 0.065,
            "indexName": "category_1_amount_1",
            "totalDocsAnalyzed": 0,
            "totalKeysExamined": 10000,
            "numBlocksFromCache": 9
          }
        }
Enter fullscreen mode Exit fullscreen mode

In the MongoDB-compatible execution plan, IXSCAN is still the stage name. What distinguishes an index only scan, in addition to the absence of FETCH above, is totalDocsAnalyzed which is the number of heap fetches analyzed for MVCC visibility, even if data is not needed because all fields are covered in the index. Here "totalDocsAnalyzed": 0 means that the PostgreSQL visibility map was fresh enough to avoid checking the heap.

I run it from PostgreSQL to get the familiar execution plan where the same is exposed as Heap Fetches: 0:

\pset pager off
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
  documentdb_api_internal, public;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'test',
  '{"aggregate":"sales","hint":"category_1_amount_1","pipeline":[{"$group":{"_id":"$category","total":{"$sum":"$amount"}}}],"cursor":{}}'
);
                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson)), 'total'::text, bsonsum(bson_expression_get(document, 'BSONHEX1300000002000800000024616d6f756e740000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson))), (bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson)
   Buffers: shared hit=9
   ->  Index Only Scan using category_1_amount_1 on documentdb_data.documents_2 collection (actual rows=10000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX1500000002000a0000002463617465676f72790000'::bson, true, 'BSONHEX12000000096e6f7700fd9efecaa001000000'::bson), document
         Index Cond: (collection.document @<> 'BSONHEX250000000363617465676f72790016000000106f7264657242795363616e00010000000000'::bson)
         Order By: (collection.document |-<> 'BSONHEX130000001063617465676f7279000100000000'::bson)
         Heap Fetches: 0
         Buffers: shared hit=9
 Planning:
   Buffers: shared hit=500
(12 rows)
Enter fullscreen mode Exit fullscreen mode

The verbose expressions are equivalent in 0.113, so the aggregate has not been simplified into a different calculation. The access path supplying them has changed to Index Only Scan. Although PostgreSQL labels one output value document, Heap Fetches: 0 and the low number of Buffers: shared hit=9 prove that it is supplied without visiting the table heap.

Conclusion

The improvement is visible on this test case: DocumentDB 0.113 shows Index Only Scan and Heap Fetches: 0 (9 shared hits), versus 10,008 shared hits in DocumentDB 0.112. With this improvement, the DocumentDB access performance is the same as MongoDB.

Here is a summary of the experiments:

Engine and API Access path Heap evidence Shared-buffer hits
MongoDB 8.0 PROJECTION_COVERED over IXSCAN totalDocsExamined: 0 not reported
DocumentDB 0.112 gateway (MongoDB API) FETCH over IXSCAN 10,000 rows fetched 10,008
DocumentDB 0.112 native (SQL function) Index Scan regular heap access 10,008
DocumentDB 0.113 gateway (MongoDB API) index-only IXSCAN totalDocsAnalyzed: 0 9
DocumentDB 0.113 native (SQL function) Index Only Scan Heap Fetches: 0 9

This feature brings the same behavior as MongoDB where the $group fields can benefit from a covering index. Unlike MongoDB, PostgreSQL does not generally require a hint. When the index-only path is cost-effective—and the visibility map is sufficiently current—the planner can select Index Only Scan automatically. In this experiment, planner settings and the DocumentDB hint were used only to make the comparison deterministic.

Top comments (0)