$lookup join strategies: understanding the trade-offs with flexible documents

In a previous post, I explored how MongoDB chooses between nested loop, indexed loop, and hash join strategies for $lookup. Here, I examine what occurs when $lookup runs on DocumentDB for PostgreSQL—an open-source extension implementing the MongoDB API on PostgreSQL.

The document model minimizes the need for joins by embedding related data directly within documents. However, when a join is necessary — such as for reference data that updates independently, many-to-many relationships, or dimensional lookups — the flexibility of embedding can complicate join optimization.

The goal isn't just to identify "which database is faster"—it's to understand why their behaviors differ, the trade-offs involved, and the options when join performance matters.

Relational databases tend to perform more joins because normalized schemas require them, but they also optimize joins more effectively thanks to scalar, well-typed columns. In contrast, document databases perform fewer joins thanks to embedding, but when they do, flexible field semantics—such as arrays—restrict the available join algorithms.

I've run all tests in Docker containers with default settings on the same machine. The timings are indicative, not benchmarks — they illustrate the relative cost of different approaches, not absolute performance under production conditions (caching, concurrency, hardware, and tuning would all change the numbers).

An example: fact and reference table

In a document database, you'd typically embed related data to avoid joins. But some data doesn't embed well:

• Exchange rates change continuously. If you embed rate_to_usd inside each portfolio document, you'd need to update millions of documents every time a rate moves.
• Portfolios reference a currency, and you want to compute USD valuations by looking up the current rate at query time.

This is a classic case where a $lookup join makes sense: a large fact collection (portfolios) joined to a small, frequently-updated reference collection (fxRates). The document model can't avoid this join without accepting stale embedded rates.

Schema and data generation

I created two collections:

• portfolios: 5 million documents with a currency field (5 distinct values)
• fxRates: 5 documents mapping each currency to its USD exchange rate

I used mongosh to create and load the collection with the following commands:

db.portfolios.drop();
db.fxRates.drop();

const currencies = ["USD", "EUR", "CHF", "GBP", "JPY"];
currencies.forEach(cur => {
  db.fxRates.insertOne({
    currency: cur,
    rate_to_usd: Math.random() * (1.5 - 0.5) + 0.5,
    last_updated: new Date()
  });
});

const totalPortfolios = 5e6;
let bulk = [];
for (let i = 1; i <= totalPortfolios; i++) {
  const currency = currencies[Math.floor(Math.random() * currencies.length)];
  bulk.push({
    portfolioId: i,
    clientId: Math.floor(Math.random() * 10000),
    valuation: Math.round(Math.random() * 1_000_000),
    currency: currency,
    asOfDate: new Date()
  });
  if (bulk.length === 10000) {
    db.portfolios.insertMany(bulk);
    bulk = [];
  }
}
if (bulk.length > 0) db.portfolios.insertMany(bulk);

db.fxRates.createIndex({ currency: 1 }, { unique: true });

The index on a five-document collection is not strictly necessary, but it's good practice and protects my lookup table from duplicates.

The Query with $lookup

This query fetches all portfolios, retrieves the foreign exchange rate for each currency, and converts the valuation to USD.

db.portfolios.aggregate([
  {$lookup: {
    from: "fxRates",
    localField: "currency",
    foreignField: "currency",
    as: "fx"
  }},
  {$unwind: "$fx"},
  {$project: {
    portfolioId: 1, valuation: 1, currency: 1,
    rate_to_usd: "$fx.rate_to_usd",
    valuation_usd: {$multiply: ["$valuation", "$fx.rate_to_usd"]}
  }}
])

MongoDB's $lookup combined with $unwind behaves like a LEFT OUTER JOIN followed by filtering out non-matching rows.

Why flexible documents make joins hard

In a relational database, portfolios.currency is a VARCHAR column. The optimizer knows it's a single scalar value per row. It can extract it, hash it, sort it, or probe an index with it — all with well-defined operators.

In a document database, currency might be:

• A string: "USD"
• An array: ["USD", "EUR"]
• Missing entirely
• A nested document

MongoDB's $lookup compatibility requires the following behavior:

