Optimize a Slow Database Query

Performance Engineering · Part 7

A single slow query can drag down an entire page, and the fix is rarely "add more hardware" — it's usually a query plan doing something dumb that a small change makes smart. This walks through optimizing one real slow query end to end: reading its plan, adding the right index, restructuring the joins, and knowing when to reach for materialization or approximation.

A real query: 5 seconds. The target: under 100ms. Let's walk through it.

Step 1: The Slow Query (5 min)

SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 100;

Takes 5 seconds on a 10M-row orders table.

Step 2: EXPLAIN ANALYZE (5 min)

EXPLAIN ANALYZE [the query];

Reveals:

Limit  (cost=X)
  Sort
    HashAggregate
      Hash Left Join
        Seq Scan on users  (filters by created_at)
        Seq Scan on orders (filters by status)
          → 5M rows scanned!

The orders sequential scan is the killer.

Step 3: First Fix — Index on Filter (5 min)

CREATE INDEX CONCURRENTLY idx_orders_status_user 
ON orders(user_id, status);

Composite: user_id first (joins on it), status second (filtered).

Re-run:

EXPLAIN ANALYZE [query];

Index Scan using idx_orders_status_user

Down to 1.5 seconds. Better but not great.

Step 4: Look at Users Scan (5 min)

Users table seq scan is also there:

Seq Scan on users  (cost=...)
  Filter: created_at > ...

CREATE INDEX idx_users_created ON users(created_at);

Now uses the index. Few hundred users out of millions.

Down to 800ms.

Step 5: Move Filter Into Join (10 min)

Hmm — query has o.status = 'completed' in JOIN condition but it's a LEFT JOIN. Pre-filtering helps:

WITH active_orders AS (
    SELECT user_id, total FROM orders WHERE status = 'completed'
)
SELECT 
    u.id, u.name,
    COUNT(o.user_id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN active_orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
HAVING COUNT(o.user_id) > 0
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 100;

Postgres usually inlines this. But sometimes it changes the plan.

Step 6: Reconsider HAVING (10 min)

HAVING COUNT(o.id) > 0 + LEFT JOIN = effectively INNER JOIN. Make it explicit:

SELECT 
    u.id, u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
INNER JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
ORDER BY lifetime_value DESC
LIMIT 100;

INNER JOIN often optimizes better. Removed the unnecessary HAVING.

Step 7: Partial Index (5 min)

Since you only query status = 'completed':

CREATE INDEX idx_orders_completed
ON orders(user_id, total)
WHERE status = 'completed';

Smaller index. Just the rows that match. Includes total for the SUM.

Step 8: Materialize for Hot Path (10 min)

If this query runs constantly, materialize:

CREATE MATERIALIZED VIEW user_ltv AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

CREATE INDEX ON user_ltv(lifetime_value DESC);

REFRESH MATERIALIZED VIEW user_ltv;  -- periodically

Query then becomes:

SELECT u.id, u.name, ltv.*
FROM users u
JOIN user_ltv ltv ON ltv.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
ORDER BY ltv.lifetime_value DESC
LIMIT 100;

Millisecond response. But data is stale to refresh time.

Step 9: Approximation (5 min)

Sometimes exact isn't needed:

-- HyperLogLog for distinct counts
SELECT estimate_distinct(user_id) FROM events;

Approximations are much faster for stats. Trade exactness for speed.

Step 10: Measure End-to-End (5 min)

Before: 5 seconds
After indexing: 800ms
After rewrite: 200ms
After materialized view: 5ms

Each step deliberate. Each verified with EXPLAIN ANALYZE.

Pick the optimization level that fits your needs.

What You Just Did

You took a 5-second query to 5ms. EXPLAIN-driven; targeted fixes; verified at each step.

Common Failure Modes

Adding random indexes. Some help; some hurt writes.

Trusting query plan from yesterday. Statistics matter; ANALYZE after big data changes.

Premature materialization. Adds complexity; often unneeded.

Optimizing rarely-run query. Spend time on what matters.

No measurements. Don't know if it helped.

---

Continue the Performance Engineering path

• Previous — Part 6: Read Flame Graphs

• Next — Part 8: Reduce Memory Usage

Part of the Performance Engineering learning path.