Database Indexing I Only Learned After 5 Years of Coding

I remember 2020. Production was busy. Users reported "the app is slow." I checked the dashboard - the order list query took 3 seconds. My first thought: "must be missing an index." I added an index on created_at. Deployed. Still 3 seconds. I added an index on user_id. Deployed. Still 3 seconds.

I was frustrated. I had indexes, why was it still slow? I read around, finally found the EXPLAIN ANALYZE tool. When I ran it, I realized: the indexes I created were never used by my query. Postgres kept using a sequential scan, iterating through 200,000 rows one by one.

That was the moment I realized: indexing isn't just "add a CREATE INDEX." Indexing is a science. And for 5 years of coding, I never learned the science. These are the things I only learned - that took my query from 3 seconds to 20ms.

---

What Is an Index (Briefly, While Walking Through)

Imagine a 1000-page book. You want to find the topic "containerization." Method 1: read page by page - that's sequential scan, O(n). Method 2: open the index at the back of the book, find "containerization page 247" - that's index scan, O(log n).

A database index = a B-tree (balanced tree) structure. Each node has a value + pointer to child + pointer to the physical row in the table. B-tree search: O(log n), much faster than O(n) scan.

But B-trees have rules. You can't just add an index and expect it to be used. The Postgres query planner is smart - it uses an index only if the cost is cheaper than a sequential scan. If your index doesn't match how your query is written, the planner will skip it.

That's what I didn't know for 5 years.

---

1. Index Isn't Used If You Put a Function on the Column

This was the first gotcha that made me realize.

-- Your query
SELECT * FROM users WHERE LOWER(email) = 'me@msncode.dev';

-- Your index (will never be used)
CREATE INDEX idx_users_email ON users(email);

Why? Because LOWER(email) is an expression. The B-tree index on email stores values me@msncode.dev, Me@MSNCode.dev, ME@MSNCode.DEV - all different. The query planner can't use the index because it has to compute lower(email) first, then compare.

Solution: create an expression index that matches the query.

-- Expression index - matches the query
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Now your query uses the index, fast

Or better: use a citext column (Postgres extension) for email, which is automatically case-insensitive without a function.

Lesson: the index must exactly match how your query accesses the column. If you use a function, create an expression index.

---

2. Type Mismatch Makes Indexes Useless

I once ran SELECT * FROM orders WHERE status_code = 200; on Postgres. Slow. The status_code index existed. EXPLAIN ANALYZE showed - sequential scan.

Turns out status_code in my schema was VARCHAR. The query writes 200 (integer). Postgres doesn't automatically cast integer to string to match the index. It casts status_code (string) to integer - and again, function on column = index not used.

-- Column VARCHAR, query integer = index not used
SELECT * FROM orders WHERE status_code = 200;

-- Solution: cast on the value side, not the column
SELECT * FROM orders WHERE status_code = '200';

This is a classic bug that's hard to debug because there's no error - the query runs, just slowly. Consistent data types = index gets used.

---

3. LIKE '%keyword%' Can't Use an Index

-- Index not used (wildcard in front)
SELECT * FROM articles WHERE body LIKE '%docker%';

-- Index used (prefix match)
SELECT * FROM articles WHERE body LIKE 'docker%';

B-tree indexes are searched from the left. If the wildcard is in front (%docker), Postgres doesn't know where to start in the B-tree. It has to scan everything.

Solutions for full-text search:
• Use the pg_trgm extension (trigram index) - supports LIKE '%keyword%'
• Or use Postgres full-text search (tsvector + GIN index)

-- pg_trgm - makes LIKE '%keyword%' use an index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_articles_body_trgm ON articles USING gin (body gin_trgm_ops);

-- Full-text search
CREATE INDEX idx_articles_body_fts ON articles USING gin (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('docker');

I use pg_trgm for this blog's search. Searching "docker" across 200 articles: 3ms. Before, using LIKE '%docker%': 400ms.

---

4. OR Clauses Can Make Indexes Useless

-- Might not use index
SELECT * FROM articles WHERE status = 'published' OR author_id = 1;

Postgres can use a bitmap index scan for OR, but often the planner decides a sequential scan is cheaper.

Solution: split into UNION ALL - each part uses its own index.

-- Each subquery uses its own index
SELECT * FROM articles WHERE status = 'published'
UNION
SELECT * FROM articles WHERE author_id = 1;

Or build a composite index if the OR shows up often. But UNION is often simpler and still fast.

---

5. Partial Indexes - Small Indexes That Are Super Fast

This is a trick I only learned and immediately fell in love with.

-- Index only for published articles
CREATE INDEX idx_articles_published ON articles(published_at)
WHERE status = 'published';

This index only stores rows where status = 'published'. If you have 1 million articles but only 100,000 are published, the index is 10x smaller → faster to search, less RAM.

For tables with a soft-delete pattern (deleted_at IS NULL), a partial index is a game-changer:

-- Index only active users
CREATE INDEX idx_users_active ON users(last_login)
WHERE deleted_at IS NULL;

A query WHERE deleted_at IS NULL AND last_login ? ✅

But it can't be used for:
• WHERE status = ? ❌ (no user_id)
• WHERE created_at > ? ❌ (no user_id + status)
• WHERE user_id = ? AND created_at > ? ⚠️ (can use for user_id, but created_at is skipped)

B-tree philosophy: you must start from the root (first column) and descend to children (second, third column). You can't skip the root and jump to a child.

Practical rule: highest selectivity column first (the one that filters the most rows), or the column most often queried alone first. Depends on your query patterns.

---

7. Covering Indexes - No Table Lookup Needed

A normal index: Postgres finds the row in the index, then looks up the physical row in the table to fetch other columns. 2 steps.

Covering index (Postgres 11+): you add extra columns using INCLUDE. Postgres can fetch all data from the index alone, no table lookup.

-- Index + INCLUDE extra columns
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at)
INCLUDE (status, total_amount);

