PostgreSQL query performance issue with complex join and large dataset
I'm trying to implement Hey everyone, I'm running into an issue that's driving me crazy. I've spent hours debugging this and Can someone help me understand I've searched everywhere and can't find a clear answer. I'm experiencing significant performance issues with a query that involves multiple joins across large tables in PostgreSQL 13. I have three tables: `orders`, `customers`, and `products`. The `orders` table contains around 1 million records, while the `customers` and `products` tables have about 500,000 and 200,000 records respectively. The query I'm running is intended to fetch order details along with customer and product information based on specific criteria. Here’s the query I’m using: ```sql SELECT o.order_id, o.order_date, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id WHERE o.order_date > '2023-01-01' AND c.country = 'USA' ORDER BY o.order_date DESC; ``` While this works fine for small datasets, it takes over 30 seconds to execute with the larger dataset, which is unacceptable for our application. I've experimented with adding indexes on `orders.order_date`, `customers.country`, and foreign keys, but I'm still seeing slow performance. To improve the situation, I created an index on `order_date` using: ```sql CREATE INDEX idx_order_date ON orders(order_date); ``` However, the execution time hasn't improved significantly. I noticed in the `EXPLAIN` output that the query planner is performing a sequential scan on the `orders` table instead of an index scan. Is there a better way to structure this query or perhaps a different indexing strategy that could help speed it up? I also considered breaking it up into smaller subqueries, but that feels less efficient. Any insights would be greatly appreciated! My development environment is Linux. Thanks in advance! I'm on Debian using the latest version of Sql. Am I missing something obvious? I'm working on a desktop app that needs to handle this. Could someone point me to the right documentation? I'm developing on CentOS with Sql. I appreciate any insights!