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 (here, here, or here). However, user perception of slowness persists for some queries, making it crucial to listen to developers and understand why they feel joins are slow.
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. Dynamic bitmap scans can combine multiple indexes but at the price of more work and less possibilities.
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 WHERE conditions and ORDER BY limits 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 model can enhance query simplicity and performance, especially when multi-key indexes optimize Equality, Sort, and Range. MongoDB support this easily, as its indexes are designed for nested 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 like the regular indexes available only for single-key fields.
Top comments (7)
I'm a SQL so more of a question than a comment. In the example given querying the product data in doc is fast because all the relevant data is embedded. What if you wanted products of a certain type? Now you have to embed the type as well or join to the products/product type document to validate the additional information. In general documents are great where the queries are well understood in advance and relate to the document root. RDBMS is better when the queries are ambiguous.
Yes, it makes sense to embed more info from products if needed by queries (see extended reference pattern). But it's often also a business requirement. In real life, the order will include lots of information from the product because once you have printed and order (or sent by e-mail) you need to keep the information from this point in time. With a fully normalized model, renaming or re-typing a product later would make it inconsistent between what the customer sees on screen (query with join) and what he has in email or printed order.
Yes, I agree 100% with that. That's the main decision point. Don't go to documents if you didn't identify your domain access patterns. Relational databases have been invented for those central monolithic databases where applications come and go, before domain driven design and (micro-)services
The post should continue with PostgreSQL's JSON approach, otherwise it's like comparing apples to oranges.
I would argue that, JSONB capacities increase with every release of PostgreSQL. This example can be re-written with JSONB and would work without any major issues.
Later populate the tables,
Let's normalize our table + create indices
Let's take a look at the results,
If you expand your article to include the same approach MongoDB follows, only then it would be fair. There is still some advantage MongoDB offers but it is not as huge compared to the JSONB approach.
It was about document vs relational databases, and PostgreSQL and MongoDB were just examples. It's not a marketing comparative to show one database better than the other. I love many databases, MongoDB, Oracle and PostgreSQL included, and I'm more concerned about how they are used efficiently.
Yes, it makes sense to see if documents in PostgreSQL can be indexed in the same way as MongoDB does. But I don't think it is possible, which is why I included this sentence instead of an example. JSONB is great to add schema-on-read content to a relational table, but I don't think its indexing capabilities can make it a document database replacement yet. A good design on PostgreSQL is a mix of relational and documents.
You created two indexes: one GIN for the products in the array and one regular for sorting rows by date. But your plan uses only one. I think it can use both (or one compound with the btree_gin extension), but this will involve a bitmap scan and will not serve the need to get rows sorted for the ORDER BY LIMIT, which is the most selective filtering here. That's why your plan didn't use the GIN index and filtered after the index scan, reading more rows than necessary (visible with 'Rows Removed by Filter').
If you find a way to have the same indexing efficiency as MongoDB's multi-key index, for equality and sort filtering, please share it. I don't think it is possible in current versions of PostgreSQL.
OK I see your point. PostgreSQL's index can't get as efficient as MongoDB's (yet) Some extensions like
btree_ginexist but it doesn't help much in this case.I just wanted to give an example on the JSONB column, I think it is important to know this feature exists in a modern relational database.
That's why starting with PostgreSQL + JSONB makes it much easier. You have flexibility of choosing both. In my mind, choosing a NoSQL option is mostly feasible when you are performance or scalability concerned.
Hi @dogacel, I followed your idea and published the PostgreSQL JSON indexing possibilities:
PostgreSQL JSONB Indexing Limitations with B-Tree and GIN
Franck Pachot ・ Apr 15
That looks amazing, thank you for covering this topic. IMHO it feels much more complete now.