PostgreSQL query optimization for multiple LEFT JOINs resulting in performance degradation
I'm learning this framework and I'm sure I'm missing something obvious here, but I'm experiencing important performance optimization with a PostgreSQL query that involves multiple `LEFT JOIN` operations. The query runs fine with a small dataset, but as the dataset grows, the execution time becomes unacceptably long. I'm attempting to retrieve a list of users along with their orders and the respective products for each order. Here’s a simplified version of my query: ```sql SELECT u.id, u.name, o.id AS order_id, p.id AS product_id FROM users u LEFT JOIN orders o ON u.id = o.user_id LEFT JOIN products p ON o.product_id = p.id; ``` With about 100,000 users, 300,000 orders, and 500,000 products, the execution time exceeds 30 seconds. I’ve tried adding indexes to the `user_id` and `product_id` columns, but it hasn’t helped much. Here are the indexes I created: ```sql CREATE INDEX idx_users_id ON users(id); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_product_id ON orders(product_id); CREATE INDEX idx_products_id ON products(id); ``` I also analyzed the query using `EXPLAIN ANALYZE` and it shows that the query planner is doing a sequential scan on the orders and products tables instead of using the indexes. I’ve considered normalizing my database structure further, but I’m worried about the additional complexity it might introduce. Is there a specific strategy I should be using for optimizing queries with multiple joins in PostgreSQL, or should I refactor the query itself? Any advice or examples would be greatly appreciated! I'm working on a web app that needs to handle this. Thanks in advance! For context: I'm using Sql on Linux. Thanks, I really appreciate it!