How Database Indexes Actually Work — Part 2: Using Them in the Real World

…knowing how an index works is not the same as knowing when to use one. This part is about that second skill: the judgment.

Table of Contents

· Composite Indexes And The Rule That Trips Everyone · Covering Indexes: When The Table Is Not Even Touched · B-Tree Is Not The Only Index Type · Indexes Are Not Free ∘ They Slow Down Writes ∘ They Take Up Space ∘ The Balance To Strike · Finding And Maintaining Your Indexes · When The Database Ignores Your Index ∘ You Wrapped The Column In A Function ∘ Implicit Type Casting ∘ An OR Across Different Columns ∘ The Leading Wildcard In LIKE ∘ Low Selectivity ∘ Partial Indexes: Index Only The Rows That Matter · How To See What Your Database Is Actually Doing · Conclusion

In Part 1, we opened up the index and saw the engine inside: the B-Tree that lets a database find one row among millions in three or four hops, walk a date range by strolling along its leaves, and return sorted rows without ever running a sort.

Understanding the mechanism is satisfying. But in real systems the hard questions are different. Should this index cover one column or three? Why did the index I just built get ignored? Which of my indexes are quietly slowing every write and giving nothing back?

This part answers those. If you have not read Part 1 yet, it explains the B-Tree datastructure that everything here builds on. Now let us put it to work.

Composite Indexes And The Rule That Trips Everyone

You can build an index on more than one column. This is a composite index.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This is powerful, but there is one rule you must understand, because it quietly breaks people’s assumptions. I call it the “left to right” rule. The official name is the leftmost prefix rule.

Think of the composite index like sorting a list of names by surname first, then first name.

The list is perfectly sorted by surname. Within each surname, it is sorted by first name.

So this index helps queries in this order:

• Filter by user_id alone. Fast. This is the surname.
• Filter by user_id and status together. Fast. Surname, then status.

But this composite index does NOT efficiently help this:

SELECT * FROM orders WHERE status = 'shipped';

Why? Because the list is sorted by user_id first. Searching by status alone is like trying to find everyone with the first name "Musa" in a phonebook sorted by surname. The "Musa" entries are scattered all over the book. There is no shortcut.

So the order of columns in a composite index is not a small detail. Put the column you filter on most, or filter on exactly, first.

Covering Indexes: When The Table Is Not Even Touched

Here is an advanced move that feels like cheating.

Normally, the database uses the index to find a pointer, then jumps to the table to read the full row. That second jump to the table costs a disk read.

But what if the index already contains every column your query asked for?

CREATE INDEX idx_orders_cover ON orders (user_id, status, total_amount);

SELECT status, total_amount FROM orders WHERE user_id = 42;

Everything this query needs, user_id, status, and total_amount, already lives inside the index. So the database never touches the actual table. It answers the whole query from the index alone.

This is called an Index-Only Scan (or a covering index). It is one of the most effective ways to speed up a hot query path, because you cut out an entire round trip to the table.

B-Tree Is Not The Only Index Type

Everything in Part 1 was about the B-Tree, and for good reason: it is the default in PostgreSQL and the right choice for the vast majority of queries. But it is not the only tool in the box. When the B-Tree is the wrong shape for your data, PostgreSQL offers specialized types:

• Hash: tuned purely for equality (=) checks. In practice the B-Tree handles equality almost as well, so you rarely need this.
• GIN: for values that hold many elements inside one row, like JSONB documents, arrays, and full-text search. It indexes every element, not just the whole value.
• GiST: for geometric and spatial data, ranges, and nearest-neighbor searches. This is what powers PostGIS.
• BRIN: for very large, naturally-ordered tables like time-series logs. It stores tiny min/max summaries per block instead of every row, so it is a fraction of the size of a B-Tree.

You reach for these when the data or the query does not fit a plain sorted tree. Everything else in this article is about the B-Tree, because that is what you will use most of the time.

Indexes Are Not Free

