Optimizing Slow Queries on a Million-Row Table

0) Problem Restatement

JPMorgan asked: queries that filter a table of millions of rows by user and by region are slow. Design an evidence-driven optimization plan. Compare indexes, partitioning, sharding and caching, and say how you'd measure the improvement.

Example table: transactions(id, user_id, region, amount, status, created_at, ...), with 5M rows.

Slow queries:

SELECT * FROM transactions WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
SELECT region, SUM(amount) FROM transactions WHERE region = 'APAC' AND created_at >= now() - interval '7 days' GROUP BY region;

1) Step 1: Measure, Don't Guess

• Find the slowest and most frequent queries (the slow query log, pg_stat_statements).
• Run EXPLAIN ANALYZE on each. Look for a Seq Scan (reading the whole table), big row estimates vs actual counts, sorts spilling to disk, and nested loops over many rows.
• Record the baseline: p50/p99 latency and rows read.

2) Step 2: Indexes (usually the biggest win)

• Query 1 filters by user_id and sorts by created_at → composite index (user_id, created_at DESC). The DB jumps to user 42's rows, already in time order, and stops after 20. From scanning 5M rows to reading ~20.
• Query 2 filters by region and a time range → index (region, created_at). Add INCLUDE (amount) to make it covering (answered from the index alone, without touching the table).
• Column order matters: equality columns first (user_id, region), then range or sort columns (created_at).
• Avoid functions on indexed columns in WHERE (WHERE DATE(created_at) = ... can't use the index, so rewrite it as a range).
• Select only the needed columns instead of SELECT *.
• Cost of indexes: every insert or update must also update each index. Add only the ones that serve real queries, and remove unused ones.

flowchart LR
    M["Measure: slow log + EXPLAIN ANALYZE"] --> I["Add targeted composite / covering indexes"]
    I --> R["Rewrite queries - sargable filters, fewer columns"]
    R --> P["Partition by time if the table keeps growing"]
    P --> C["Cache or pre-aggregate hot reports"]
    C --> S["Read replicas / sharding only if still needed"]

3) Step 3: Beyond Indexes (only if needed)

• Partitioning (e.g., monthly partitions by created_at): time-range queries skip old partitions (partition pruning), and deleting old data is instant (drop the partition). Useful when the table grows to hundreds of millions of rows.
• Pre-aggregation: for the regional report, keep a daily_region_totals table updated incrementally, so the report reads a few hundred rows.
• Caching: cache results of frequent identical queries (short TTL) in Redis.
• Read replicas: move heavy reporting reads off the primary.
• Sharding (splitting data across database servers): only when a single server truly can't handle the data or writes. It adds a lot of complexity. A million-row table almost never needs it.

4) Step 4: Verify

• Re-run EXPLAIN ANALYZE: expect an Index Scan / Index Only Scan, and far fewer rows read.
• Compare p50/p99 before and after under realistic load, and watch write latency (new indexes cost something).
• Roll out index creation without locking the table (CREATE INDEX CONCURRENTLY in PostgreSQL).

5) Wrap-Up

Start with evidence: the slow query log, EXPLAIN ANALYZE and baselines. Fix most problems with composite indexes that match the filters and sort order (equality columns first, covering where useful), and with query rewrites that keep filters index-friendly. Add partitioning, pre-aggregated tables, caching or read replicas only as data and load grow, keep sharding as a last resort, and verify every change with plans and latency numbers.