DISTINCT ON is the latest-row-per-group query
"The most recent order for each customer" does not need a window function or a self join.
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;The ORDER BY has to start with the DISTINCT ON columns. The row that sorts first in each group is the one you get. An index on (customer_id, created_at DESC) makes it fast.
postgresql