Indexing for Real Workloads

Scenario

Example app: an order system with these hot queries:

1. WHERE customer_id = ? AND status = 'open' ORDER BY created_at DESC (customer dashboard)
2. WHERE status = 'pending' AND created_at < now() - interval '15 min' (abandoned-checkout job; 0.5% of rows are pending)
3. SELECT total FROM orders WHERE id = ? (very frequent)

What you see in production

Over the years the orders table collected 14 indexes, several on single columns. Reads are still slow, and checkout inserts became 4× slower than a year ago — each insert updates 14 indexes.

The naive code

One index per column "just in case":

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (status);
CREATE INDEX ON orders (created_at);
-- … 11 more

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT, db_index=True)
    status = models.CharField(max_length=20, db_index=True)
    created_at = models.DateTimeField(db_index=True)

Reproduce it

On the sample shop, compare a single-column index with a composite one that matches filter and sort, and a covering index that answers the query from the index alone:

CREATE INDEX idx_status ON orders (status);
EXPLAIN QUERY PLAN SELECT order_date FROM orders WHERE customer_id = 1 AND status = 'delivered' ORDER BY order_date;
CREATE INDEX idx_cust_status_date ON orders (customer_id, status, order_date);
EXPLAIN QUERY PLAN SELECT order_date FROM orders WHERE customer_id = 1 AND status = 'delivered' ORDER BY order_date;

id | parent | notused | detail
4 | 0 | 0 | SEARCH orders USING INDEX idx_status (status=?)
15 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY

id | parent | notused | detail
3 | 0 | 0 | SEARCH orders USING COVERING INDEX idx_cust_status_date (customer_id=? AND status=?)

Root cause

• A query typically uses one index per table access; three single-column indexes rarely combine as well as one composite index built for the query.
• Column order matters: equality filters first, then range or sort columns. (customer_id, status, created_at) serves query 1 completely; (created_at, customer_id) doesn't.
• Every index is written on every insert and on updates to indexed columns — 14 indexes means 15 writes per order, more WAL and bigger replicas lag.

The fix

Design indexes from the query list, then drop the rest:

-- 1: dashboard — equality, equality, sort
CREATE INDEX CONCURRENTLY orders_customer_status_created ON orders (customer_id, status, created_at DESC);
-- 2: tiny partial index for the background job (0.5% of rows)
CREATE INDEX CONCURRENTLY orders_pending_created ON orders (created_at) WHERE status = 'pending';
-- 3: covering lookups (index-only scan) — INCLUDE adds payload columns without ordering them
CREATE INDEX CONCURRENTLY orders_id_total ON orders (id) INCLUDE (total);
-- find unused indexes (since stats reset)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

ALTER TABLE orders ADD INDEX customer_status_created (customer_id, status, created_at);
-- no partial indexes: index the selective expression or use a separate queue table for pending orders
SELECT * FROM sys.schema_unused_indexes WHERE object_name = 'orders';

class Order(models.Model):
    class Meta:
        indexes = [
            models.Index(fields=["customer", "status", "-created_at"], name="orders_customer_status_created"),
            models.Index(fields=["created_at"], name="orders_pending_created", condition=Q(status="pending")),
        ]

-- V57__orders_indexes.sql (Flyway, Postgres): CONCURRENTLY can't run inside a transaction
-- flyway: executeInTransaction=false
CREATE INDEX CONCURRENTLY orders_customer_status_created ON orders (customer_id, status, created_at DESC);

Schema::table('orders', function (Blueprint $t) {
    $t->index(['customer_id', 'status', 'created_at'], 'orders_customer_status_created');
});
// Postgres partial index: DB::statement("CREATE INDEX CONCURRENTLY … WHERE status = 'pending'") in a non-transactional migration

Trade-offs

• Partial indexes are tiny and fast for skewed predicates, but only used when the query repeats the same WHERE condition.
• Covering indexes enable index-only scans but grow the index; Postgres index-only scans also depend on the visibility map (VACUUM).
• Low-cardinality columns (status with 5 values) are poor leading columns unless the query also filters something selective — or the value you search for is rare (partial index).
• Removing an index is cheap to undo but check replicas and rare monthly jobs before dropping "unused" ones.

Detect it in production

• pg_stat_user_indexes.idx_scan = 0 over a full business cycle → candidate for removal.
• Duplicate prefixes: (a) is redundant next to (a, b).
• Insert latency and WAL volume per table; replica lag growing with write volume.

Key points

• Index for queries, not for columns: equality first, then range/sort.
• Use partial indexes for rare values and covering indexes for hot lookups.
• Every index costs writes, memory and replication; remove unused and redundant ones.