DocumentDB 0.109: $match $sort $limit $project covered by index scan

This should have been the first post of this series. A few months ago, I demonstrated (video) what I see as one of the key advantages of a document model: a compound index can support filtering, sorting, and pagination across data embedded in a one-to-many relationship. In a normalized relational model, that relationship typically spans multiple tables. Indexes belong to individual tables, so answering the same query may require to join more rows before sorting and filtering. That demo used MongoDB. Some MongoDB emulations on SQL databases pretend compatibility but lack performance it they don't implement MongoDB-style indexing or query plans. That's generally because the emulation is built on top of RDBMS indexes which were not designed for non-1NF schemas. Azure DocumentDB takes a different approach: it runs as a PostgreSQL extension which, thanks to PostgreSQL’s extensibility, can define native indexing for non-relational datatypes. The DocumentDB extension uses Extended RUM indexes. In this post, I’ll run the same as I did on MongoDB to show that DocumentDB on PostgreSQL provides the same performance with similar execution plan. You can also play with it on db<>fiddle https://dbfiddle.uk/Ft_2L34U: This starts by creating, though the MongoDB-compatible endpoint, the collection and index with accounts and operations: // // Create same data as

// db.accounts.createIndex({ category: 1, "operations.date": -1, }); function insert(num) { const ops = []; for (let i = 0; i Limit (actual time=0.101..0.101 rows=1.00 loops=1) Output: collection.document, (documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)) Buffers: shared hit=5 -> Custom Scan (DocumentDBApiExplainQueryScan) (actual time=0.100..0.100 rows=1.00 loops=1) Output: collection.document, documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson) namespaceName: test.accounts indexName: category_1_operations.date_-1 indexKey: {"category": 1,"operations.date": -1} isMultiKey: true indexBounds: ["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)] innerScanLoops: 1 loops scanType: ordered scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 116)] id: (startup cost=0.282, total cost=287.743, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=6328, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%) Buffers: shared hit=5 -> Index Scan using "category_1_operations.date_-1" on documentdb_data.documents_2 collection (actual time=0.055..0.055 rows=1.00 loops=1) Output: collection.document Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@=) 'BSONHEX130000001063617465676f7279000100000000'::documentdb_core.bson) Order By: (collection.document OPERATOR(documentdb_api_catalog.|-<>) 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson) Index Searches: 0 Buffers: shared hit=5 Planning: Buffers: shared hit=518 Planning Time: 1.605 ms Execution Time: 0.202 ms The Index Scan covered the $match filter with Index Cond , the $sort with Order By , the $limit: 1 with rows=1.00 and the $projection with Output . There no sort or filter above it. The Custom Scan is a purely decorative wrapper that DocumentDB injects around the real index access path so that EXPLAIN can expose MongoDB-style index metadata, like isMultiKey: true and the indexBounds . I got the remark that the planning time is huge here Planning Time: 1.605 ms . I've ran it on db<>fiddle which use micro-VM for fast ephemeral instances, so don't compare time. But still, Buffers: shared hit=518 as a lot and this is due to the first query in a PostgreSQL session that reads from the catalog. If I had run the query with explain first to check the result (https://dbfiddle.uk/eFaEQ0Gq), the next query would have had much faster planning: Planning: Buffers: shared hit=2 Planning Time: 0.126 ms Execution Time: 0.104 ms The video demonstrated the main advantage of a document model with multi-key indexes on MongoDB. The same exists open-source on PostgreSQL with the DocumentDB extension. Extended RUM was added in v0.106 (August 29, 2025), ordered indexes/scans were enabled by default in v0.109 (March 09, 2026) - be sure that documentdb_extended_rum is listed in shared_preload_libraries - and later releases improved edge cases of it (e.g. collation support across 0.111-0.113, multikey fixes in 0.116). Top comments (0)