What actually makes a join slow?
Almost always one of four things. And the first one is by far the most common.
There is no index on the join column. Without an index, the engine has to read every row of one table and look for a partner in the other. That is a nested loop over a million rows, and it is slow. With an index, the engine can jump straight to the partner.
The join multiplies the rows. If one customer has three hundred orders and you join to the lines inside each order, the intermediate result is large, and everything after it is slower because of it.
The join column has a function wrapped around it. The condition WHERE LOWER(a.name) = LOWER(b.name) cannot use an index, because the engine has to work out the value for every row before it can compare it.
The two columns have different types. If one side is a number and the other side is text, the engine has to convert every row, and it cannot use the index either.
-- These three are all slow, and each one has a fix.
--
-- No index on the join column:
-- CREATE INDEX idx_orders_customer ON orders (customer_id);
--
-- A function on the join column:
-- JOIN orders o ON o.customer_id = c.id (good)
-- JOIN orders o ON LOWER(o.customer_id) = LOWER(c.id) (slow)
--
-- Different types on the two sides:
-- JOIN orders o ON o.customer_id = c.id (both numbers)
-- JOIN orders o ON o.customer_id = CAST(c.id AS TEXT) (slow)
The next question in this topic shows how to find the problem from the plan.