DEV Community

Cover image for DocumentDB 0.109: $match $sort $limit $project covered by index scan
Franck Pachot
Franck Pachot

Posted on

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

#database #mongodb #postgres #sql

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 https://youtu.be/Hq9CFhxSqgw?si=g6CnDuv_PqPHRquc
//

db.accounts.createIndex({
  category: 1,
  "operations.date": -1,
});

function insert(num) {
  const ops = [];
  for (let i = 0; i < num; i++) {
    const account = Math.floor(Math.random() * 10_000) + 1;
    const category = Math.floor(Math.random() * 3);
    const operation = {
      date: new Date(),
      amount: Math.floor(Math.random() * 1_000) + 1,
    };
    ops.push({
      updateOne: {
        filter: { _id: account },
        update: {
          $set: { category: category },
          $push: { operations: operation },
        },
        upsert: true,
      },
    });
  }
  db.accounts.bulkWrite(ops);
}

insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
Enter fullscreen mode Exit fullscreen mode

As the account operations are embedded as an array for each account, a single index can serve filtering on account's attributes, like "category", and operation's attributes, like "date".

A simple query asks Which Category 1 account had the most recent activity? and this involves a filter on category and operation date:


db.accounts.find(
    { "category": 1 },
    { "operations.amount": 1, "operations.date": 1 }
  ).sort({ "operations.date": -1 }).limit(1);
Enter fullscreen mode Exit fullscreen mode

This is typical of pagination, filtering a specific number of documents from an ordered result:

[
  {
    _id: 6115,
    operations: [
      { date: ISODate('2026-09-25T22:34:03.218Z'), amount: 320 },
      { date: ISODate('2026-09-25T22:34:05.739Z'), amount: 847 }
    ]
  }
]
Enter fullscreen mode Exit fullscreen mode

The MongoDB-compatible execution plan shows that it didn't require a sort operation as the index scan provides the documents in the expected order:

db.accounts.find(
    { "category": 1 },
    { "operations.amount": 1, "operations.date": 1 }
  ).sort({ "operations.date": -1 }).limit(1).explain().queryPlanner.winningPlan

{
  stage: 'LIMIT',
  startupCost: 0,
  totalCost: 0.2,
  estimatedTotalKeysExamined: 1,
  inputStage: {
    stage: 'PROJECT',
    startupCost: 0,
    totalCost: 0.2,
    estimatedTotalKeysExamined: 1,
    inputStage: {
      stage: 'FETCH',
      ns: 'test.accounts',
      startupCost: 0,
      totalCost: 208.79,
      estimatedTotalKeysExamined: 1055,
      inputStage: {
        stage: 'IXSCAN',
        ns: 'test.accounts',
        indexName: 'category_1_operations.date_-1',
        direction: 'Forward',
        indexUsage: {
          indexKeyString: '{"category": 1,"operations.date": -1}',
          isMultiKey: true,
          bounds: [
            '["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]'
          ]
        },
        startupCost: 0,
        totalCost: 208.79,
        hasOrderBy: true,
        indexFilterSet: [ { category: { '$eq': 1 } } ],
        estimatedTotalKeysExamined: 1055
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

I can run the same query, as an aggregation pipeline, on the PostgreSQL endpoint though DocumentDB functions:

EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
    'test',
    '{"aggregate": "accounts", "pipeline": [
  {"$match": {"category": 1}},
  {"$sort": {"operations.date": -1}},
  {"$limit": 1},
  {"$project": {"operations.amount": 1, "operations.date": 1}}
    ], "cursor": {}}'::documentdb_core.bson
);

                                                   QUERY PLAN
--------------------------------------------------------------------------
Subquery Scan on agg_stage_3 (actual time=0.111..0.112 rows=1.00 loops=1)
  Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, 'BSONHEX31000000106f7065726174696f6e732e616d6f756e740001000000106f7065726174696f6e732e64617465000100000000'::documentdb_core.bson, 'BSONHEX12000000096e6f7700d660b4daa001000000'::documentdb_core.bson)
  Buffers: shared hit=5
  ->  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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)