I Added 20 Indexes to "Fix" Slow Queries — My Database Got 3x Slower

Six months ago, I inherited a PostgreSQL database that was choking on production traffic. API response times hit 8 seconds. Users were timing out. The ops team was getting paged at 2 AM.

So I did what any "experienced" developer would do: I added indexes. Lots of them.

Twenty indexes across twelve tables. Problem solved, right?

Wrong. The database got slower. Write operations crawled. Disk usage spiked. And the queries I was trying to optimize? They were still slow.

Here's what I learned the hard way about index tuning — and the process I use now that actually works.

The Mistake Everyone Makes

The biggest misconception about indexes is this: more indexes = faster queries.

PostgreSQL has to maintain every index on every write. Add an index, and every INSERT, UPDATE, and DELETE gets heavier. With 20 extra indexes, our write-heavy analytics table was spending more time updating indexes than storing data.

But the real killer was something I didn't expect: index bloat.

What Actually Went Wrong

1. I Indexed Low-Cardinality Columns

I put an index on a status column with only 4 possible values: pending, active, suspended, deleted.

PostgreSQL's query planner looked at that index, saw that each value matched ~25% of rows, and decided a full table scan was cheaper. The index was dead weight — costing disk space and write performance, providing zero read benefit.

2. I Created Redundant Indexes

I had:

• CREATE INDEX idx_user_email ON users(email)
• CREATE INDEX idx_user_email_name ON users(email, name)

The second index already covers queries on email alone. The first one was pure redundancy. PostgreSQL was maintaining two indexes for essentially the same lookup.

3. I Ignored Partial Indexes

Our orders table had millions of rows, but 90% were completed or cancelled. The slow queries were all looking for status = 'pending'. A partial index like:

CREATE INDEX idx_orders_pending 
ON orders(created_at, customer_id) 
WHERE status = 'pending';

This tiny index (10% of the table) outperformed my full-table indexes by 5x.

The Fix: A Methodical Index Audit

Here's the process I followed to undo the damage and actually optimize:

Step 1: Find Unused Indexes

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

This revealed 14 indexes that had never been used since the last stats reset. I dropped them immediately.

Step 2: Find the Real Slow Queries

Instead of guessing, I used pg_stat_statements:

SELECT 
  query,
  calls,
  mean_exec_time,
  total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This showed me which queries were actually burning CPU time. Not the ones I assumed were slow — the ones that actually were.

Step 3: Use EXPLAIN ANALYZE

For every slow query, I ran EXPLAIN ANALYZE to see the actual execution plan. Not EXPLAIN — EXPLAIN ANALYZE. The difference is that EXPLAIN ANALYZE actually runs the query and shows real timing data.

What I found: PostgreSQL was doing sequential scans on tables where I had indexes, because my query conditions didn't match the index column order.

Step 4: Build Right-Sized Indexes

The three indexes that actually made a difference:

-- Composite index matching the actual WHERE + ORDER BY pattern
CREATE INDEX idx_analytics_date_type 
ON analytics(event_date, event_type) 
WHERE event_date > '2026-01-01';

-- Covering index that includes all needed columns (no table lookup)
CREATE INDEX idx_users_lookup 
ON users(email) 
INCLUDE (name, created_at);

-- Expression index for a common pattern
CREATE INDEX idx_orders_lower_email 
ON orders(LOWER(customer_email));

The Results

Metric	Before Audit	After Audit
Total indexes	47	18
Avg query time	3.2s	0.4s
Write latency	180ms	25ms
Index disk usage	12.4 GB	2.1 GB
Index cache hit rate	67%	94%

The Rule I Follow Now

Never add an index without running EXPLAIN ANALYZE first.

Every index should have a specific query it's designed to accelerate. If you can't point to the query and show the before/after execution plan, don't create the index.

Indexes are not a "just in case" thing. They're a surgical tool. Use them like one.

---

Have you ever made your database slower by trying to optimize it? What was your wake-up call?