Making Indexes Work For You: Composite Indexes and Query Plans (Part 2)

In Part 1, I covered what database indexes are, how B-trees work, and the surprise discovery that they can actually slow down your writes. Now I want to get into the practical side. As a Rails developer, I've used add_index in migrations plenty of times — it always felt like one of those "just sprinkle it on a column and queries get faster" things. But there's a whole world of details I had no idea about, and that's what this post is about.

Composite Indexes: A Whole New Concept For Me

Until I started digging into this, I had no idea composite indexes were even a thing. I always thought of indexes as "one column = one index." Turns out you can put multiple columns into a single index, and it changes how the database can use it in some pretty surprising ways.

A composite index is just an index on multiple columns. Say you frequently query users by both country and city:

SELECT * FROM users WHERE country = 'US' AND city = 'Austin';

You could create a composite index like this:

CREATE INDEX idx_users_country_city ON users(country, city);

Now here's the part that genuinely blew my mind: the order of columns matters, and a composite index can also act as an index for just the first column on its own. I had to read that twice. The index above works like a phone book — sorted first by country, then by city within each country.

This means a single composite index on (country, city) is useful for:

• Queries filtering on country AND city — uses both parts.
• Queries filtering on country alone — still works! It uses the first part of the index.

But it is not useful for:

• Queries filtering on city alone — it can't skip past the first column.

This is called the leftmost prefix rule, and it was completely new to me. The database can only use the index starting from the leftmost column. The phone book analogy finally made it click: you can look up everyone with last name "Smith," and you can also just look up all the Smiths in general — but you can't efficiently look up everyone named "John" across all last names. The order is baked in.

The practical takeaway: when designing composite indexes, think carefully about which column goes first — the leftmost column is the one that can do double duty as a standalone index. A good default is to lead with the more selective column (the one that narrows results the most), but if a column is queried on its own far more often than the other, that can tip the decision the other way. There isn't one universal rule; selectivity and query patterns both matter.

Wait, You Can Just Ask The Database What It's Doing?

This was another "I had no idea" moment. Apparently you don't have to guess whether your index is being used — you can literally just ask the database to explain what it's about to do. All those times I was second-guessing my queries, and the answer was right there the whole time.

-- Uses our composite index (country is the leftmost column):
EXPLAIN SELECT * FROM users WHERE country = 'US';

-- Does NOT use the index — city alone can't skip past country:
EXPLAIN SELECT * FROM users WHERE city = 'Austin';

The output tells you the query plan — how the database intends to execute your query. Run EXPLAIN on both versions above and you'll see the leftmost prefix rule in action: the first query uses the index, the second falls back to scanning the whole table. The key things I learned to look for:

• Seq Scan (or Full Table Scan) — the database is reading every row. On a large table this usually means your index isn't being used and it's worth investigating why. On a small table it's often the right call, so context matters.
• Index Scan — good. It's using an index to jump to the right rows.
• Index Only Scan — even better. It found everything it needs in the index itself without touching the table.

Running EXPLAIN on some of my own queries was eye-opening. A few of them weren't using the indexes I assumed they were — sometimes because I had the wrong columns indexed, sometimes because the query was structured in a way the optimizer just couldn't take advantage of. I felt like I had been driving with a blindfold on.

The Biggest Shift: When NOT To Index

Honestly, this is the section that flipped my whole mental model. Until I read about it, I had this assumption that indexes were always a good idea. More indexes = faster everything, right? Nope. Turns out there are real situations where adding an index actively hurts performance, and being able to spot them is what separates "I added an index" from "I added the right index."

Small tables — If your table has 100 rows, a full table scan is essentially instant. An index just adds overhead for no benefit.

Columns with low cardinality — A status column with only a few possible values like active, pending, and archived? The database might decide a full scan is faster anyway because the index doesn't narrow things down enough.

Write-heavy tables — If a table gets thousands of inserts per second and is rarely queried, all those indexes are slowing down every write for no real payoff.

Too many indexes — I've seen tables with an index on nearly every column. Each one adds write overhead and storage cost. Be intentional about what you index.

The Gotcha I Learned The Hard Way

There's one thing I learned from actual experience that I want to call out, because it caught me completely off guard: adding an index to a table that's already huge is not a casual operation.

I remember the first time I needed to add an index for a new report on a big table in production. In my dev environment with a few thousand rows, the migration took milliseconds. On production, with millions of rows? It took tens of minutes instead of milliseconds — and worse, by default, the database can lock the table while the index is being built, which means writes get blocked. On a busy app, that's bad news.

In Rails, the fix is to use the concurrently algorithm so the index can be built without locking the table:

class AddIndexToOrders < ActiveRecord::Migration[8.1]
  disable_ddl_transaction!

  def change
    add_index :orders, :customer_id, algorithm: :concurrently
  end
end

The disable_ddl_transaction! is required because PostgreSQL won't let you create an index concurrently inside a transaction. The trade-off is that concurrent index builds are slower overall and can occasionally fail — and when they fail, you're left with an INVALID index that you have to manually drop before you can retry. But they don't lock your table, which is usually what you want in production.

The lesson: indexes are easy to add when your table is small. Once it's big, adding one becomes a deployment event. Plan for it.

My Rules of Thumb (So Far)

After digging into all of this, here's the mental checklist I've started using:

1. Index columns you filter on a lot — if it shows up in a WHERE clause often, it probably needs an index. Emphasis on often — see rule 5.
2. Index columns you join on — foreign keys used in JOINs benefit a lot from indexes.
3. Check your composite index order — put the most selective column (the one that narrows results the most) first.
4. Use EXPLAIN — don't guess. Let the database tell you what it's doing.
5. Don't over-index — more isn't always better. Every index has a maintenance cost.

Wrapping Up

Coming into this, I already knew indexes made queries faster — that part was never a mystery. What I didn't realize was how much strategy there is behind them. Composite indexes, the leftmost prefix rule, EXPLAIN, knowing when NOT to index, and the production gotcha of adding indexes to big tables — every one of those was new territory for me, and they've changed how I think about database performance.

If you're a Rails dev like me and you've been sprinkling add_index on columns without thinking too hard about it, I'd really recommend running EXPLAIN on a few of your queries. You might be surprised by what you find.

Next up in this series, I'm planning to explore JOINs in depth — INNER, LEFT, RIGHT, FULL OUTER, and when to use each one. See you there.