• If localField is an array ["USD", "EUR"], it matches any foreign document where foreignField equals "USD" OR "EUR" (or contains either, if it's also an array).
• It's effectively an "any element matches any element" semantic.

This means that the join condition is not always a simple equality a = b, but may involve “any element matches” semantics evaluated at runtime. Instead, the matching logic must evaluate each document's field at runtime, determine whether it's a scalar or an array, and match accordingly.

The safest general approach is a lateral join — executing the inner query for each outer document and passing the current document's field value into the matching function. This is what both MongoDB and DocumentDB for PostgreSQL do.

What happens under the hood (DocumentDB for PostgreSQL)

I use the DocumentDB API in a SQL query rather than the MongoDB-compatible endpoint to view the PostgreSQL execution plan.

EXPLAIN (ANALYZE ON, BUFFERS ON, COSTS ON, VERBOSE ON)
SELECT document
FROM bson_aggregation_pipeline('test',
'{
  "aggregate": "portfolios",
  "pipeline": [
    {
      "$lookup": {
        "from": "fxRates",
        "localField": "currency",
        "foreignField": "currency",
        "as": "fx"
      }
    },
    {
      "$unwind": "$fx"
    },
    {
      "$project": {
        "portfolioId": 1,
        "valuation": 1,
        "currency": 1,
        "rate_to_usd": "$fx.rate_to_usd",
        "valuation_usd": {
          "$multiply": [
            "$valuation",
            "$fx.rate_to_usd"
          ]
        }
      }
    }
  ],
  "cursor": {}
}');

Since I joined a large collection with a small one and require all documents from both, I would anticipate a hash join. Instead, it uses a nested loop join:

Nested Loop  (actual time=579..64792 rows=5000000 loops=1)
  ->  Seq Scan on documents_11 collection  (rows=5000000 loops=1)
  ->  Seq Scan on documents_10 collection_0_1  (rows=1 loops=5000000)
        Filter: bson_dollar_lookup_join_filter(...)
        Rows Removed by Filter: 4
Execution Time: 87750 ms

The fxRates table (5 rows, fitting in a single 8kB block) is scanned 5 million times. PostgreSQL's cost-based optimizer knows the table is tiny and fits in cache, so a sequential scan is the right choice over an index scan — but the scan is still executed 5 million times because of the LATERAL pattern. The filter function bson_dollar_lookup_join_filter is evaluated 25 million times. This function handles array semantics — it extracts the field from the outer document, determines whether it's scalar or an array, and checks for matches in the inner document.

Because the inner side is marked as LATERAL, it depends on the current outer row. This prevents PostgreSQL from evaluating both sides independently, which is required for hash or merge joins. As a result, only a nested loop strategy is possible.

In MongoDB, the equivalent behavior is the IndexedLoopJoin strategy: for each outer document, probe the index on the foreign field. The algorithm and per-document cost are the same.

A note on MongoDB's Hash Join

MongoDB 8.0 can use hash join for $lookup when allowDiskUse: true, no compatible index on the foreign field, the foreign collection is small, and the SBE engine is active. Under these conditions, MongoDB builds an in-memory hash table from the foreign collection, correctly handling array semantics by storing per-element entries.

In tests with 5M portfolios and 5 fxRates, MongoDB's native HashJoin finished in ~14 seconds — the fastest of my tests. Without tweaks, it took 170 seconds — the worst.

To achieve 14 seconds, I dropped the index on the foreign field, enabled allowDiskUse, and set internalQueryFrameworkControl to trySbeEngine. The default trySbeRestricted mode doesn't push the $lookup and $unwind to SBE, since the optimization depends on feature flags that aren't enabled in this mode. With trySbeEngine, SBE handles the pipeline, using HashJoin:

// Setup for hash join
db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "trySbeEngine"});
db.fxRates.dropIndex("currency_1");

// The query (same as all other tests)
db.portfolios.aggregate([
  {$lookup: {from: "fxRates", localField: "currency", foreignField: "currency", as: "fx"}},
  {$unwind: "$fx"},
  {$project: {portfolioId: 1, valuation: 1, currency: 1, rate_to_usd: "$fx.rate_to_usd", valuation_usd: {$multiply: ["$valuation", "$fx.rate_to_usd"]}}}
], {allowDiskUse: true}).explain("executionStats");

// Restore
db.fxRates.createIndex({currency: 1}, {unique: true});
db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "trySbeRestricted"});

DocumentDB for PostgreSQL doesn't currently implement this optimization — it relies on PostgreSQL's native join strategies, which don't understand BSON array semantics. Under normal conditions, both MongoDB and DocumentDB use a Nested Loop join.

Attempting alternatives via the MongoDB API

Using _id as Join Key (~71s)

The documentDB extension has a special case when foreignField is _id — it uses direct object_id equality:

// Reshape fxRates to use currency as _id
db.fxRates.drop();
currencies.forEach(cur => {
  db.fxRates.insertOne({
    _id: cur,
    rate_to_usd: Math.random() * (1.5 - 0.5) + 0.5,
    last_updated: new Date()
  });
});

