In September 2025, AWS announced it will contribute to the open-source DocumentDB extension for PostgreSQL, originally developed by Microsoft for CosmosDB and donated to the Linux Foundation. Amazon DocumentDB plans to migrate to this extension, but some improvements were already on the roadmap for 2025 such as a new query planner (also known as NQP or planner_version 2.0). Here is a great improvement for a simple query: get the last ten orders for a product in a specific country.
I created the "orders" collection and an index that covers the filtering and pagination for my use case:
db.orders.createIndex({
country_id: 1,
"order_details.product_id": 1,
created_at: -1
});
I place 20,000 orders across three countries, averaging about 6,667 per country:
let products = []; for (let p = 1; p <= 20; p++) {
products.push({ product_id: p, qty: (p % 5) + 1 });
}
let docs = []; for (let i = 20000; i > 0; i--) {
const country_id = (i % 3);
const order_details = products.slice(0, 1 + Math.floor(i / 1000));
docs.push({
country_id: country_id,
order_details: order_details,
created_at: new Date(Date.now() - (20000 - i) * 60000)
});
}
db.orders.insertMany(docs);
The following query retrieves the 10 most recent orders from country "1" that include product "15" in their order details array, sorted by order creation date in descending order:
db.orders.find(
{ country_id: 1,
order_details: { $elemMatch: { product_id: 15 } }
} ).sort({ created_at: -1 }).limit(10)
;
The compound index on ("country_id", "order_details.product_id", "created_at") is optimized for filtering and sorting directly through the index. It effectively demonstrates Amazon DocumentDB’s new query planner (NQP) enhancements — particularly in multi-key index bounds calculation, $elemMatch optimization, and sort removal — resulting in fewer documents scanned and faster query execution than with planner v1.
MongoDB
I began by executing this command in the reference for a document database—MongoDB:
test> db.orders.find(
... { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
... ).sort({ created_at: -1 }).limit(10).explain("executionStats")
{
explainVersion: '1',
queryPlanner: {
namespace: 'test.orders',
parsedQuery: {
'$and': [
{
order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
},
{ country_id: { '$eq': 1 } }
]
},
indexFilterSet: false,
queryHash: 'F529912D',
planCacheShapeHash: 'F529912D',
planCacheKey: '0FAD6C41',
optimizationTimeMillis: 0,
maxIndexedOrSolutionsReached: false,
maxIndexedAndSolutionsReached: false,
maxScansToExplodeReached: false,
prunedSimilarIndexes: false,
winningPlan: {
isCached: false,
stage: 'LIMIT',
limitAmount: 10,
inputStage: {
stage: 'FETCH',
filter: {
order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
},
inputStage: {
stage: 'IXSCAN',
keyPattern: {
country_id: 1,
'order_details.product_id': 1,
created_at: -1
},
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
isMultiKey: true,
multiKeyPaths: {
country_id: [],
'order_details.product_id': [ 'order_details' ],
created_at: []
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: {
country_id: [ '[1, 1]' ],
'order_details.product_id': [ '[15, 15]' ],
created_at: [ '[MaxKey, MinKey]' ]
}
}
}
},
rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 10,
executionTimeMillis: 0,
totalKeysExamined: 10,
totalDocsExamined: 10,
executionStages: {
isCached: false,
stage: 'LIMIT',
nReturned: 10,
executionTimeMillisEstimate: 0,
works: 11,
advanced: 10,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
limitAmount: 10,
inputStage: {
stage: 'FETCH',
filter: {
order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
},
nReturned: 10,
executionTimeMillisEstimate: 0,
works: 10,
advanced: 10,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
docsExamined: 10,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 10,
executionTimeMillisEstimate: 0,
works: 10,
advanced: 10,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
keyPattern: {
country_id: 1,
'order_details.product_id': 1,
created_at: -1
},
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
isMultiKey: true,
multiKeyPaths: {
country_id: [],
'order_details.product_id': [ 'order_details' ],
created_at: []
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: {
country_id: [ '[1, 1]' ],
'order_details.product_id': [ '[15, 15]' ],
created_at: [ '[MaxKey, MinKey]' ]
},
keysExamined: 10,
seeks: 1,
dupsTested: 10,
dupsDropped: 0
}
}
}
},
queryShapeHash: '4919DD1DFF01E767C00E497A5C23ADA7B3AC64059E2D454E3975CE17B836BC8A',
command: {
find: 'orders',
filter: {
country_id: 1,
order_details: { '$elemMatch': { product_id: 15 } }
},
sort: { created_at: -1 },
limit: 10,
'$db': 'test'
},
serverInfo: {
host: '365bf79b0370',
port: 27017,
version: '8.2.1',
gitVersion: '3312bdcf28aa65f5930005e21c2cb130f648b8c3'
},
serverParameters: {
internalQueryFacetBufferSizeBytes: 104857600,
internalQueryFacetMaxOutputDocSizeBytes: 104857600,
internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
internalDocumentSourceGroupMaxMemoryBytes: 104857600,
internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
internalQueryProhibitBlockingMergeOnMongoS: 0,
internalQueryMaxAddToSetBytes: 104857600,
internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,
internalQueryFrameworkControl: 'trySbeRestricted',
internalQueryPlannerIgnoreIndexWithCollationForRegex: 1
},
ok: 1,
'$clusterTime': {
clusterTime: Timestamp({ t: 1763157330, i: 1 }),
signature: {
hash: Binary.createFromBase64('AAAAAAAAAAAAAAAAAAAAAAAAAAA=', 0),
keyId: Long('0')
}
},
operationTime: Timestamp({ t: 1763157330, i: 1 })
}
This is an efficient execution plan that examines only ten index entries (keysExamined: 10) to retrieve the ten documents in the result (nReturned: 10).
Oracle
Before examining DocumentDB, I tested the same query on Oracle Autonomous Database 26ai to compare it with a less efficient execution plan:
db.aggregate([ { $sql : {
statement: "alter session set \
statistics_level=all",
resetSession : false }} ]);
db.orders.find(
{ country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
).sort({ created_at: -1 }).limit(10)
;
db.aggregate( [ { $sql : `
select * from
dbms_xplan.display_cursor(format=>'ALLSTATS LAST +PROJECTION +PEEKED_BINDS')
` } ] ).forEach(row => print(row.PLAN_TABLE_OUTPUT));
SQL_ID cx0upnw3dsmrt, child number 1
-------------------------------------
select /*+ FIRST_ROWS(10) */ "DATA",rawtohex("RESID"),"ETAG" from
"ORA"."orders" where JSON_EXISTS("DATA",'$?( (@.country_id.numberOnly()
== $B0) && ( exists(@.order_details[*]?( (@.product_id.numberOnly() ==
$B1) )) ) )' passing :1 as "B0", :2 as "B1" type(strict)) order by
JSON_QUERY("DATA", '$.created_at[*].max()') desc nulls last fetch next
10 rows only
Plan hash value: 345710747
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 1604 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 1604 | | | |
| 2 | VIEW | | 1 | 1 | 10 |00:00:00.01 | 1604 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1 | 10 |00:00:00.01 | 1604 | 9216 | 9216 | 8192 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| orders | 1 | 1 | 2000 |00:00:00.01 | 1604 | | | |
| 5 | HASH UNIQUE | | 1 | 1 | 2000 |00:00:00.01 | 19 | 1323K| 1323K| 1479K (0)|
|* 6 | INDEX RANGE SCAN (MULTI VALUE) | $ora:orders.country_id_1_order_details.product_id_1_created_at_-1 | 1 | 1 | 2000 |00:00:00.01 | 19 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
2 - (NUMBER): 15
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
6 - access("orders"."SYS_NC00005$"=SYS_CONS_ANY_SCALAR(:1, 3) AND "orders"."SYS_NC00006$"=SYS_CONS_ANY_SCALAR(:2, 3))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."DATA"[JSON,8200], "from$_subquery$_002"."RAWTOHEX("RESID")"[VARCHAR2,4000], "from$_subquery$_002"."ETAG"[RAW,16]
2 - "from$_subquery$_002"."DATA"[JSON,8200], "from$_subquery$_002"."RAWTOHEX("RESID")"[VARCHAR2,4000], "from$_subquery$_002"."ETAG"[RAW,16]
3 - (#keys=1) JSON_VALUE( /*+ QJSNMD5_TC_JCMP_JV */ JSON_QUERY("DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.created_at[*].max()' RETURNING JSON WITHOUT ARRAY WRAPPER NULL ON ERROR TYPE(LAX) ) FORMAT OSON , '$'
RETURNING ANY ORA_RAWCOMPARE(32767) ERROR ON ERROR TYPE(LAX) )[32767], "DATA" /*+ LOB_BY_VALUE */ [JSON,8200], "orders"."RESID"[RAW,2000], "ETAG"[RAW,16]
4 - "DATA" /*+ LOB_BY_VALUE */ [JSON,8200], "orders"."RESID"[RAW,2000], "ETAG"[RAW,16]
5 - (#keys=2) "orders".ROWID[ROWID,10], SYSVARCOL[8]
6 - "orders".ROWID[ROWID,10], "orders"."SYS_NC00005$"[RAW,4000], "orders"."SYS_NC00006$"[RAW,4000], SYSVARCOL[8]
db.aggregate([{$sql:{statement: "select banner from v$version"}}])[0];
[{BANNER: 'Oracle AI Database 26ai Enterprise Edition Release 23.26.0.1.0 - for Oracle Cloud and Engineered Systems'}]
Here, 2000 (A-Rows) index entries have been read, fetching the documents (TABLE ACCESS BY INDEX ROWID), to be all sorted (SORT ORDER BY STOPKEY), and filtered (COUNT STOPKEY) down to the ten rows of the result. The index has been used, but not efficiently because it is multi-key (MULTI VALUE) and must be deduplicated afterwards (HASH UNIQUE), which doesn't preserve the key order. This is less efficient than MongoDB multi-key indexes where deduplication happens during the scan and preserve the ordering.
Amazon DocumentDB with query planner v1
I created a DocumentDB cluster on AWS which defaults to the first version of the query planner:
I ran the same query and display the execution plan (plannerVersion: 1):
rs0 [direct: primary] test> db.orders.find(
... { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
... ).sort({ created_at: -1 }).limit(10).explain("executionStats")
... ;
{
queryPlanner: {
plannerVersion: 1,
namespace: 'test.orders',
winningPlan: {
stage: 'SUBSCAN',
inputStage: {
stage: 'LIMIT_SKIP',
inputStage: {
stage: 'SORT',
sortPattern: { created_at: -1 },
inputStage: {
stage: 'FETCH',
inputStage: {
stage: 'IXSCAN',
indexName: 'country_id_1_order_details.product_id_1_created_at_-1'
}
}
}
}
}
},
executionStats: {
executionSuccess: true,
executionTimeMillis: '43.478',
planningTimeMillis: '0.318',
executionStages: {
stage: 'SUBSCAN',
nReturned: '10',
executionTimeMillisEstimate: '43.046',
inputStage: {
stage: 'LIMIT_SKIP',
nReturned: '10',
executionTimeMillisEstimate: '43.040',
inputStage: {
stage: 'SORT',
nReturned: '10',
executionTimeMillisEstimate: '43.037',
sortPattern: { created_at: -1 },
inputStage: {
stage: 'FETCH',
nReturned: '2000',
executionTimeMillisEstimate: '42.211',
inputStages: [
{
stage: 'IXSCAN',
nReturned: '2000',
executionTimeMillisEstimate: '0.757',
indexName: 'country_id_1_order_details.product_id_1_created_at_-1'
},
{ nReturned: '1', executionTimeMillisEstimate: '0.019' }
]
}
}
}
}
},
serverInfo: { host: 'docdb-2025-11-12-10-10-14', port: 27017, version: '5.0.0' },
ok: 1,
operationTime: Timestamp({ t: 1763157127, i: 1 })
}
This execution plan is no better than Oracle's, reading 2000 index entries (stage: 'IXSCAN', nReturned: '2000') and retrieving 2000 documents, which are then sorted and filtered to produce the ten documents in the result.
Amazon DocumentDB with New Query Planner (v2)
I switched to the new query planner:
The execution plan shows plannerVersion: 2 and no FETCH and no SORT:
rs0 [direct: primary] test> db.orders.find(
... { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
... ).sort({ created_at: -1 }).limit(10).explain("executionStats")
... ;
{
queryPlanner: {
plannerVersion: 2,
namespace: 'test.orders',
winningPlan: {
stage: 'LIMIT_SKIP',
inputStage: {
stage: 'IXSCAN',
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
direction: 'forward',
indexCond: {
'$and': [
{ country_id: { '$eq': 1 } },
{ 'order_details.product_id': { '$eq': 15 } }
]
},
filter: {
order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
}
}
}
},
indexFilterSet: false,
indexFilterApplied: false,
executionStats: {
executionSuccess: true,
executionTimeMillis: '14.543',
planningTimeMillis: '14.311',
executionStages: {
stage: 'LIMIT_SKIP',
nReturned: '10',
executionTimeMillisEstimate: '0.155',
inputStage: {
stage: 'IXSCAN',
nReturned: '10',
executionTimeMillisEstimate: '0.152',
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
direction: 'forward',
indexCond: {
'$and': [
{ country_id: { '$eq': 1 } },
{ 'order_details.product_id': { '$eq': 15 } }
]
},
filter: {
order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
}
}
}
},
serverInfo: { host: 'docdb-2025-11-12-10-10-14', port: 27017, version: '5.0.0' },
ok: 1,
operationTime: Timestamp({ t: 1763157259, i: 1 })
}
This plan is optimal, similar to the one in MongoDB, reading only the necessary entries: stage: 'IXSCAN', nReturned: '10'.
DocumentDB extension for PostgreSQL
Since AWS implemented those improvements into the Amazon DocumentDB query planner and announced in parallel that they will contribute to the DocumentDB extension for PostgreSQL, we hope that they will do the same for it in the future. Currently, there are no optimizations for such queries and indexes. I started the latest container with PostgreSQL and the DocumentDB extension:
docker run -d -p 10260:10260 -p 9712:9712 --name pgddb \
ghcr.io/microsoft/documentdb/documentdb-local:latest \
--username ddb --password ddb
# connect to the MogoDB emulation gateway
mongosh 'mongodb://ddb:ddb@localhost:10260/?tls=true&tlsAllowInvalidCertificates=true'
An execution plan shows FETCH and SORT on lots of documents:
test> db.orders.find(
... { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
... ).sort({ created_at: -1 }).limit(10).explain("executionStats")
{
queryPlanner: {
namespace: 'test.orders',
winningPlan: {
stage: 'LIMIT',
estimatedTotalKeysExamined: 10,
inputStage: {
stage: 'SORT',
sortKeysCount: 1,
estimatedTotalKeysExamined: 77,
inputStage: {
stage: 'FETCH',
runtimeFilterSet: [
{ '$elemMatch': { order_details: { product_id: 15 } } }
],
estimatedTotalKeysExamined: 77,
inputStage: {
stage: 'IXSCAN',
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
indexFilterSet: [ { '$eq': { country_id: 1 } } ],
estimatedTotalKeysExamined: 922
}
}
}
}
},
executionStats: {
nReturned: Long('10'),
executionTimeMillis: Long('29'),
totalDocsExamined: Long('10'),
totalKeysExamined: Long('10'),
executionStages: {
stage: 'LIMIT',
nReturned: Long('10'),
executionTimeMillis: Long('29'),
totalKeysExamined: Long('10'),
totalDocsExamined: 10,
numBlocksFromCache: 1250,
numBlocksFromDisk: 0,
inputStage: {
stage: 'SORT',
nReturned: Long('10'),
executionTimeMillis: Long('29'),
totalKeysExamined: Long('10'),
totalDocsExamined: 10,
sortMethod: 'top-N heapsort',
totalDataSizeSortedBytesEstimate: 36,
numBlocksFromCache: 1250,
numBlocksFromDisk: 0,
inputStage: {
stage: 'FETCH',
nReturned: Long('2000'),
executionTimeMillis: Long('28'),
totalKeysExamined: Long('2000'),
totalDocsExamined: 6667,
exactBlocksRead: 1229,
lossyBlocksRead: 0,
totalDocsRemovedByRuntimeFilter: 4667,
totalDocsRemovedByIndexRechecks: 0,
numBlocksFromCache: 1250,
numBlocksFromDisk: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: Long('6667'),
executionTimeMillis: Long('0'),
totalKeysExamined: Long('6667'),
indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
numBlocksFromCache: 6,
numBlocksFromDisk: 0
}
}
}
}
},
explainVersion: 2,
command: "db.runCommand({explain:{ 'find': 'orders', 'filter': { 'country_id': 1, 'order_details': { '$elemMatch': { 'product_id': 15 } } }, 'sort': { 'created_at': -1 }, 'limit': 10 }})",
ok: 1
}
The index has been used only for the equality predicate on the top-level field "country_id", and all index entries for this country have been read (nReturned: 6667), fetching all documents, sorting, and filtering.
This query, common in OLTP applications that involve filtering, sorting, and pagination, is optimal in MongoDB. Amazon DocumentDB gets a similar execution plan when enabling the new query planner.
While databases can emulate the pupular MongoDB API for basic queries, achieving good performance depends on having a highly capable query planner supported by advanced multi-key indexes. Traditional SQL databases were not originally designed to handle documents with arrays, as they typically prefer normalization across multiple tables with joins. To use compound indexes effectively, however, all relevant fields for Equality, Sort, and Range filtering must be contained within the same document instead of being distributed across multiple rows in relational tables.
Top comments (0)