Query SELECT user_id, created_at, status, total_amount FROM orders WHERE user_id = 1 → index-only scan, skips the table entirely. 2x faster.

But careful: INCLUDE columns aren't filtered, only fetched. You can't WHERE status = 'paid' using an INCLUDE column. For filtering, put it in the index key, not INCLUDE.

---

8. EXPLAIN ANALYZE: How to Read It

This is a skill I consider mandatory for senior devs. Without it, you're debugging indexes blind.

EXPLAIN ANALYZE SELECT * FROM articles WHERE status = 'published' ORDER BY created_at DESC LIMIT 10;

Output (simplified):

Limit (cost=0.42..1.23 rows=10 width=128) (actual time=0.015..0.038 rows=10 loops=1)
-> Index Scan using idx_articles_published_created on articles
(cost=0.42..4583.21 rows=58420 width=128)
(actual time=0.014..0.036 rows=10 loops=1)
Index Cond: (status = 'published')
Planning Time: 0.124 ms
Execution Time: 0.056 ms

What I look at:
• Index Scan = good, index is used. If Seq Scan = index not used, must fix.
• Index Cond = the condition used to scan the index. If your condition isn't here, the index doesn't match the query.
• actual time = real execution time. 0.056ms = fast. If seconds, problem.
• rows = rows scanned. If rows is large even though LIMIT 10, you're scanning too much.
• Planning Time = time Postgres spent deciding the plan. Usually fast, but dynamic queries (ORM-generated) can be slow here.

Without ANALYZE, you only see estimates. EXPLAIN ANALYZE actually runs the query and gives real numbers. Always use ANALYZE.

---

Quick Comparison: Scan Types

| Scan Type | Meaning | When It's Good |
|---|---|---|
| Seq Scan | Scans all rows | Small table (< 1000 rows), or query matches most rows |
| Index Scan | Scans index + table lookup | Selective query, index matches |
| Index Only Scan | Scans index only | Covering index, no table lookup needed |
| Bitmap Index Scan | Bitmap index + lookup | Multiple index OR, or low selectivity query |

Index Only Scan = fastest. Seq Scan on a large table = warning sign (unless you actually want to select most rows).

---

Common Misconceptions

"Adding an index = query is definitely fast." - Wrong. An index that doesn't match the query isn't used. You still get a sequential scan. Worse: an unused index makes writes slower (every INSERT/UPDATE must update the index too).

"Indexes are free." - No. Every index = slower writes + more storage. Unused indexes = burden. Audit indexes every 6 months, drop unused ones (Postgres has pg_stat_user_indexes to see index usage).

"Foreign key indexes are automatic." - No. Postgres doesn't automatically create an index for FKs. If you JOIN on an FK without an index, sequential scan. Create it manually:

CREATE INDEX idx_orders_user_id ON orders(user_id);

"A composite index can be used for any column." - No. Column order matters. You can't skip the first column and use only the second.

---

An Honest Closing

Indexing is a science I learned slowly. For the first 5 years I just "added an index when things were slow" without understanding why. When I finally read my first EXPLAIN ANALYZE, I realized 80% of the indexes I created were never used.

The philosophy I learned: an index isn't an optimization you slap on when things are slow. An index is part of the design. You design indexes based on your query patterns, not just blindly creating CREATE INDEX on columns that appear in WHERE clauses.

Skills I consider mandatory for senior devs:
1. Read EXPLAIN ANALYZE output
2. Understand B-trees, partial indexes, covering indexes
3. Know when an index isn't used (function, type mismatch, LIKE %, OR)
4. Audit index usage (pg_stat_user_indexes)
5. Understand the trade-off: faster reads vs slower writes

If you've never read EXPLAIN ANALYZE in your life, try it now. Open your database (the staging one, not real prod), run a query you suspect is slow, and read the output. The moment you see Seq Scan on a 1-million-row table and realize why your query is slow, that moment changes how you think about databases.

I'm still learning. Every time I find a slow query, there's a new gotcha. Databases are a deep science - 5 years only scratches the surface.

---

Sources

• PostgreSQL Documentation: Index Types
• PostgreSQL Documentation: Using EXPLAIN