sql Questions
2 months ago
SQL: optimizing a slow JOIN query with millions of records
I have a query that's taking 30+ seconds on a PostgreSQL database:
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as revenue
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY revenue DESC;Tables: 500K users, 8M orders. I've added indexes on user_id and created_at but it's still slow. What am I missing?
Y
Yuki Tanaka
asked 2 months ago
1
Answers
23
Upvotes
7900
Views