Chapter 19: Introduction to Index Design — How to Place Indexes That Speed Up Search, and the Pitfalls

What you'll learn in this chapter

• Explain the mechanism by which an index speeds up a search, in contrast to a full table scan
• Distinguish "indexes created automatically" from "indexes you create yourself"
• Decide which columns to index (WHERE / JOIN / ORDER BY) by looking at a query
• Design the column order of a composite index
• Confirm whether an index was used, with EXPLAIN
• Explain why over-indexing is harmful

Prerequisites

You should understand Chapter 10: Key Concepts and Chapter 13: Design in Practice. This chapter focuses narrowly on "how to place indexes at design time," not "the internal implementation of an index."

What Happens Without an Index?

Searching a table with no index using WHERE email = 'yamada@example.com' means the database reads every row from the top, in order, checking whether it matches the condition. This is called a full table scan (sequential scan).

For a 10-row table, this is instant, but a table with a million rows needs a million comparisons. The more users you have, the slower the search gets, which is the classic performance problem of "it was fast right after launch, but suddenly got slow six months later."

An index is index data that keeps a specific column's values sorted. Just like looking up "term → page number" in a book's back-of-book index, it lets you look up "value → row location" directly, so you don't need to read every row.

PostgreSQL's default index is a tree structure called a B-Tree. Since values are kept in sorted order, it works like flipping open a dictionary — repeatedly "opening to the middle and deciding whether to go to the first half or second half" until you reach the target value. The number of comparisons is on the order of log N relative to the row count N, so even with a million rows, you can pin down the target row in around 20 comparisons (the detailed internal implementation of the tree structure is out of scope for this guide — for design decisions, it's enough to remember the property that "being sorted makes it strong at equality search, range search, and ordering").

Creating an index (PostgreSQL)

CREATE INDEX idx_users_email ON users(email);

Indexes Created Automatically, and Indexes That Aren't

You don't create every index yourself. Some constraints create one automatically.

Target	Index	Basis
PRIMARY KEY	Created automatically	An index is needed to check uniqueness (PostgreSQL: CREATE TABLE)
A UNIQUE constraint	Created automatically	Same as above
FOREIGN KEY (the referencing side)	Not created	PostgreSQL doesn't index a foreign-key column (PostgreSQL: Constraints). MySQL (InnoDB) creates one automatically

The third row is easy to miss. A foreign-key column like orders.user_id gets used frequently in JOINs and filters, like "this user's list of orders," but PostgreSQL doesn't automatically index it. What's more, when you delete or update a row on the parent side (users), the database searches the child side for "does anything still reference this," so without an index on the foreign-key column, even deleting a parent row gets slow.

Index a foreign-key column yourself

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,                    -- automatically indexed
    user_id      INTEGER NOT NULL REFERENCES users(id),    -- not indexed automatically
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    ordered_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);        -- create it yourself

Which Columns to Index

"Just index every column, for now" is a mistake (see "The Harm of Over-Indexing" below for why). The starting point for deciding is the queries you actually issue — columns that appear in the following three places are candidates.

Where it appears in a query	Example	Why to index it
A WHERE filter condition	WHERE email = ? / WHERE ordered_at >= ?	Turns a full table scan into an index search
A JOIN condition (≈ a foreign key)	JOIN orders ON orders.user_id = users.id	Quickly locates the matching row on every join
An ORDER BY sort	ORDER BY created_at DESC LIMIT 10	Lets you just read from the front of a pre-sorted index, skipping a full sort

Conversely, there are columns where indexing has little effect even if they appear in a query: columns with an extremely small number of distinct values. For example, indexing is_active (just TRUE / FALSE) alone: if TRUE rows make up half the table, the index only narrows things down to half, so you still end up reading a huge number of rows. The database estimates this from its statistics, and if it judges "a full table scan is faster than using the index," it ignores the index.

A design-time rule of thumb

At the table-design stage, it's realistic to cover "primary key (automatic) + foreign keys (manual) + unique business keys (automatic)," and add anything else once your main queries are decided, by looking at the WHERE / ORDER BY clauses. Use the Design Template Collection's index checklist during review.

Composite Indexes — Column Order Is Everything

A single index that combines multiple columns is called a composite index.

A query that lists a user's order history, newest first

SELECT * FROM orders
WHERE  user_id = 42
ORDER BY ordered_at DESC
LIMIT 10;

The composite index best suited to this query

CREATE INDEX idx_orders_user_ordered ON orders(user_id, ordered_at);

