DocumentDB 0.116 (MongoDB 7.0)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

//
// 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);
category_1_operations.date_-1
[Function: insert]

--
-- this runs in the background. I run it manually for reproducible tests
--

vacuum analyze

VACUUM


//
// Which Category 1 account had the most recent activity?
//

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

[
  {
    _id: 341,
    operations: [
      { date: ISODate('2026-09-26T11:43:10.219Z'), amount: 777 },
      { date: ISODate('2026-09-26T11:43:10.224Z'), amount: 848 }
    ]
  }
]

//
// this uses the compound index to find the category and get accounts
// ordered by latest operation - without the need to read all and sort
//

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.21,
  estimatedTotalKeysExamined: 1,
  inputStage: {
    stage: 'PROJECT',
    startupCost: 0,
    totalCost: 0.21,
    estimatedTotalKeysExamined: 1,
    inputStage: {
      stage: 'FETCH',
      ns: 'test.accounts',
      startupCost: 0,
      totalCost: 32.11,
      estimatedTotalKeysExamined: 158,
      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: 32.11,
        hasOrderBy: true,
        indexFilterSet: [ { category: { '$eq': 1 } } ],
        estimatedTotalKeysExamined: 158
      }
    }
  }
}

SET documentdb_core.bsonUseEJson TO true;

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
);

SET




document


{ "_id" : { "$numberInt" : "341" }, "operations" : [ { "date" : { "$date" : { "$numberLong" : "1790422990219" } }, "amount" : { "$numberInt" : "777" } }, { "date" : { "$date" : { "$numberLong" : "1790422990224" } }, "amount" : { "$numberInt" : "848" } } ] }



SELECT 1


---
--- As it's PostgreSQL behind we can see the execution plan with PostgreSQL
--- operations on the extended RUM index scan which includes
--- the filter (Index Cond:) and sort (Order By:), and projection (Output:)
---

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.076..0.077 rows=1.00 loops=1)

  Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, '{ "operations.amount" : { "$numberInt" : "1" }, "operations.date" : { "$numberInt" : "1" } }'::documentdb_core.bson, '{ "now" : { "$date" : { "$numberLong" : "1790422994043" } } }'::documentdb_core.bson)

  Buffers: shared hit=4

  ->  Limit (actual time=0.070..0.070 rows=1.00 loops=1)

        Output: collection.document, (documentdb_api_catalog.bson_orderby(collection.document, '{ "operations.date" : { "$numberInt" : "-1" } }'::documentdb_core.bson))

        Buffers: shared hit=4

        ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual time=0.069..0.070 rows=1.00 loops=1)

              Output: collection.document, documentdb_api_catalog.bson_orderby(collection.document, '{ "operations.date" : { "$numberInt" : "-1" } }'::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: 40)]

              _id_: (startup cost=0.275, total cost=43.377, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=947, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

              Buffers: shared hit=4

              ->  Index Scan using "category_1_operations.date_-1" on documentdb_data.documents_2 collection (actual time=0.026..0.027 rows=1.00 loops=1)

                    Output: collection.document

                    Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@=) '{ "category" : { "$numberInt" : "1" } }'::documentdb_core.bson)

                    Order By: (collection.document OPERATOR(documentdb_api_catalog.|-<>) '{ "operations.date" : { "$numberInt" : "-1" } }'::documentdb_core.bson)

                    Index Searches: 0

                    Buffers: shared hit=4

Planning:

  Buffers: shared hit=2

Planning Time: 0.126 ms

Execution Time: 0.104 ms



EXPLAIN


---
--- Create relational tables to show the difference
---


SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, public;  

CREATE TABLE accounts AS
SELECT  
  bson_get_value_text(t.document, '_id')::int      AS account_id,  
  bson_get_value_text(t.document, 'category')::int AS category
FROM (  
  SELECT document  
  FROM bson_aggregation_pipeline('test',  
    '{"aggregate":"accounts","pipeline":[],"cursor":{}}')  
) t;

CREATE TABLE account_operations AS
SELECT  
  bson_get_value_text(t.document, '_id')::int      AS account_id,  
  ((bson_get_value_text(t.document, 'operations')::json)->'date'->>'$date')::timestamptz AS "date",
  ((bson_get_value_text(t.document, 'operations')::json)->>'amount')::int AS "amount"  
FROM (  
  SELECT document  
  FROM bson_aggregation_pipeline('test',  
    '{"aggregate":"accounts","pipeline":[{"$unwind":"$operations"}],"cursor":{}}')  
) t;

