INNER JOIN vs WHERE EXISTS: Performance Comparison

Overview

When filtering rows based on another table, two common approaches are using INNER JOIN and WHERE EXISTS. In this scenario, we have two tables: orders (1M rows) and customers (100K rows), and we want to retrieve all orders from customers in a specific country ('USA').

Queries

We have two queries that return the same results:

1-- Query 1: INNER JOIN
2SELECT o.*
3FROM orders o
4INNER JOIN customers c ON o.customer_id = c.id
5WHERE c.country = 'USA';
6
7-- Query 2: WHERE EXISTS
8SELECT o.*
9FROM orders o
10WHERE EXISTS (
11    SELECT 1 FROM customers c
12    WHERE c.id = o.customer_id AND c.country = 'USA'
13);

1-- Query 1: INNER JOIN
2SELECT o.*
3FROM orders o
4INNER JOIN customers c ON o.customer_id = c.id
5WHERE c.country = 'USA';
6
7-- Query 2: WHERE EXISTS
8SELECT o.*
9FROM orders o
10WHERE EXISTS (
11    SELECT 1 FROM customers c
12    WHERE c.id = o.customer_id AND c.country = 'USA'
13);

Performance Comparison

In PostgreSQL 15, the performance of these two queries is generally similar, and the optimizer can often transform one into the other. However, there are some subtle differences:

• INNER JOIN can be more efficient when the joined table (customers) is smaller, as it allows for more efficient indexing and caching.
• WHERE EXISTS can be more efficient when the subquery is highly selective, as it allows the optimizer to short-circuit the evaluation of the subquery as soon as it finds a matching row.

Indexing

To improve performance, ensure that the following indexes are created:

1CREATE INDEX idx_orders_customer_id ON orders (customer_id);
2CREATE INDEX idx_customers_id_country ON customers (id, country);

1CREATE INDEX idx_orders_customer_id ON orders (customer_id);
2CREATE INDEX idx_customers_id_country ON customers (id, country);

These indexes can significantly speed up the queries, especially the INNER JOIN query.

Conclusion

In general, the choice between INNER JOIN and WHERE EXISTS should be based on the specific use case and the characteristics of the data. However, if you're using PostgreSQL 15, the performance difference between the two queries is likely to be small, and you should focus on creating efficient indexes to support your queries.

Final Answer

Both queries can perform well, but INNER JOIN might be slightly faster when the joined table is smaller and properly indexed. Always test and analyze the execution plans of your specific queries to determine the best approach.