A case where SQL joins struggle but MongoDB documents shine

Claims such as "Joins are slow" or "Joins don't scale" often prompt me to showcase how efficiently rows can be joined in a SQL database (example). However, the user perception of slowness remains, and it's essential to listen to developers and understand their problems with joins. Joining tables in relational databases can sometimes lead to suboptimal execution plans when data filtering occurs post-join. This limitation arises because indexes are efficient when selective predicates are on the same table. However, there is no multi-table index, at least for OLTP. Data warehouses in Oracle Databases can use bitmap join indexes, star transformation, and materialized views to overcome this, but they are not suited to OLTP workloads. These suboptimal execution plans may be the reason why developers think that joins are slow, even if it is not the execution of the join itself that is slow. Denormalization can help, but it undermines the advantages of normalization. In contrast, document databases like MongoDB utilize embedded documents to optimize complex queries with fewer joins, and multi-key composite indexes offer efficient access paths that cover all selective filters . Here is an example in PostgreSQL and MongoDB. PostgreSQL relational model Relational databases normalize one-to-many relationships to separate tables. For instance, consider the relationship between orders and their corresponding order details, where each order can have multiple associated entries in the order details table. CREATE TABLE orders( id BIGSERIAL PRIMARY KEY, country_id INT, created_at TIMESTAMPTZ DEFAULT clock_timestamp() ); CREATE TABLE order_details ( id BIGINT REFERENCES orders ON DELETE CASCADE, line INT, product_id BIGINT, quantity INT, PRIMARY KEY(id, line) ); I insert some data, with a distribution of products that decrease in orders over time: BEGIN TRANSACTION; INSERT INTO orders(country_id) SELECT 10 * random() FROM generate_series(1,1000000); INSERT INTO order_details (id, line, product_id, quantity) SELECT id, generate_series(1,10), log(2,(1 + id * random())::int), 100 * random() FROM orders; COMMIT; A relational data model does not depend on specific access patterns. Optimizing these patterns requires indexes. My primary keys defined indexes for navigating between orders and order details, but I have another use case. To analyze orders by country, product, and date range, I create the following indexes: CREATE INDEX ON orders (country_id, created_at DESC, id); CREATE INDEX ON order_details (product_id, id); To analyze the latest orders for a specific country and product, I use the following SQL query: PREPARE query(int,int,int) AS SELECT id, created_at, product_id, quantity FROM orders JOIN order_details d USING(id) WHERE country_id=$1 AND product_id=$2 ORDER BY created_at DESC LIMIT $3 ; I vacuum and analyze to get the best execution plan: postgres=# VACUUM ANALYZE orders, order_details; VACUUM Ideally, such query should read only the rows for one country and one product, and get them ordered by date to apply the Top-n ORDER BY LIMIT without having to read all rows and sort them. postgres=# EXPLAIN (analyze, buffers, costs off) EXECUTE query(1, 15, 10) ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Limit (actual time=0.031..0.110 rows=10 loops=1) Buffers: shared hit=132 -> Nested Loop (actual time=0.030..0.108 rows=10 loops=1) Buffers: shared hit=132 -> Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.011..0.023 rows=39 loops=1) Index Cond: (country_id = 1) Heap Fetches: 0 Buffers: shared hit=5 -> Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.002..0.002 rows=0 loops=39) Index Cond: ((product_id = 15) AND (id = orders.id)) Buffers: shared hit=127 Planning: Buffers: shared hit=16 Planning Time: 0.272 ms Execution Time: 0.127 ms (15 rows) The execution plan is effective, beginning with the index on orders.created_at to eliminate the need for sorting. To preserve the order and push down the join filter, it uses a nested loop join to retrieve the rows from the other table. Since there is another filter on order_details.product_id, after the join, it had to read more rows (rows=39) to obtain the final required rows (rows=10), and then more loops. As my example is small, the consequence is minimal in terms of time, nested loops (loops=39) and buffers (shared hit=127), but highligh

Apr 6, 2025 - 22:10

A case where SQL joins struggle but MongoDB documents shine

