Optimizing complex queries with multiple joins in PostgreSQL - performance issues
Could someone explain Hey everyone, I'm running into an issue that's driving me crazy. I'm confused about After trying multiple solutions online, I still can't figure this out... I'm experiencing significant performance issues with a query that involves multiple joins in PostgreSQL 13. I have three tables: `orders`, `customers`, and `products`. The `orders` table contains foreign keys referencing both `customers` and `products`. The query I'm using looks like this: ```sql SELECT o.id, c.name, p.title, SUM(o.quantity) AS total_quantity FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id WHERE o.order_date >= '2023-01-01' GROUP BY o.id, c.name, p.title ORDER BY total_quantity DESC; ``` I expected this query to return quickly, but it often takes over 10 seconds to complete, especially as the `orders` table grows. I've already created indexes on `customer_id` and `product_id`, but the performance hasn't improved significantly. Additionally, I'm using the `EXPLAIN ANALYZE` command to inspect the query plan, and I see that it’s performing a lot of sequential scans which seem to be the bottleneck. Here’s the relevant part of the output: ``` Seq Scan on orders o (cost=0.00..1234.56 rows=10000 width=64) (actual time=0.123..12134.567 rows=5000 loops=1) Filter: (order_date >= '2023-01-01'::date) ``` I tried adding a composite index on `(order_date, customer_id, product_id)` but that didn’t help either. I’m wondering if I’m missing something in terms of normalization or indexing strategy, or if there is a more efficient way to structure this query. Any suggestions on how to optimize this, or techniques to analyze and improve the performance of such queries? Thanks! What's the best practice here? I'm working in a Linux environment. What am I doing wrong? For context: I'm using Sql on Windows 10. Any help would be greatly appreciated! I'd be grateful for any help.