db.portfolios.aggregate([
  {$lookup: {from: "fxRates", localField: "currency", foreignField: "_id", as: "fx"}},
  {$unwind: "$fx"},
  {$project: {portfolioId:1, valuation:1, currency:1,
              rate_to_usd:"$fx.rate_to_usd",
              valuation_usd:{$multiply:["$valuation","$fx.rate_to_usd"]}}}
])

It uses an index scan with the join condition applied as an Index Cond, which is more efficient than a sequential scan with a Filter. It's slightly faster, taking 71 seconds instead of 88 seconds, yet it remains a nested loop with 5 million iterations:

Nested Loop  (actual time=17..48170 rows=5000000 loops=1)
  ->  Seq Scan on documents_11 collection  (rows=5000000 loops=1)
  ->  Index Scan using _id_ on documents_12  (rows=1 loops=5000000)
        Index Cond: (object_id = ANY (bson_dollar_lookup_extract_filter_array(...)))
Execution Time: 70578 ms

This is the same as MongoDB's IndexedLoopJoin — the _id field is guaranteed to be scalar, so the extension can use a direct equality lookup on the primary key. However, it doesn't change the join strategy.

Uncorrelated $lookup + $filter (~68s)

A minor enhancement involves reading all fxRates at once, using an empty pipeline and no join condition, attaching the data as an array, and then filtering locally:

db.portfolios.aggregate([
  {$lookup: {from: "fxRates", pipeline: [], as: "allFx"}},
  {$addFields: {
    fx: {$arrayElemAt: [{$filter: {
      input: "$allFx", as: "r",
      cond: {$eq: ["$$r.currency", "$currency"]}
    }}, 0]}
  }},
  {$project: {portfolioId:1, valuation:1, currency:1,
              rate_to_usd:"$fx.rate_to_usd",
              valuation_usd:{$multiply:["$valuation","$fx.rate_to_usd"]}}}
])

The execution plan shows a Nested Loop with a single loop:

Nested Loop  (actual time=17..20177 rows=5000000 loops=1)
  ->  Aggregate  (rows=1 loops=1)          -- reads fxRates ONCE
  ->  Seq Scan on documents_11  (rows=5000000 loops=1)
Execution Time: 67905 ms  (of which ~48s is $addFields+$project)

The join itself is fast — fxRates are aggregated once into a single array. But the per-document $filter + $arrayElemAt evaluates BSON expressions 5 million times. We traded "nested loop probe" for "per-row array scan in BSON space".

This is conceptually similar to the "nested loop with materialization" approach from the previous MongoDB article — reading the lookup collection once, but matching per-document in the projection.

Pipeline-Based $lookup — No Help

Using $lookup with pipeline and let doesn't enhance performance:

  {$lookup: {
    from: "fxRates",
    let: { cur: "$currency" },
    pipeline: [
      {$match: {$expr: {$eq: ["$currency", "$$cur"]}}}
    ],
    as: "fx"
  }},
  {$unwind: "$fx"},

The extension still creates a LATERAL join (all code paths set rightTree->lateral = true), and it introduces additional overhead due to variable resolution.

With the MongoDB-compatible API, no solution significantly improves the efficiency of the join. But on DocumentDB, the power of SQL opens new possibilities.

The PostgreSQL escape hatch: SQL with Hash Join

Since DocumentDB stores data in standard PostgreSQL tables, we can query the same collections with SQL—within the same transaction and with full ACID guarantees. The trade-off is that we lose flexible-document join semantics and assume scalar join keys.

Prerequisite: enabling Hash Join for the BSON type (a hack)

The bson type has a hash operator class (bson_hash_ops) used for GROUP BY and DISTINCT. But the = operator doesn't declare hash join support — it's missing HASHES and MERGES properties. This is likely intentional, since bson = bson comparison on full documents has different semantics than field-level equality. But for my investigation (comparing extracted scalar field values), it would work:

-- Requires superuser — this is a hack, not a supported configuration
-- If DocumentDB enables this in the future, it will be part of the extension
ALTER OPERATOR documentdb_core.= (documentdb_core.bson, documentdb_core.bson)
  SET (COMMUTATOR = OPERATOR(documentdb_core.=), HASHES, MERGES);

Without this, PostgreSQL cannot execute hash join for bson = bson conditions, even in custom SQL. However, note that the SQL hash join method, enabled by this hack, does not replicate MongoDB's "any element matches" behavior when joined fields include arrays.

The Query: CTE with Hash Join (~39s)

