What makes a subquery correlated, and why does that matter for performance?
A subquery is correlated when it refers to the row of the query that holds it. The inside query cannot be worked out on its own.
-- This one is correlated. The inside query uses c.id
-- from the outside row.
SELECT c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
So why does that matter for performance? Because a correlated subquery has to be run once for every row of the outer query. The engine cannot work out the answer once and reuse it, because the answer is different for every row.
That is the whole difference between the two kinds of subquery. An uncorrelated subquery gives the same answer every time, so the engine works it out once and uses it again and again. A correlated one gives a different answer for each row, so it has to be run again and again.
There are three things you can do about it. The first one is to let the planner rewrite it, because a good planner turns most of them into a join. The second one is to rewrite it yourself as a join, which is the same answer and usually much faster. The third one is to keep the correlated subquery but to make the inside query cheap, by giving it an index on the column that it filters on.
-- The index that makes the correlated subquery fast.
CREATE INDEX idx_orders_customer ON orders (customer_id);
The next question in this topic puts a correlated subquery to work.