CREATE INDEX ON accounts (account_id, category);

CREATE INDEX ON account_operations (account_id, "date");

SET



SELECT 947



SELECT 1000



CREATE INDEX



CREATE INDEX


VACUUM ANALYZE;

VACUUM


---
--- A join to run the same query
---

WITH latest_account AS (
  SELECT a.account_id
  FROM accounts AS a
  JOIN account_operations AS o ON o.account_id = a.account_id
  WHERE a.category = 1
  ORDER BY o."date" DESC
  LIMIT 1
)
SELECT a.account_id, a.category, o."date", o.amount
FROM latest_account AS l
JOIN accounts AS a ON a.account_id = l.account_id
JOIN account_operations AS o ON o.account_id = l.account_id
ORDER BY o."date";


account_id
category
date
amount


341
1
2026-09-26 11:43:10.219+00
777

341
1
2026-09-26 11:43:10.224+00
848



SELECT 2


---
--- With relational tables it must join before sort and limit
---

EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
WITH latest_account AS (
  SELECT a.account_id
  FROM accounts AS a
  JOIN account_operations AS o ON o.account_id = a.account_id
  WHERE a.category = 1
  ORDER BY o."date" DESC
  LIMIT 1
)
SELECT a.account_id, a.category, o."date", o.amount
FROM latest_account AS l
JOIN accounts AS a ON a.account_id = l.account_id
JOIN account_operations AS o ON o.account_id = l.account_id
ORDER BY o."date";


QUERY PLAN


Sort (actual time=0.322..0.324 rows=2.00 loops=1)

  Output: a.account_id, a.category, o.date, o.amount

  Sort Key: o.date

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=18

  ->  Nested Loop (actual time=0.319..0.321 rows=2.00 loops=1)

        Output: a.account_id, a.category, o.date, o.amount

        Buffers: shared hit=18

        ->  Nested Loop (actual time=0.315..0.316 rows=1.00 loops=1)

              Output: a_1.account_id, a.account_id, a.category

              Buffers: shared hit=15

              ->  Limit (actual time=0.309..0.310 rows=1.00 loops=1)

                    Output: a_1.account_id, o_1.date

                    Buffers: shared hit=12

                    ->  Sort (actual time=0.309..0.309 rows=1.00 loops=1)

                          Output: a_1.account_id, o_1.date

                          Sort Key: o_1.date DESC

                          Sort Method: top-N heapsort  Memory: 25kB

                          Buffers: shared hit=12

                          ->  Hash Join (actual time=0.108..0.268 rows=340.00 loops=1)

                                Output: a_1.account_id, o_1.date

                                Hash Cond: (o_1.account_id = a_1.account_id)

                                Buffers: shared hit=12

                                ->  Seq Scan on documentdb_api.account_operations o_1 (actual time=0.005..0.055 rows=1000.00 loops=1)

                                      Output: o_1.account_id, o_1.date, o_1.amount

                                      Buffers: shared hit=7

                                ->  Hash (actual time=0.099..0.099 rows=319.00 loops=1)

                                      Output: a_1.account_id

                                      Buckets: 1024  Batches: 1  Memory Usage: 20kB

                                      Buffers: shared hit=5

                                      ->  Seq Scan on documentdb_api.accounts a_1 (actual time=0.004..0.064 rows=319.00 loops=1)

                                            Output: a_1.account_id

                                            Filter: (a_1.category = 1)

                                            Rows Removed by Filter: 628

                                            Buffers: shared hit=5

              ->  Index Only Scan using accounts_account_id_category_idx on documentdb_api.accounts a (actual time=0.005..0.005 rows=1.00 loops=1)

                    Output: a.account_id, a.category

                    Index Cond: (a.account_id = a_1.account_id)

                    Heap Fetches: 0

                    Index Searches: 1

                    Buffers: shared hit=3

        ->  Index Scan using account_operations_account_id_date_idx on documentdb_api.account_operations o (actual time=0.003..0.003 rows=2.00 loops=1)

              Output: o.account_id, o.date, o.amount

              Index Cond: (o.account_id = a.account_id)

              Index Searches: 1

              Buffers: shared hit=3

Planning:

  Buffers: shared hit=36

Planning Time: 0.362 ms

Execution Time: 0.346 ms



EXPLAIN

account_id	category	date	amount
341	1	2026-09-26 11:43:10.219+00	777
341	1	2026-09-26 11:43:10.224+00	848