By now, indexes sound like pure magic. So why not index every column and go home?

Because indexes have a real cost, and ignoring that cost creates a new class of slow.

They Slow Down Writes

Remember, an index is a sorted B-Tree that must stay balanced. Every time you INSERT, UPDATE, or DELETE, the database has to update the table AND every affected index.

If a table has 8 indexes, one insert becomes one table write plus 8 index updates. On a write-heavy table, over-indexing quietly turns your fast inserts into slow ones.

They Take Up Space

Each index is a copy of column data plus pointers. Index them all, and your database size can balloon past the size of the actual data. That is more disk, more memory pressure, and slower backups.

This is not hand-waving. On my 1.4 million row table, here is the size of each index next to the table itself.

The table data is about 423 MB. The indexes together add up to around 361 MB. The indexes are almost as heavy as the data they point to. One index on the long title column alone is 217 MB.

The Balance To Strike

Index the columns you actually filter, sort, and join on. Leave the rest alone. An index that no query uses is pure cost with zero benefit.

Finding And Maintaining Your Indexes

If an index no query uses is pure cost, the obvious next question is how to find those. PostgreSQL keeps a running count of how often each index is actually used, in pg_stat_user_indexes.

SELECT indexrelname AS index_name,
       idx_scan      AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY idx_scan ASC;

Any index sitting at idx_scan = 0 is dead weight: it slows down every write and takes up disk, while giving you nothing back. Before you drop one, let a full business cycle pass. Some indexes only earn their keep during a monthly report or a seasonal spike, and they will read as "unused" right up until that moment arrives.

When you are sure, drop it:

DROP INDEX CONCURRENTLY IF EXISTS idx_unused_thing;

That CONCURRENTLY keyword matters on a live table. A plain DROP INDEX, like a plain CREATE INDEX, takes a lock that blocks writes while it runs. CONCURRENTLY does the same work without that lock, so your application keeps serving traffic while it happens.

There is one more slow decay worth knowing. On tables with heavy update and delete traffic, indexes accumulate dead entries over time, a condition called bloat. A bloated index is larger and slower than it needs to be. You rebuild it to reclaim the space:

REINDEX INDEX CONCURRENTLY idx_products_title;

You will not reach for this often, but when an index has quietly ballooned in size and lookups start to feel sluggish, a REINDEX is usually the cure.

When The Database Ignores Your Index

This is the part that frustrates developers the most. You create a beautiful index, and the database refuses to use it. It goes back to a full scan.

It is usually not a bug. The database is often right. Here are the common reasons.

You Wrapped The Column In A Function

-- Index on email will NOT be used here
SELECT * FROM users WHERE LOWER(email) = 'johndoe@example.com';

The index stores email, not LOWER(email). Once you transform the column, the sorted order no longer matches, so the index is useless.

The fix is a function-based index (also called an expression index) that stores the transformed value instead of the raw one:

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Now the index holds the lowercased emails in sorted order, so the query matches it exactly and the seek works again. The rule to remember: the expression in your query must match the expression in the index, character for character.

Implicit Type Casting

-- id is an integer column, but we compare it against a string
SELECT * FROM products WHERE id = '42';

When the value you compare against does not match the column’s type, the database sometimes has to cast the column, row by row, to make the comparison. The moment it transforms the column, you are back in the function trap above: the sorted order no longer lines up, and the index is skipped. Pass the matching type (id = 42, not id = '42') and the seek works.

An OR Across Different Columns

-- One scan cannot serve both sides of this OR
SELECT * FROM products WHERE title = 'AirPods' OR category = 'Audio';

An index on title handles the left side. An index on category handles the right side. But a single scan cannot ride two different indexes to satisfy one OR, so the planner often gives up and scans the whole table. Split the query so each half seeks with its own index, then merge the results with UNION:

SELECT * FROM products WHERE title = 'AirPods'
UNION
SELECT * FROM products WHERE category = 'Audio';

The Leading Wildcard In LIKE