To utilize a SQL join, I first query the two collections within two common table expressions in the WITH clause, then join them in the main query:

WITH portfolios AS (
  SELECT document FROM documentdb_api.collection('test', 'portfolios')
),
fxRates AS (
  SELECT document FROM documentdb_api.collection('test', 'fxRates')
)
SELECT documentdb_api_internal.bson_dollar_project(
  documentdb_api_internal.bson_dollar_merge_documents_at_path(
    p.document, f.document, 'fx'),
  '{ "portfolioId" : 1, "valuation" : 1, "currency" : 1,
     "rate_to_usd" : "$fx.rate_to_usd",
     "valuation_usd" : { "$multiply" : ["$valuation", "$fx.rate_to_usd"] } }'::bson,
  '{}'::bson
)
FROM portfolios p
JOIN fxRates f
  ON documentdb_api_catalog.bson_expression_get(
       p.document, '{"": "$currency"}'::bson, true)
   = documentdb_api_catalog.bson_expression_get(
       f.document, '{"": "$currency"}'::bson, true);

With this query and the operator tweak enabling hash join, I have the following execution plan:

Hash Join  (actual time=7.4..34018 rows=5000000 loops=1)
  Hash Cond: (bson_expression_get(documents_11.document, '{"":"$currency"}'...)
            = bson_expression_get(documents_10.document, '{"":"$currency"}'...))
  ->  Seq Scan on documents_11  (rows=5000000 loops=1)
  ->  Hash  (rows=5 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on documents_10  (rows=5 loops=1)
Execution Time: 38664 ms

PostgreSQL creates a small 5-row hash table (9 kB) and probes it once per portfolio. It makes a single pass over both collections. Most of the remaining time is spent calling bson_expression_get 5 million times to retrieve the join key, along with bson_dollar_merge_documents_at_path and bson_dollar_project to generate the final output.

In the end, this query is only about twice as fast. It requires a complex workaround, breaks document semantics, and still spends most of its time evaluating BSON expressions.

Results Summary

I started Docker containers as:

# Start MongoDB (minimal setup for ACID transactions)
docker exec -it $(
 docker run -d -p 27017:27017 mongo --replSet rs0
) mongosh --eval "rs.initiate()"

# Start DocumentDB (PostgreSQL and MongoDB-compatible endpoints)
docker run -d -p 9712:9712 -p 10260:10260 \
 ghcr.io/documentdb/documentdb/documentdb-local:latest \
 --username "franck" --password "franck" --start-pg

Below is a summary of my experiments, run in Docker containers with default configurations, involving 5 million portfolios, 5 fxRates, and a unique index on fxRates.currency:

MongoDB's native HashJoin via the Slot-Based Execution engine is fastest, handling hash table build/probe natively with per-element array support and avoiding BSON field extraction overhead, but will not be used without configuration tweaks. The DocumentDB SQL escape hatch uses PostgreSQL's optimizer for the same join strategy but incurs overhead with bson_expression_get on each row.

The other solutions are compatible with standard configurations and use appropriate data models and query code. Remember that the time here reflects reading five million documents, and the difference may be insignificant on small datasets.

The Trade-off: Flexibility vs. Optimization

These experiments show the trade-off clearly. Relational systems rely on joins due to normalization, but they can optimize them effectively thanks to typed scalar columns. Document databases avoid many joins, but when joins are needed, flexible semantics—like arrays—limit the available algorithms.

DocumentDB for PostgreSQL sits in the middle. It relies on PostgreSQL storage and execution while preserving MongoDB semantics. As a result, $lookup uses only a subset of SQL's join capabilities to preserve this flexibility. The SQL workaround shows that performance improves when you enforce scalar semantics, but this runs counter to the expectations of a document model, where any field in one document can be an array in another.

So the real question is not which system is faster, but which trade-off you choose: flexibility with embedded arrays or optimization for scalar values.

This was tested on MongoDB 8.0 and DocumentDB 0.112 on PostgreSQL 17.10, and both can improve in the future. Optimization is possible when the field is a known scalar. But if you have a fixed schema, do you still want a document database or switch to SQL? PostgreSQL can also gain optimizations that benefit DocumentDB queries. For example, the lateral join could be memoized in a future version.

If you're thinking about using DocumentDB for PostgreSQL — whether you're migrating from MongoDB or starting fresh — don't stop at the first slow query. Look into the causes, since the trade-off between speed and flexibility can differ. Check execution plans, and file an issue or start a discussion. More feedback from real workloads helps the contributors improve the extension. That's a major advantage of open source.