I'm dealing with a frustrating performance issue on one of my Django API endpoints that connects to AWS RDS PostgreSQL. The endpoint takes a ridiculous 8-11 seconds to respond when accessed without any query parameters. If I add a specific query parameter like `type=sale`, it actually gets slower, but if I use other types, like `type=expense`, it runs in about 100ms. I'm using `.select_related()` on various accounts and parties, `.prefetch_related()` for related images, `.annotate()` for some conditional values and a window function, and finally `.distinct()` to avoid duplicates. This problem only seems to happen on AWS RDS while it works perfectly fine on both localhost and EC2-hosted versions of Postgres. I'm really puzzled and would love any insights!
2 Answers
It sounds like a few things might be off. Have you checked the query plan? Understanding how the query executes on RDS could reveal potential bottlenecks. Also, what indexes do you have set up? The data size might vary across your instances, and using the same instance size everywhere is crucial. Make sure you’re not hitting limits with your RDS instance.
Your intuition could be spot on about missing indices. Just because it works locally doesn’t mean it will run smoothly in production. Try running your queries locally against a dataset that mirrors your production data to see how it really performs.
Good point! With ORM, adding `.distinct()` at the end shouldn't be a problem, but it can slow things down when only a single row is returned. When there are multiple rows, it seems to work better in RDS for some reason. I'm starting to think it might be an indexing issue.