Claims such as "Joins are slow" or "Joins don't scale" often prompt me to showcase how efficiently rows can be joined in a SQL database (example). However, the user perception of slowness remains, and it's essential to listen to developers and understand their problems with joins.

Joining tables in relational databases can sometimes lead to suboptimal execution plans when data filtering occurs post-join. This limitation arises because indexes are efficient when selective predicates are on the same table. However, there is no multi-table index, at least for OLTP. Data warehouses in Oracle Databases can use bitmap join indexes, star transformation, and materialized views to overcome this, but they are not suited to OLTP workloads. These suboptimal execution plans may be the reason why developers think that joins are slow, even if it is not the execution of the join itself that is slow.

Denormalization can help, but it undermines the advantages of normalization. In contrast, document databases like MongoDB utilize embedded documents to optimize complex queries with fewer joins, and multi-key composite indexes offer efficient access paths that cover all selective filters .

Here is an example in PostgreSQL and MongoDB.

PostgreSQL relational model

Relational databases normalize one-to-many relationships to separate tables. For instance, consider the relationship between orders and their corresponding order details, where each order can have multiple associated entries in the order details table.

CREATE TABLE orders(
  id BIGSERIAL PRIMARY KEY,
  country_id INT,
  created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

CREATE TABLE order_details (
  id BIGINT REFERENCES orders ON DELETE CASCADE,
  line INT,
  product_id BIGINT,
  quantity INT,
  PRIMARY KEY(id, line)
);

I insert some data, with a distribution of products that decrease in orders over time:

BEGIN TRANSACTION;

INSERT INTO orders(country_id)
 SELECT 10 * random() FROM generate_series(1,1000000);

INSERT INTO order_details (id, line, product_id, quantity)
 SELECT 
  id,
  generate_series(1,10),
  log(2,(1 + id * random())::int),
  100 * random()
  FROM orders;

COMMIT;

A relational data model does not depend on specific access patterns. Optimizing these patterns requires indexes. My primary keys defined indexes for navigating between orders and order details, but I have another use case.
To analyze orders by country, product, and date range, I create the following indexes:


CREATE INDEX ON orders (country_id, created_at DESC, id);

CREATE INDEX ON order_details (product_id, id);

To analyze the latest orders for a specific country and product, I use the following SQL query:


PREPARE query(int,int,int) AS
 SELECT id, created_at, product_id, quantity
 FROM orders
 JOIN order_details d USING(id)
 WHERE country_id=$1 AND product_id=$2
 ORDER BY created_at DESC LIMIT $3
;

I vacuum and analyze to get the best execution plan:

postgres=# VACUUM ANALYZE orders, order_details;
VACUUM

Ideally, such query should read only the rows for one country and one product, and get them ordered by date to apply the Top-n ORDER BY LIMIT without having to read all rows and sort them.

postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 15, 10)
;
                                                                                                                                                                                                 QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.031..0.110 rows=10 loops=1)
   Buffers: shared hit=132
   ->  Nested Loop (actual time=0.030..0.108 rows=10 loops=1)
         Buffers: shared hit=132
         ->  Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.011..0.023 rows=39 loops=1)
               Index Cond: (country_id = 1)
               Heap Fetches: 0
               Buffers: shared hit=5
         ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.002..0.002 rows=0 loops=39)
               Index Cond: ((product_id = 15) AND (id = orders.id))
               Buffers: shared hit=127
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.272 ms
 Execution Time: 0.127 ms
(15 rows)

The execution plan is effective, beginning with the index on orders.created_at to eliminate the need for sorting. To preserve the order and push down the join filter, it uses a nested loop join to retrieve the rows from the other table.
Since there is another filter on order_details.product_id, after the join, it had to read more rows (rows=39) to obtain the final required rows (rows=10), and then more loops. As my example is small, the consequence is minimal in terms of time, nested loops (loops=39) and buffers (shared hit=127), but highlights the issue: it requires reading four rows and ten pages for each row to return results.

If I run the same query with another product that hasn't been ordered recently, it reads lots of orders before finding ten ones that include this product:

postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 8, 10)
;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=15.614..16.661 rows=10 loops=1)
   Buffers: shared hit=37582
   ->  Gather Merge (actual time=15.613..16.659 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=37582
         ->  Nested Loop (actual time=1.396..9.112 rows=7 loops=3)
               Buffers: shared hit=37582
               ->  Parallel Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.015..0.546 rows=4165 loops=3)
                     Index Cond: (country_id = 1)
                     Heap Fetches: 0
                     Buffers: shared hit=70
               ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.002..0.002 rows=0 loops=12494)
                     Index Cond: ((product_id = 8) AND (id = orders.id))
                     Buffers: shared hit=37512
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.272 ms
 Execution Time: 16.684 ms
(19 rows)

To get the 10 rows result, this execution has read 12495 rows with 3 parallel processes (rows=4165 loops=3), and 37582 pages in total, before it was able to find the Top-10 verifying all filters.

The problem is that the user doesn't understand why it can take longer, as it is the same query and returns the same number of rows. Moreover, reading so many unnecessary pages impacts other queries as it occupies space in the shared buffer.

When the query planner estimates that this is too much, it does not choose to avoid a sort and switches to a hash join.


postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 5, 10)
;
                                                                 QUERY PLAN                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------                                     
 Limit (actual time=30.370..30.373 rows=10 loops=1)
   Buffers: shared hit=1882
   ->  Sort (actual time=30.369..30.371 rows=10 loops=1)
         Sort Key: orders.created_at DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=1882
         ->  Hash Join (actual time=28.466..30.324 rows=236 loops=1)
               Hash Cond: (d.id = orders.id)
               Buffers: shared hit=1882
               ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.013..1.434 rows=2311 loops=1)                                              
                     Index Cond: (product_id = 5)
                     Buffers: shared hit=1387
               ->  Hash (actual time=28.400..28.401 rows=99672 loops=1)
                     Buckets: 131072  Batches: 1  Memory Usage: 5697kB
                     Buffers: shared hit=495
                     ->  Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.010..13.136 rows=99672 loops=1)                                      
                           Index Cond: (country_id = 1)
                           Heap Fetches: 0
                           Buffers: shared hit=495
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.267 ms
 Execution Time: 30.415 ms
(23 rows)

This plan doesn't depend on the size of the result but must read too many rows (rows=2311 and rows=99672) before joining, filtering them (to rows=236), and sorting them. This is where it becomes a scalability problem: the response time depends on the size of the database rather than the result size. A query that is supposed to read orders from a small time window must read the whole history of orders for one country, and the whole history of details for one product.

Note that this example is the best case, where tables were freshly vacuumed, and Index Only Scan is optimal with Heap Fetches: 0. It will be more expensive on an active table.

MongoDB document model

MongoDB's document model allows embedding related data within a single collection, optimizing data locality in memory and disk.

Here is a collection that loads similar data to the previous sample, but with order details embedded in the orders document, like it is structered in a business document or an application object:

const bulkOps = [];
for (let i = 0; i < 1000000; i++) {
  const orderDetails = [];
  for (let line = 1; line <= 10; line++) {
    orderDetails.push({
      line: line,
      product_id: Math.floor(Math.log2(1 + i * Math.random())),
      quantity: Math.floor(100 * Math.random()),
    });
  }
  bulkOps.push({
    insertOne: {
      document: {
        country_id: Math.floor(10 * Math.random()),
        created_at: new Date(),
        order_details: orderDetails
      }
    }
  });
}
db.orders.bulkWrite(bulkOps);

One advantage of the document model is the ability to get an order with its details without any join:

