I bounce between data pipelines, API fires, and new features all week, and there’s this one thing that keeps biting us. Slow pages. And 8 times out of 10 it’s the same root cause: we forgot the right index.
We had this analytics dashboard—we tuned the React, cached the API, CDN was spotless. Still slow. The query behind it? joining big tables and scanning like theres no tomorrow. No index on the join keys. Oops.
A quick demo to prove I’m not just ranting
I spun up a tiny test on my dev box: made an orders table, loaded 100k rows, timed a few queries. First with no indexes, then with some obvious ones.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TIMESTAMP NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL,
country VARCHAR(100)
);
Before indexes (avg):
Customer lookup: 6.11 ms
Status filter: 8.47 ms
Date range: 6.73 ms
After indexes:
Customer lookup: 0.88 ms (~7x faster)
Status filter: 2.41 ms (~3.5x)
Date range: 1.48 ms (~4.5x)
Note: these are from my laptop which is also running Docker, two IDEs, Slack, and that Electron app we dont talk about… so, not a lab.
The classic slow page (you’ve seen this movie)
Admin page loads in 10 seconds, everyones pointing fingers. Frontend swears it’s fine, backend says “works on my machine”. The DB? doing full table scans through millions of rows because the where clause is on status and order_date and, yeah, neither is indexed.
Usual suspects
Foreign keys without matching indexes on the child table
Date columns everybody filters by (no index)
Status fields in every WHERE (also no index)
The tiny bit of code that tells the truth
Here’s how I timed the “customer orders” lookup in Go:
func testCustomerIDQuery(db *sql.DB, description string) {
var total time.Duration
for i := 0; i < numQueries; i++ {
id := rand.Intn(100000) + 1
start := time.Now()
rows, _ := db.Query("
SELECT id, customer_id, order_date, total_amount, status, country
FROM orders WHERE customer_id = $1
LIMIT 10", id)
if rows != nil { rows.Close() }
total += time.Since(start)
}
fmt.Printf("%s avg: %v\n", description, total/time.Duration(numQueries))
}
And the “magic” is not magic, it’s just this:
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_total_amount ON orders(total_amount);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_country ON orders(country);
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);``
Add those, re‑run the exact same queries, and your 10‑second page quietly becomes sub‑second. It’s almost embarrassing how often that’s the fix.
Index size reality check
People ask “wont indexes be huge?”. From the same test (100k rows):
idx_customer_date: 3.1 MB
orders_pkey: 2.2 MB
idx_order_date: 2.2 MB
idx_total_amount: 2.2 MB
idx_customer_id: 1.9 MB
idx_country: 712 KB
idx_status: 688 KB
Call it ~12 MB total. For the speedup you get, thats cheap.
A couple gotchas (learned the hard way)
Composite order matters. (customer_id, order_date) helps WHERE customer_id = ? ORDER BY order_date DESC LIMIT 10. Flip it and you’ll be sad.
Check the plan. EXPLAIN (ANALYZE, BUFFERS)—you want Index Scan / Index Only Scan, not Seq Scan.
Not every column deserves an index. Super low selectivity (like a boolean) usually wont help with a plain b‑tree.
Writes pay the bill. Indexes speed reads, but inserts/updates get a bit slower—so pick the ones you actually use.
The boring checklist that works
Before you reach for sharding, a rewrite, or a shiny new DB:
Profile the slow endpoint.
EXPLAIN (ANALYZE, BUFFERS) the worst query.
If it’s scanning a big table, add the smallest useful index.
Re‑test. Ship. Sleep.
It’s not flashy. It won’t wow anyone at a meetup. But it’ll make your app feel fast, which is what users care about anyway.
Top comments (2)
Thanks for the detailed walkthrough. Seeing real query times and index sizes puts things into perspective.
Anytime thanks for the response