Abu Hanif
← Back to Blog
· 2 min read

Why Your PostgreSQL Queries Are Slow (And How to Fix Them)

Database

An in-depth look at debugging a 3-second API endpoint, fixing an N+1 query, and implementing the correct PostgreSQL index.

I spent the better part of last week debugging an API endpoint that was taking over 3 seconds to respond. The culprit? A missing index on a heavily queried foreign key, combined with an N+1 query issue in Django.

Here's a breakdown of what I found, how I diagnosed it, and the exact steps to prevent this in your applications.

The Symptoms

The endpoint was a standard GET /api/v1/orders/ that returned a list of user orders, along with the items in each order. In local development with 10 orders, it was instantaneous. In production with 500,000 orders? A completely different story.

I ran EXPLAIN ANALYZE on the underlying query:

EXPLAIN ANALYZE 
SELECT * FROM orders_orderitem 
WHERE order_id IN (SELECT id FROM orders_order WHERE user_id = 4521);

The output showed a Sequential Scan on orders_orderitem. Postgres was reading every single row in a 5-million row table.

Tip: Always run EXPLAIN ANALYZE on slow queries. EXPLAIN just gives you the query plan, but adding ANALYZE actually runs the query and gives you the real execution time.

The Solution

1. Add the Database Index

In Django, foreign keys get indexes automatically, but if you're writing raw SQL or managing migrations manually, it's easy to miss. I explicitly verified the index in Django:

class OrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, db_index=True)
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    quantity = models.IntegerField()

2. Fix the N+1 Query in Django

Even with the index, Django was still executing a separate query for every single order to fetch the items.

The fix is using prefetch_related:

# Before (N+1 Problem)
orders = Order.objects.filter(user=request.user)
data = [{
    'id': order.id,
    'items': [item.product.name for item in order.orderitem_set.all()] 
} for order in orders]

# After (Optimized)
orders = Order.objects.filter(user=request.user).prefetch_related('orderitem_set__product')
data = [{
    'id': order.id,
    'items': [item.product.name for item in order.orderitem_set.all()] 
} for order in orders]

The Results

By adding the index and fixing the ORM query, the response time dropped from 3.2 seconds to 45 milliseconds.

Takeaways

  • Always load test your endpoints with production-like data volumes.
  • Use select_related for foreign keys (1-to-1 or many-to-1).
  • Use prefetch_related for reverse foreign keys and many-to-many relationships.
← All Articles