A composite index has the same structure as a roster sorted by "last name, then first name." A roster is "sorted by first name within people who share the same last name," so it works for a last-name-only search or a last-name-plus-first-name search, but not for a first-name-only search (you'd have to flip through every page).

This is called the leftmost-prefix rule. For an index on (user_id, ordered_at):

Query	Can it use the index efficiently?
WHERE user_id = 42	✓ A search on just the leftmost column
WHERE user_id = 42 AND ordered_at >= '2026-01-01'	✓ A search that uses columns in order from the left
WHERE user_id = 42 ORDER BY ordered_at DESC	✓ Covers both filtering and sorting with a single index
WHERE ordered_at >= '2026-01-01'	✕ A search that skips the leftmost column (user_id) barely uses this index at all (scanning the whole thing is theoretically possible but an inefficient use the planner won't pick)

The practical rule of thumb for deciding the order is "put columns filtered by equality (=) first, and columns used for a range (>= / BETWEEN) or sorting after." Narrowing down to a single user by equality first means the rows within that are already ordered by ordered_at, so both the range and the sort can be handled directly by the index. Reversing it to (ordered_at, user_id) means reading every user's rows that fall within the date range before sorting them out by user_id, which hurts filtering efficiency.

Note that if you have (user_id, ordered_at), you don't need a separate index on user_id alone (it works as the leftmost prefix). A duplicate index only adds to "the harm of over-indexing," covered below.

Partial Indexes — Indexing a Small Subset With a Condition

An index created with a WHERE condition is called a partial index. It pairs well with a table that uses logical deletion (Chapter 13), letting you index only the "live" rows.

A partial index covering only undeleted rows

CREATE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;

Even as deleted rows pile up, the index stays small, and it directly speeds up the app's go-to query, WHERE deleted_at IS NULL AND email = ?. The partial unique index used in Chapter 14 (UNIQUE INDEX ... WHERE is_primary = TRUE) applies this same mechanism to a uniqueness constraint.

EXPLAIN — Checking Whether an Index Is Actually Used

Don't guess whether an index you created is actually being used — check with EXPLAIN. Just prefix it to a query, and the database's execution plan is displayed.

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

An example execution plan (key parts only)

Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1 width=52)
  Index Cond: (user_id = 42)

The first thing to read is the scan method on the first line.

Output	Meaning
Seq Scan	A full table scan. If this shows up for a filter on a large table, that's a signal to consider adding an index
Index Scan	The row is located via the index, then the row itself is read
Index Only Scan	The result can be returned from the index alone (no need to read the row itself — the fastest pattern)

Using EXPLAIN ANALYZE actually runs the query and shows the measured time too (attaching it to an UPDATE / DELETE really executes it, so wrap it in a transaction and ROLLBACK).

There are three typical reasons an index exists but you still get a Seq Scan.

1. The row count is small: for just a few hundred rows, a full table scan is judged to be faster. This is normal behavior and needs no fix
2. A function is applied to the column: WHERE lower(email) = ? can't use an index on email (the index holds the original values). Either create an expression index, CREATE INDEX ... ON users(lower(email)), or change the query
3. A LIKE with a leading wildcard: WHERE name LIKE '%Yamada%' can't use a B-Tree, since there's no fixed "starting point" in sort order (this moves into full-text search territory, out of scope for this guide)

The Harm of Over-Indexing

In exchange for speeding up reads, an index makes you pay the cost of updating the index itself on every write. Every time a row is INSERTed, a new entry has to be inserted at the correct position in every index. An INSERT into a table with 10 indexes means 1 write to the table itself plus 10 writes to indexes.

Harm	Description
Slower writes	Every INSERT / UPDATE / DELETE updates every index
Storage consumption	An index is a data structure with real substance, and can balloon to a size comparable to the table itself
Unused indexes left in place	Even once a query changes and stops using an index, you keep paying its cost

The basic stance is "index columns you know you'll search on. Don't index columns you're not sure about — add them later, once you observe a slow query, after confirming with EXPLAIN." Investigating which indexes go unused (monitoring statistics views) is an operational topic, so it's out of scope for this guide.

UUID Primary Keys and Index Locality

Chapter 10 introduced the guideline "if you expose an ID in a URL, prefer a UUID over a sequential number." From an indexing perspective, there's one thing to watch out for with UUIDs.

A sequential primary key (SERIAL / IDENTITY) monotonically increases, so it keeps getting appended to the end of the B-Tree. A UUIDv4, generated by gen_random_uuid(), is completely random, on the other hand, so insertion positions scatter across the whole index. Splits happen all over the pages (the index's internal blocks), and on a table with heavy write volume, the index becomes increasingly fragmented and bloated.

UUIDv7 (RFC 9562, May 2024) resolves this weakness. Because it holds a timestamp in its first 48 bits, values come out nearly sorted in generation order, and get appended to the end of the index just like a sequential number. Starting with PostgreSQL 18, you can generate one with the built-in uuidv7() function; on earlier versions, you can generate one with a library on the application side.

A rule of thumb for choosing

• Need an unguessable ID + heavy write volume → UUIDv7
• An existing system already using UUIDv4 → no need to migrate until you observe a performance problem (decide based on EXPLAIN and write throughput)
• An ID used only internally → a sequential number (BIGSERIAL / IDENTITY) is the simplest and fastest

Exercises

Exercise 1: Where to index

For a blog service's posts table, the app frequently issues the following three queries. Decide which indexes should be added (the primary key is already defined as id BIGSERIAL PRIMARY KEY).

-- (a) An author's articles, newest first
SELECT * FROM posts WHERE author_id = ? ORDER BY published_at DESC LIMIT 20;

-- (b) Fetch one article by slug
SELECT * FROM posts WHERE slug = ?;

-- (c) The total count of published articles
SELECT COUNT(*) FROM posts WHERE status = 'published';

Sample answer

-- For (a): a composite with the equality filter author_id first, and the sort column published_at after
CREATE INDEX idx_posts_author_published ON posts(author_id, published_at);

-- For (b): slug is a one-article-per-value business key, so index it via a UNIQUE constraint (which also creates an index automatically)
ALTER TABLE posts ADD CONSTRAINT uq_posts_slug UNIQUE (slug);

-- For (c): status has too few distinct values for a standalone index to help much.
--     If you only need to count "published" rows, a partial index is an option
CREATE INDEX idx_posts_published ON posts(published_at) WHERE status = 'published';

Why this works:

• For (a), combining "equality → sort" into a single index in that order lets both the filter and the sort be handled by the index alone
• For (b), a single UNIQUE constraint gets you both faster searches and the business rule "no duplicate slugs" at once
• For (c), a low-cardinality column like status doesn't gain much from a standalone index. Making it a partial index turns it into a small index covering only "published" rows

A common mistake: creating two standalone indexes on author_id and published_at for (a). Two standalone indexes can't produce the state of "filtered, then already sorted," so they're less efficient than a single composite index. Also, if you have (author_id, published_at), a standalone index on author_id is unnecessary, since it's covered by the leftmost prefix.

Exercise 2: Judge whether it's used

Given CREATE INDEX idx_users_email ON users(email);, can the following queries use this index?

1. SELECT * FROM users WHERE email = 'a@example.com';
2. SELECT * FROM users WHERE lower(email) = 'a@example.com';
3. SELECT * FROM users WHERE email LIKE '%@example.com';

Sample answer

#	Verdict	Reason
1	✓ Can use it	An equality search on the value exactly as it's stored in the index
2	✕ Can't use it	The index holds the value before lower() is applied. It would work if you separately created an expression index on lower(email)
3	✕ Can't use it	With a leading %, there's no fixed starting point in sort order, so the B-Tree can't be traversed

How to check: don't guess — run it with EXPLAIN attached and see whether Index Scan or Seq Scan is shown. That's the reliable way.

Summary

Here's a recap of what this chapter covered.

• An index is a "pre-sorted index structure" that turns a full table scan into a log-N-order search
• Primary keys and UNIQUE constraints get an index automatically, but PostgreSQL doesn't index foreign-key columns (create it yourself)
• Candidates for indexing are columns that appear in WHERE / JOIN / ORDER BY. A standalone index on a column with few distinct values has limited effect
• A composite index follows the leftmost-prefix rule: "columns filtered by equality first, range/sort columns after"
• Check whether it's actually being used with EXPLAIN (Seq Scan showing up is a signal to reconsider)
• An index costs write throughput and storage. Only index what you know you'll use, and add more later once you've observed a need
• If you need an unguessable ID on a table with heavy write volume, UUIDv7 is friendlier to the index than UUIDv4

Related references

• Design Template Collection — Index Checklist — items to check during review
• Glossary — a quick-reference dictionary of technical terms

Back to the top of the guide

Introduction to Database Design — Getting Started