PostgreSQL: Performance implementing Multiple JOINs in a Complex Query
I'm reviewing some code and I'm working with important performance optimization with a complex query that involves multiple JOINs in PostgreSQL 14... The query is designed to retrieve user activity data from several tables, but it's taking an unusually long time to execute, even with just a few hundred records in the tables. Here's the SQL query I'm using: ```sql SELECT u.id, u.username, COUNT(a.id) AS activity_count, MAX(a.timestamp) AS last_activity FROM users u LEFT JOIN activities a ON u.id = a.user_id LEFT JOIN profiles p ON u.id = p.user_id LEFT JOIN subscriptions s ON u.id = s.user_id WHERE p.status = 'active' GROUP BY u.id, u.username; ``` Despite indexing the `user_id` columns in the `activities`, `profiles`, and `subscriptions` tables, the query still runs slow, often taking several seconds. I've tried using `EXPLAIN ANALYZE`, and it shows that the query planner is using nested loops for the joins, which seems inefficient for this use case. I've also checked for potential deadlocks or locks, but nothing seems off there. I wonder if I should consider breaking this query into smaller parts or maybe there's a more optimal way to structure it? Any advice or best practices for improving the performance of this query would be greatly appreciated. My team is using Sql for this REST API. Thanks for any help you can provide! I appreciate any insights!