test> db.orders.find().sort({created_at: -1}).limit(1);
[
  {
    _id: ObjectId('67f1a477aabaf2dad73f4791'),
    country_id: 3,
    created_at: ISODate('2025-04-05T21:45:21.546Z'),
    order_details: [
      { line: 1, product_id: 19, quantity: 40 },
      { line: 2, product_id: 18, quantity: 10 },
      { line: 3, product_id: 18, quantity: 75 },
      { line: 4, product_id: 18, quantity: 81 },
      { line: 5, product_id: 16, quantity: 66 },
      { line: 6, product_id: 14, quantity: 17 },
      { line: 7, product_id: 19, quantity: 82 },
      { line: 8, product_id: 19, quantity: 81 },
      { line: 9, product_id: 17, quantity: 56 },
      { line: 10, product_id: 19, quantity: 59 }
    ]
  }
]

Having all fields in one document allows creating a single index that covers all filters, and MongoDB supports multi-key indexes, which enables indexing fields in embedded subdocuments:

db.orders.createIndex(
 { "country_id": 1, "order_details.product_id": 1, "created_at": -1 }
);

The query to retreive the last ten orders for one country and one product is simple without join:

db.orders.find(
  { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
).sort({ created_at: -1 }).limit(10);

Let's check the execution plan:

mdb> db.orders.find(
   { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
 ).sort({ created_at: -1 }).limit(10).explain(`executionStats`).executionStats
;
{
  executionSuccess: true,
  nReturned: 10,
  executionTimeMillis: 0,
  totalKeysExamined: 10,
  totalDocsExamined: 10,
  executionStages: {
    isCached: false,
    stage: 'LIMIT',
    nReturned: 10,
    executionTimeMillisEstimate: 0,
    works: 11,
    advanced: 10,
    limitAmount: 10,
    inputStage: {
      stage: 'FETCH',
      filter: {
        order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
      },
      nReturned: 10,
      executionTimeMillisEstimate: 0,
      works: 10,
      advanced: 10,
      docsExamined: 10,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 10,
        executionTimeMillisEstimate: 0,
        works: 10,
        advanced: 10,
        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
      }
    }
  }
}

The plan shows lots of details, but the most important is:

  nReturned: 10,
  totalKeysExamined: 10,
  totalDocsExamined: 10,

To get the 10 rows for the result, MongoDB has read only 10 index entries and 10 documents. It is the most optimal, reading only what is necessary. The index scan is optimal as it contains the bounds for all equality filters and get the rows ordered without the need for an additional sort:

        indexBounds: {
          country_id: [ '[1, 1]' ],
          'order_details.product_id': [ '[15, 15]' ],
          created_at: [ '[MaxKey, MinKey]' ]
        },

In addition to be fast, the performance is predictable because this execution plan will always be the same. This is visible with allPlansExecution:

mdb> db.orders.find(
   { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
 ).sort({ created_at: -1 }).limit(10).explain(`allPlansExecution`).queryPlanner
;
{
  namespace: 'test.orders',
  parsedQuery: {
    '$and': [
      {
        order_details: { '$elemMatch': { product_id: { '$eq': 42 } } }
      },
      { country_id: { '$eq': 1 } }
    ]
  },
  indexFilterSet: false,
  queryHash: '0DAE06A4',
  planCacheShapeHash: '0DAE06A4',
  planCacheKey: 'C3D96884',
  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': 42 } } }
      },
      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': [ '[42, 42]' ],
          created_at: [ '[MaxKey, MinKey]' ]
        }
      }
    }
  },
  rejectedPlans: []
}

There is only one plan in cache, and no rejectedPlans.

Conclusion

When developers claim that "joins are slow" it is not about the execution itself. SQL databases have decades of optimization, with multiple join methods, caching and buffering. However, when filters can’t be pushed down before the join, it prevents early filtering, resulting in excessive row joined, long response time and unstable execution plans.

Developers and DBAs perceive situations differently. Developers experience slow response times and problematic joins, while DBAs observe the database's efficiency in joining lots of rows. For instance, when millions of rows are joined to return a small result in seconds, DBAs attribute issues to poorly constructed queries, while developers challenge the relational database.

A document database can enhance data modeling and query performance, especially when multi-key indexes optimize Equality, Sort, and Range. MongoDB support this easily, as its indexes are designed for documents. PostgreSQL can store documents in JSONB but indexes on arrays require GIN indexes and they do not maintain sort order or efficiently handle range queries.