PostgreSQL: Index not used for JOIN operation in query, resulting in slow performance
I'm learning this framework and I've searched everywhere and can't find a clear answer....... I'm facing an issue where a JOIN operation in my PostgreSQL database is not utilizing the index I created, which is causing significantly slower query performance. I've optimized my tables for JOINs, yet the query planner doesn't seem to be picking up on the indexes. The tables involved are `users` and `orders`, with `user_id` in `orders` referencing `id` in `users`. Here's the relevant part of my schema: ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT, amount DECIMAL(10, 2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE INDEX idx_user_id ON orders(user_id); ``` I run a query to fetch the names of users along with their order amounts: ```sql SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100; ``` When I analyze this query using `EXPLAIN ANALYZE`, I find that it performs a sequential scan on the `orders` table instead of using the `idx_user_id` index. The resulting execution time is around 2 seconds on a dataset with 1 million rows, which is unacceptable for my application. I have tried running `VACUUM ANALYZE` on both tables, ensuring that statistics are up to date. I also experimented with `SET enable_seqscan TO off;` to force index usage, but the performance didn't improve significantly. Can anyone suggest why the index might not be used in this scenario and how I can resolve this issue? I'm using Sql 3.11 in this project. I'd really appreciate any guidance on this.