Optimizing Slow Queries in REST API with PostgreSQL - Need Advice on Indexing Strategy
I've spent hours debugging this and I've tried everything I can think of but I tried several approaches but none seem to work. I'm working on a personal project and During development of a REST API that interacts with a PostgreSQL database, our team has identified performance bottlenecks in certain GET requests. Specifically, queries fetching user data with filters on multiple fields are taking longer than expected. We've tried basic indexing on the user table, but the improvements were minimal. Here’s a simplified version of one of the problematic queries: ```sql SELECT * FROM users WHERE age > 25 AND city = 'New York'; ``` Currently, our users table has the following structure: ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, city VARCHAR(100) ); ``` We initially added a single index on the `city` column: ```sql CREATE INDEX idx_city ON users(city); ``` Following this, we noticed a slight performance gain but not nearly enough. After further investigation, I learned about composite indexes. I went ahead and created a composite index on both `age` and `city`: ```sql CREATE INDEX idx_age_city ON users(age, city); ``` Despite this change, the query still isn’t performing satisfactorily. I’ve also considered revising the database configuration settings, like `work_mem` and `shared_buffers`, but I’m unsure of the optimal values for our setup. What strategies can we employ to effectively optimize these queries? Are there specific PostgreSQL settings or advanced indexing techniques that you recommend? Any insights from experiences with similar scenarios would be greatly appreciated. This is part of a larger web app I'm building. Any ideas what could be causing this? What's the correct way to implement this? For context: I'm using Sql on Windows 11. Could this be a known issue? Is there a better approach? I'm developing on Ubuntu 20.04 with Sql. Any ideas how to fix this?