-- Cannot use the index
SELECT * FROM products WHERE name LIKE '%phone';

A B-Tree is sorted from the start of the value. Searching for something that starts with anything (%phone) gives the tree no starting point to seek to. This is where full-text search comes in, which is a topic for another day.

Low Selectivity

If a column has very few distinct values, an index barely helps.

Imagine a gender column or an is_active boolean where 90% of rows are true. If your query returns most of the table anyway, the database decides that reading the table directly is cheaper than bouncing between the index and the table millions of times. And it is usually correct.

Indexes shine when they help you find a small slice of a large table.

I can show you the database making exactly this call. In my table, 1,130,503 products have reviews = 0. That is 79% of everything.

So I put an index on reviews, then asked for all the rows where reviews = 0.

The index is right there, ready to use. The database looked at it and said no thank you. It ran a Seq Scan anyway, because reading the whole table once is cheaper than jumping through the index a million times to fetch most of the table.

The database is not being lazy. It is being smart. An index is a tool for finding needles, not for hauling the whole haystack.

Partial Indexes: Index Only The Rows That Matter

That low-selectivity problem has an elegant fix. If 79% of the reviews values are 0 and nobody ever searches for those, why index them at all?

A partial index covers only the rows that match a condition you attach to it:

CREATE INDEX idx_products_reviews_active ON products (reviews)
WHERE reviews > 0;

This index ignores the 1.13 million dead rows completely and stores only the ~21% that people actually query. It is smaller on disk, cheaper to keep balanced on every write, and now the planner is happy to use it, because the rows it covers are the selective ones. Partial indexes fit the “one value dominates the column” shape perfectly, like WHERE status = 'pending' or WHERE deleted_at IS NULL.

The one rule: your query’s filter has to be compatible with the index’s WHERE clause. A search for reviews = 0 still cannot use this index, which is exactly what we want.

How To See What Your Database Is Actually Doing

You do not have to guess whether your index is being used. The database will tell you. Just ask it with EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM products WHERE serial_code = 'SPX-99A7-2231';

EXPLAIN shows the plan the database intends to use. EXPLAIN ANALYZE actually runs the query and shows the real timings.

The words you are looking for in the output:

• Seq Scan: The database is reading the whole table. If this is on a large table and you expected an index, something is wrong.
• Index Scan: The database is using your index to find rows, then fetching them from the table. Good.
• Index Only Scan: The database answered entirely from the index without touching the table. Best.

Two small habits make these plans easier to read and trust.

First, run the query twice and trust the second run. The first run can be slow simply because the data was cold on disk. The second run shows the true cost once the cache is warm.

Second, while you are learning, turn off parallel workers for your session:

SET max_parallel_workers_per_gather = 0;

This collapses the plan into a single, readable path instead of a tree of parallel workers, which is exactly how I captured the clean screenshots above.

When I fixed that slow serial code lookup from Part 1, EXPLAIN ANALYZE was how I confirmed it. Before the index, it screamed Seq Scan and a runtime in seconds. After the index, it showed Index Scan and a runtime in single-digit milliseconds. Same query, same data, completely different life.

Make EXPLAIN ANALYZE a habit. It turns performance tuning from guesswork into reading a clear report.

Conclusion

Let us bring it home.

A database without an index is a person searching for one word in a dictionary by scrolling through every page. A database with the right index is a person using the dictionary alphabets sequence, jumping straight to the letter.

The B-Tree is what makes that jump possible. It keeps your data sorted and balanced so the database can find one row among millions in a handful of hops.

But an index is a tool, not a blanket. It speeds up reads and slows down writes. It helps specific columns and specific query shapes, and it ignores you when you fight its sorted nature.

So the next time a query feels slow, do not reach for a bigger server first. Run EXPLAIN ANALYZE, look for that Seq Scan, and ask yourself one question: does this query have the index it deserves?

That single line turned a three-second scan into a few milliseconds. It might just save your next feature too.