Here's a detailed look at scalar $group index pushdown, an optimization introduced with DocumentDB 0.117-0 on September 10, 2026. It also highlights an advantage over MongoDB: the PostgreSQL query planner ensures optimal performance without requiring additional hints in the query.
DocumentDB is a fully open-source PostgreSQL extension that brings the MongoDB API to the SQL table. It offers MongoDB users an alternative by integrating with PostgreSQL, transforming MongoDB operators into efficient PostgreSQL access paths. Microsoft leads the development, continuously enhancing the extension based on valuable feedback from enterprise customers on Azure DocumentDB.
To demonstrate the optimization, I load 50,000 documents with 100 distinct a values and 200-byte payloads. I create an index on {a: 1} then calculate {$group: {_id: null, total: {$sum: "$a"}}}. The query has no hint, so the optimizer must decide whether it can answer the accumulator from the a_1 index.
Controlled method
MongoDB, DocumentDB 0.116, and DocumentDB 0.117 process the same documents, indexes, and unhinted pipelines. Each DocumentDB gateway execution is divided into setup, VACUUM (ANALYZE), and query phases. I compare rows, heap fetches, and buffers rather than elapsed time across different containers.
The feature is present in 0.117 but disabled by default. I enable only documentdb.enable_scalar_aggregate_index_pushdown. All other settings stay the same.
MongoDB 8.0 reference
I initially perform the unhinted aggregation on MongoDB Atlas 8.0:
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex({a: 1});
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": "2",
"queryPlanner": {
"namespace": "perf117.scalar_group",
"parsedQuery": {},
"indexFilterSet": false,
"queryHash": "7234A6DE",
"planCacheShapeHash": "7234A6DE",
"planCacheKey": "2A6F009E",
"optimizationTimeMillis": 0,
"optimizedPipeline": true,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"queryPlan": {
"stage": "GROUP",
"planNodeId": 3,
"inputStage": {
"stage": "COLLSCAN",
"planNodeId": 1,
"filter": {},
"direction": "forward"
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s8 env: { }",
"stages": "[3] project [s8 = newBsonObj(\"_id\", s6, \"total\", s7)] \n[3] project [s6 = null, s7 = doubleDoubleSumFinalize(s5)] \n[3] group [] [s5 = aggDoubleDoubleSum(s1)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"a05943ac-772f-4c76-b128-e6e3bfdd0158\" true false "
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 12,
"totalKeysExamined": 0,
"totalDocsExamined": 50000,
"executionStages": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"8": "newBsonObj(\"_id\", s6, \"total\", s7) "
},
"inputStage": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"6": "null ",
"7": "doubleDoubleSumFinalize(s5) "
},
"inputStage": {
"stage": "group",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"groupBySlots": [],
"expressions": {
"5": "aggDoubleDoubleSum(s1) ",
"initExprs": {
"5": null
}
},
"mergingExprs": {
"4": "aggMergeDoubleDoubleSums(s4) "
},
"usedDisk": false,
"spills": 0,
"spilledBytes": 0,
"spilledRecords": 0,
"spilledDataStorageSize": 0,
"inputStage": {
"stage": "scan",
"planNodeId": 1,
"nReturned": 50000,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"numReads": 50000,
"recordSlot": 2,
"recordIdSlot": 3,
"scanFieldNames": [
"a"
],
"scanFieldSlots": [
1
]
}
}
}
}
},
"queryShapeHash": "F1620CAE8E90491891C0FB6F23ECD9556823F83467253A897C540C2A88AC66E5",
"command": {
"aggregate": "scalar_group",
"pipeline": [
{
"$group": {
"_id": null,
"total": {
"$sum": "$a"
}
}
}
],
"cursor": {},
"$db": "perf117"
},
"serverInfo": {
"host": "a004f7434a57",
"port": 27017,
"version": "8.0.28",
"gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted",
"internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
},
"ok": 1
}
}
MongoDB performs a COLLSCAN for this unhinted scalar aggregation, scanning all 50,000 documents without using any index keys, resulting in one group. The aggregation does not spill, but the documents containing payload data are still read.
MongoDB creates index-access plans only when the index helps with filtering, sorting, or a DISTINCT_SCAN rewrite, which isn't the case here. To force an IXSCAN for this query, you need to specify the index explicitly using { hint: { a: 1 } } (this bypasses the query planner so you must ensure that the result remains unaffected, especially if using a partial index):
db.scalar_group.explain(
"executionStats"
).aggregate(
pipeline, { hint: { a: 1 } }
).queryPlanner.winningPlan
;
{
isCached: false,
queryPlan: {
stage: 'GROUP',
planNodeId: 3,
inputStage: {
stage: 'PROJECTION_COVERED',
planNodeId: 2,
transformBy: { a: true, _id: false },
inputStage: {
stage: 'IXSCAN',
planNodeId: 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]' ] }
}
}
},
slotBasedPlan: {
slots: '$$RESULT=s7 env: { }',
stages: '[3] project [s7 = newBsonObj("_id", s5, "total", s6)] \n' +
'[3] project [s5 = null, s6 = doubleDoubleSumFinalize(s4)] \n' +
'[3] group [] [s4 = aggDoubleDoubleSum(s1)] spillSlots[s3] mergingExprs[aggMergeDoubleDoubleSums(s3)] \n' +
'[1] ixseek KS(0A0104) KS(F0FE04) none s2 none none lowPriority [s1 = 0] @"10eeeb21-169c-4c72-bbb4-4a674cbf37db" @"a_1" true '
}
}
With the hint, MongoDB can use an index-only scan (IXSCAN + PROJECTION_COVERED), reading only 50,000 index entries.
DocumentDB 0.116 (before this optimization)
I load and index the same data through the DocumentDB 0.116 gateway (MongoDB-compatible endpoint):
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.scalar_group.countDocuments(),
indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
I run VACUUM (ANALYZE) in PostgreSQL to simulate the auto-vacuum job in a reproducible way:
VACUUM (ANALYZE)
;
Then I run the unchanged scalar aggregation:
db = db.getSiblingDB("perf117");
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 4.189,
"explainCommandExecTimeMillis": 159.291,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "COLLSCAN",
"startupCost": 0,
"totalCost": 2477,
"estimatedTotalKeysExamined": 50000
}
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "COLLSCAN",
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"numBlocksFromCache": 1852
}
}
}
},
{
"$root": {
"queryPlanner": {
"winningPlan": {
"stage": "GENERIC_AGGREGATE",
"startupCost": 0,
"totalCost": 2602.02,
"aggStrategy": "Sorted",
"estimatedTotalKeysExamined": 1
}
},
"executionStats": {
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"executionStages": {
"stage": "GENERIC_AGGREGATE",
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"numBlocksFromCache": 1852
}
}
}
}
],
"ok": 1
}
}
DocumentDB 0.116 also performs a collection scan through the gateway. It examines 50,000 documents and reports 1,852 shared-buffer hits.
I run the same setup and pipeline through the native DocumentDB API in the PostgreSQL endpoint, but explicitly disable Seq Scan to show why the Index Scan is more expensive:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
'perf117',
'scalar_group',
format(
'{"_id":%s,"a":%s,"payload":"%s"}',
i, i % 100, repeat('x', 200)
)::documentdb_core.bson,
NULL
))
FROM generate_series(1, 50000) i;
SELECT documentdb_api_internal.create_indexes_non_concurrently(
'perf117',
'{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
true
);
SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
'perf117',
'{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);
...
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f7700876814cda001000000'::bson, NULL::text)), 'BSONHEX070000000a0000'::bson
Buffers: shared hit=2101
-> Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
Output: document
namespaceName: perf117.scalar_group
indexName: _id_
_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=2101
-> Index Scan using _id_ on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
Output: document
Index Cond: (collection.shard_key_value = '4'::bigint)
Buffers: shared hit=2101
Planning:
Buffers: shared hit=254
(15 rows)
Without scalar pushdown, DocumentDB 0.116 scans the _id_ index and visits the table for all 50,000 rows. The plan reports 2,101 shared-buffer hits. The a_1 index cannot yet supply the accumulator directly.
In this case, the Index Scan was not selected by default because it was estimated to be more costly—I had to disable the Seq Scan to reveal it.
DocumentDB 0.117 (with the optimization)
Since this feature is off by default in 0.117, where it was introduced, I enable it on the server running the MongoDB gateway and restart the local container to ensure new gateway sessions adopt the setting.
ALTER SYSTEM SET documentdb.enable_scalar_aggregate_index_pushdown TO 'on';
SELECT pg_reload_conf();
The setting used for the captured run is:
documentdb.enable_scalar_aggregate_index_pushdown
---------------------------------------------------
on
(1 row)
The source code indicates that, like many features, it will be enabled in a later version: /* Added in v117, Pending stabilization, enable in v119 */. Since then, v0.118 has been renamed to v1.0 and v0.119 to v1.1 as DocumentDB prepares for a 1.0 release.
I repeat the same setup from the MongoDB endpoint:
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.scalar_group.countDocuments(),
indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
The same maintenance step makes the heap all-visible:
VACUUM (ANALYZE);
The query is unchanged and still has no hint:
db = db.getSiblingDB("perf117");
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 1.54,
"explainCommandExecTimeMillis": 414.061,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "IXSCAN",
"ns": "perf117.scalar_group",
"indexName": "a_1",
"direction": "Forward",
"isIndexOnlyScan": true,
"indexUsage": {
"indexKeyString": "{\"a\": 1}",
"isMultiKey": false,
"bounds": [
"[\"a\": (MinKey, MaxKey)]"
]
},
"startupCost": 0,
"totalCost": 4.01,
"indexFilterSet": [
{
"a": {
"$range": {
"fullScan": true
}
}
}
],
"estimatedTotalKeysExamined": 16667
},
"indexCosts": [
{
"namespace": "perf117.scalar_group",
"costs": [
{
"indexName": "_id_",
"indexKeyString": "{\"_id\": 1}",
"startupCost": 0.415,
"totalCost": 1379.415,
"selectivity": 1,
"correlation": 0.75,
"estimatedPercentIndexPagesLoaded": 100,
"estimatedTotalIndexEntries": 50000,
"boundarySelectivity": 1
}
]
}
]
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 98.976,
"executionStartAtTimeMillis": 0.084,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "IXSCAN",
"nReturned": 50000,
"executionTimeMillis": 98.976,
"executionStartAtTimeMillis": 0.084,
"indexName": "a_1",
"totalDocsAnalyzed": 0,
"indexUsage": {
"scanLoops": 100,
"scanType": "ordered",
"scanKeys": [
"key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
]
},
"totalKeysExamined": 50000,
"numBlocksFromCache": 24
}
}
}
},
{
"$root": {
"queryPlanner": {
"winningPlan": {
"stage": "GENERIC_AGGREGATE",
"startupCost": 0,
"totalCost": 45.7,
"aggStrategy": "Sorted",
"estimatedTotalKeysExamined": 1
}
},
"executionStats": {
"nReturned": 1,
"executionTimeMillis": 413.955,
"executionStartAtTimeMillis": 413.95,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"executionStages": {
"stage": "GENERIC_AGGREGATE",
"nReturned": 1,
"executionTimeMillis": 413.955,
"executionStartAtTimeMillis": 413.95,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"numBlocksFromCache": 24
}
}
}
}
],
"ok": 1
}
}
The 0.117 gateway now performs an IXSCAN on a_1 with isIndexOnlyScan: true. It scans 50,000 index entries, reports totalDocsAnalyzed: 0, and makes 24 shared-buffer hits. The 200-byte payload remains unread.
The native query differs from the 0.116 script only in enabling the released feature:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
'perf117',
'scalar_group',
format(
'{"_id":%s,"a":%s,"payload":"%s"}',
i, i % 100, repeat('x', 200)
)::documentdb_core.bson,
NULL
))
FROM generate_series(1, 50000) i;
SELECT documentdb_api_internal.create_indexes_non_concurrently(
'perf117',
'{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
true
);
SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SET documentdb.enable_scalar_aggregate_index_pushdown TO on;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
'perf117',
'{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f77006d7a14cda001000000'::bson, NULL::text)), 'BSONHEX070000000a0000'::bson
Buffers: shared hit=24
-> Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
Output: document
namespaceName: perf117.scalar_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_: (indexKey='{"_id": 1}', 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
-> Index Only Scan using a_1 on documentdb_data.documents_3 collection (actual rows=50000 loops=1)
Output: document
Index Cond: (collection.document @<> 'BSONHEX18000000036100100000000866756c6c5363616e00010000'::bson)
Heap Fetches: 0
Buffers: shared hit=24
Planning:
Buffers: shared hit=328
(22 rows)
PostgreSQL shows the same change as Index Only Scan using a_1, with Heap Fetches: 0 and 24 shared-buffer hits. This is the access path underlying the MongoDB-compatible IXSCAN stage.
DocumentDB and PostgreSQL two-step optimization
Before this change, scalar $group index-path generation considered the group key but not the fields referenced by its accumulators. With a constant key such as _id: null, it could overlook a covering index on an accumulator field. The optimization collects simple "$field" accumulator paths and adds a fullScan qual for each. In this example, the native plan shows document @<> '{"a":{"fullScan":true}}', while the gateway explain shows the marker in indexFilterSet. The marker expands to inclusive MinKey–MaxKey bounds, so it matches all documents rather than filtering the result.
This exposes an access path. It does not force the planner to choose it.
PostgreSQL chooses the path: it costs the available alternatives and, in this run, selects the a_1 Index Only Scan. The reported access-path cost is 4.01, versus 1,379.415 for the _id_ index alternative. Those are planner estimates, not execution times. VACUUM (ANALYZE) supplies statistics and marks heap pages all-visible, helping the scan avoid heap fetches, but the injected condition does not guarantee that PostgreSQL will choose it.
In comparison, MongoDB’s hinted plan also scans a_1 from MinKey to MaxKey—shown explicitly as indexBounds: {a: ['[MinKey, MaxKey]']} but obtaining that covered path required the user’s {hint: {a: 1}} option. That hint directs MongoDB to use the index, whereas DocumentDB’s fullScan marker leaves the final access-path choice to PostgreSQL’s cost-based planner.
Conclusion
This is a measurable optimization improvement, not just a new plan-node name. DocumentDB 0.117 replaces full-document access with a covered scan of the accumulator field.
Here is a summary of the experiment:
| Engine and API | Access path | Index entries | Heap/document reads | Shared-buffer hits |
|---|---|---|---|---|
| MongoDB 8.0 | COLLSCAN |
0 | 50,000 documents | not reported |
MongoDB 8.0 with hint()
|
IXSCAN |
50,000 | 0 | not reported |
| DocumentDB 0.116 gateway (MongoDB API) | COLLSCAN |
not applicable | 50,000 documents | 1,852 |
| DocumentDB 0.116 native (SQL function) with Seq Scan disabled |
_id_ Index Scan
|
50,000 | 50,000 heap rows | 2,101 |
| DocumentDB 0.117 gateway (MongoDB API), feature on | index-only IXSCAN on a_1
|
50,000 | 0 (totalDocsAnalyzed) |
24 |
| DocumentDB 0.117 native (SQL function), feature on |
Index Only Scan on a_1
|
50,000 | 0 (Heap Fetches) |
24 |
Without needing the developer to manually suggest index usage, DocumentDB 0.117 naturally chooses a more efficient access path compared to un-hinted MongoDB 8.0 in the captured plans. MongoDB reads 50,000 documents, while DocumentDB accesses only the covered index. This extension uniquely combines the best of both worlds: MongoDB's emulation with BSON-based access paths and extended RUM indexes, plus PostgreSQL's smart query planner and cost-based optimizer. Note: It relies on accurate planner statistics for optimization and a fresh visibility map for execution. I've run VACUUM ANALYZE manually here to create a consistent test case without delay, but statistics gathering also runs smoothly in the background through PostgreSQL’s auto-vacuum.
Top comments (0)