Checking your 6: how to use tracing to pinpoint and fix missing indexes in your app

Your app’s humming along, and then boom—someone pulls up a massive table, and your database slows to a crawl. Your API hangs. Logs are screaming. Users are... not happy. Don’t panic. Way, way way too often, the culprit is missing indexes. Here’s how to find and fix them with a little help by tracing & monitoring your backend.
Beware: you might actually need caps lock for this.

what even is an index?

Think of an index as those tiny sticky notes the star student used in their textbook. Without an index, your query digs through every single row, like flipping through a giant unorganized binder. Add an index? Now it’s flipping straight to the right page.

Here’s the classic example. Fetching emails from a users table:

SELECT email FROM users WHERE email = '[e-mail]';

Without an index, that query scans the whole table. Add this:

CREATE INDEX idx_users_email ON users(email);

Boom. Your query just went from a cross-country road trip to first-class airfare.

finding slow queries (or: how I learned to stop guessing and love EXPLAIN)

Slow queries are like ghosts—you know they’re there, but where? Running SQL without checking EXPLAIN is like debugging without logs.

Example:

EXPLAIN SELECT email FROM users WHERE email = '[e-mail]';
------
Seq Scan on users  (cost=0.00..100.00 rows=1 width=255)
  Filter: (email = '[e-mail]')

If it says Seq Scan, congrats—you’ve got a full table scan. That means your database is reading every single row. It’s screaming for help.

the index fix

Let’s fix that nonsense. Add an index:

CREATE INDEX idx_users_email ON users(email);

Re-run EXPLAIN:

Index Scan using idx_users_email on users  (cost=0.29..8.44 rows=1 width=255)
  Index Cond: (email = '[e-mail]')

Now it should say Index Scan or Bitmap Heap Scan - the type of idx is up to the optimizer. Translation: Your query is finally taking the shortcut.

when to index (and when to chill)

Indexes are powerful, but they’re not free. Too many can backfire, like over-optimizing code until it’s unreadable.

Index these:

1: Columns you filter on a lot:

SELECT * FROM orders WHERE status = 'shipped';
CREATE INDEX idx_orders_status ON orders(status);

2: Columns used in JOINs:

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

3: Columns you sort by:

SELECT * FROM products ORDER BY price DESC;
CREATE INDEX idx_products_price ON products(price);

Skip these:

• Low-selectivity columns (e.g., a status column with only active/inactive).
• Frequently updated columns (indexes need to be rebuilt for every write op).

real-world debugging: how I found a missing index with sentry

Here’s a recent example: I was working on a habit tracker app that ran slow as molasses on the homepage. The top query was this:

SELECT name, status FROM habits_daily 
WHERE date = $1 AND user_id = $2;

I already had my backend traced with Sentry, so the culprit jumped out. This one query was 20x slower than the next slowest... and took 10x more time in total because it was being called so frequently (Shoutout to the Backend Insights tab for making it so obvious.)

I ran EXPLAIN:

EXPLAIN SELECT name, status FROM habits_daily 
WHERE date = '2024-12-01' AND user_id = '123';

Sure enough, it was doing a Seq Scan. No index. No surprise.

So, I added an index...

CREATE INDEX idx_habits_date_user ON habits_daily(date, user_id);

...and re-checked:

EXPLAIN SELECT name, status FROM habits_daily 
WHERE date = '2024-12-01' AND user_id = '123';
------
**Bitmap Heap Scan on habits_daily**  (**cost=4.35..31.52** rows=1 width=143)

Boom. Bitmap Heap Scan. Looks like it's running right, at least for now.
The result? Query cost dropped by 1000x. My homepage stopped lagging. And yeah, seeing it all happen in Sentry made it ridiculously easy to catch and confirm the fix.

the tl;dr

• Use EXPLAIN to find slow queries.
• Add indexes for high-impact filters, joins, and sort fields.
• Keep an eye on performance monitoring tools to zero in on bottlenecks fast. Tools like Sentry help you spot these issues before your users do. Seriously, if you’re not using something like that, you’re making life harder than it needs to be. Now go optimize your queries—you’ll thank yourself later.