In the previous lesson you discovered that GreenStore has eleven indexes nobody created and is missing the ones for its eleven foreign keys. Time to fix that. Here you'll learn the full syntax of CREATE INDEX and, above all, the four variants that separate someone who "creates indexes" from someone who designs them: composite indexes, with the leftmost-prefix rule that decides what order the columns go in; partial ones, which index only the rows that matter; expression ones, which close the LOWER(email) problem you've been carrying since module 6; and the ones with INCLUDE to get an Index Only Scan.

The second half is just as important and usually missing from tutorials: managing the indexes that already exist. Listing them, measuring how much space they take, rebuilding them and spotting the two most expensive problems of a mature database: the indexes nobody uses and the ones that are redundant with another.

A warning about this module's objects. Every index created in module 8 is teaching material. It isn't part of GreenStore's canonical schema defined in 01-06: if you reload greenstore.sql, they disappear, and modules 9 to 12 don't assume them.

1. CREATE INDEX: the full syntax and how to name them
2. The indexes GreenStore is missing
3. CREATE INDEX CONCURRENTLY
4. Composite indexes and the leftmost-prefix rule
5. Partial indexes
6. Indexes on expressions
7. INCLUDE: covering indexes on purpose
8. Ordering and nulls inside the index
9. Management: listing, measuring, renaming, rebuilding and dropping
10. Unused and redundant indexes
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

The minimal case, and 70 % of the indexes you'll create in your life:

Notice what you don't have to put in: neither the method (btree is the default value) nor the data type nor the size. And what you should: the name. If you leave it out, PostgreSQL generates <table>_<columns>_idx, and you end up with orders_customer_id_order_date_idx1. The most widespread convention —and the one the course will use— is idx_<table>_<columns>:

The reasons are the same as in 05-01 with constraints: readable error messages and execution plans, migrations that can refer to the index by name, and two environments that don't end up with swapped names. The indexes backing a PRIMARY KEY or a UNIQUE are the exception: don't create them by hand. Declare the constraint and let the engine create the index; do it the other way round and you'll have an index with no associated constraint.

Let's start with what 08-01 identified: the eleven unindexed foreign keys. These are the ones genuinely worth having on a real-sized GreenStore:

The first two are the most profitable in the whole schema: order_lines is the table with the most rows, it appears in almost every JOIN in the course and its order_id has ON DELETE CASCADE, so with no index every order deletion walks the entire table.

And now the check worth always making, however much it hurts:

The index takes up twice as much as the table. It isn't a mistake: the 47 lines fit in a single 8 kB page, whereas a B-tree needs at least two —the metadata page and the root. It's the cleanest demonstration that at these volumes the index can't contribute anything, and that's why this module needs a large test table (you'll build it in 08-05). What follows makes sense with the GreenStore of five years' time in mind, with millions of lines.

A normal CREATE INDEX blocks the table's writes while it's being built. Reads carry on working; the INSERTs, UPDATEs and DELETEs wait. With 47 rows that lasts microseconds; with 50 million it can last twenty minutes, and those twenty minutes are an outage.

CONCURRENTLY builds the index in two passes over the table, without taking the lock that prevents writing. In exchange it has three drawbacks: it's slower (two full scans, plus a wait for the open transactions to finish); it can leave an INVALID index if something goes wrong, that is, one that's created but useless —it takes up space, it's maintained on every write and the planner doesn't use it—; and it doesn't fit inside a transaction, so BEGIN; CREATE INDEX CONCURRENTLY ...; errors out.

Detecting an invalid index and curing it —the cure is always the same, drop it and redo it—:

The rule, in one line: in development, CREATE INDEX; in production over a table with traffic, CONCURRENTLY always, and check afterwards that the index is valid.

The mechanism by which a normal CREATE INDEX prevents writing is that of locks, and with it come transactions and PostgreSQL's concurrency model. All of that is module 9; here it's enough to know it exists and that CONCURRENTLY is the way of dodging it. It's the same family of precautions you saw in 05-06 with the ALTER TABLEs that rewrite the table.

A composite index indexes several columns at once. It isn't the same as two separate indexes: it sorts first by the first column and, within each value, by the second, exactly like an ORDER BY customer_id, order_date.

Think of a phone book sorted by surname and then by first name. You can look up "all the Smiths" and you can look up "Smith, Anna". What you can't do is look up "all the Annas": you'd have to read the whole book. That's the leftmost-prefix rule: a composite index is good for the queries that use a prefix of its column list, starting with the first one.

The star case, over the course's data:

With the index on (customer_id, order_date), the engine enters the customer_id = 2 block and reads the two rows already sorted, backwards: zero comparisons and zero sorting. (With 20 orders it'll do a Seq Scan, of course; the reasoning holds for the large case.)

It's the most important decision of a composite index, and there's a practical rule that gets it right almost always:

First the columns compared by equality; then the one compared by range; and last, the one that only appears in the ORDER BY.

The reason is geometric. With (customer_id, order_date) and the filter customer_id = 1 AND order_date >= '2025-06-01', the engine jumps to the start of "customer 1, June 2025" and reads a contiguous stretch of leaves: it finds Lucía's single order after that date. With (order_date, customer_id) it would have to read every order from June 2025 onwards, for every customer, discarding as it goes. The sooner the range closes, the fewer entries get touched. Two corollaries:

• The moment a column is compared by range, the following ones stop being useful for filtering (only for discarding without going to the table). That's why the range goes last among the filters.
• An index on (a, b) makes an index on (a) unnecessary, but not one on (b). That's the basis of section 10's redundancy hunt.

And a practical limit: it's rarely worth going beyond three columns. Every extra column fattens the entries, reduces the fanout (08-01) and serves fewer queries.

A partial index carries a WHERE clause and only indexes the rows that satisfy it. A simple idea with two big effects: the index is smaller —it fits better in memory and is walked faster— and its maintenance is cheaper, because the excluded rows don't touch it when they're inserted.

The index's condition must imply the query's for the planner to be able to use it:

Where they really shine. In GreenStore, status <> 'delivered' is 6 orders out of 20 (30 %) and active is 19 products out of 20 (95 %): the saving is zero or negative. But those proportions collapse in a real shop: five years from now, the non-delivered orders will be around 200 out of 2,000,000 (0.01 %) and the active products around 3,000 out of 40,000 (7.5 %). The partial index on pending orders would index two hundred rows instead of two million: it would fit entirely in memory and would answer in microseconds. It's the classic pattern of work queues —"give me what's still to be processed"— and one of PostgreSQL's best tricks. Another very frequent use is indexing only what isn't null: CREATE INDEX idx_orders_employee_id ON orders (employee_id) WHERE employee_id IS NOT NULL; saves half the entries, because ten of the twenty orders are web orders (01-06), and it still serves every query by sales rep.

Dialect note: partial indexes exist in PostgreSQL and SQLite with this very syntax, and in SQL Server as filtered indexes. MySQL doesn't have them at all: it's one of the gaps you notice most when migrating to it.

Here module 6's warning is closed: a function on the filtered column prevents the index from being used. The solution is to index the function's result.

And the golden rule, the source of almost every disappointment:

The index's expression must match the query's literally. The planner compares expressions, not meanings.

That last pair has some substance to it. Both queries return the same 16 orders from 2025, but each one needs its own index, and CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM order_date)); only serves annual questions. The preferable solution isn't that one: it's to rewrite the query as a range over order_date and use a normal index, which will additionally serve "the March orders", "last week's" and ORDER BY order_date. It's the sargability criterion 08-04 develops.

Two technical requirements: the function has to be IMMUTABLE —for the same arguments, always the same result; LOWER(text) is, NOW() isn't, and neither is EXTRACT(YEAR FROM ...) over a TIMESTAMPTZ, because it depends on the session's time zone, although over a DATE like GreenStore's it is— and it's recomputed on every write, so it makes INSERTs somewhat more expensive than a normal index does.

Dialect note: Oracle has had them for decades (function-based indexes) and SQLite since 3.9. SQL Server doesn't have them directly: you emulate them with a persisted computed column plus an index on it. MySQL 8 supports them with double parentheses: CREATE INDEX ... ((LOWER(email))).

INCLUDE adds columns to the index that aren't part of the key: they're no good for searching or for sorting, they're just stored in the leaves so they can be read without going to the table. It's the explicit way of building 08-01's covering index and triggering an Index Only Scan.

That last row is the most elegant use: CREATE UNIQUE INDEX ... ON customers (email) INCLUDE (name, last_name) still guarantees "one email, one customer" and on top of that answers "what's the name of this email's owner?" without touching the table. INCLUDE has existed since PostgreSQL 11, and it's also in SQL Server and Oracle; MySQL doesn't need it in the same way, because its primary index is clustered.

By default a B-tree index is built ASC NULLS LAST, just like module 2's ORDER BY. And here you can save yourself some work: for a single-column ORDER BY you don't need to declare anything, because PostgreSQL can walk any index backwards. An index on (order_date) serves ORDER BY order_date just as well as ORDER BY order_date DESC.

Explicit ordering only matters when the ORDER BY mixes directions, because then no reading of the index, forwards or backwards, produces that order:

The same goes for nulls: if your reports do ORDER BY employee_id NULLS FIRST over GreenStore's ten web orders, an index on (employee_id NULLS FIRST) avoids the sort; the default index doesn't.

In psql, \di lists every index and \d+ orders those of a specific table with their definition; from SQL, 08-01's pg_indexes view. To measure:

(A sample run after creating this lesson's indexes; the total also includes each table's auxiliary structures.) The reading is perfectly real: the indexes take up more than ten times what the data does. With 20 rows per table it's an anecdote; in a mature database, indexes weighing more than the data is an alarm signal to be investigated with section 10.

REINDEX rebuilds the index from scratch. It's used in three situations: a corrupted index, an index that has grown too much through the accumulation of dead space (08-05's bloat), or a change of locale affecting text ordering. Day to day there's no need to reindex as a routine: PostgreSQL keeps the trees balanced on its own.

Dropping an index is instantaneous and doesn't touch the data; a normal DROP INDEX locks the table for an instant and CONCURRENTLY avoids that. What you can't drop that way is an index backing a constraint:

The right route is ALTER TABLE customers DROP CONSTRAINT customers_email_key;, which takes the constraint and the index away together.

In a database with years behind it, indexes pile up: one for a report that no longer exists, another for a query that got rewritten, a third "just in case". They all keep charging their toll on every write.

(A sample run: in GreenStore you get zeros because with 20 rows the planner chooses Seq Scan — 08-05.) An idx_scan = 0 is a candidate for dropping, with four caveats:

1. The counters accumulate since the last statistics reset. An index used only by the year-end close will look useless in March.
2. An index backing a UNIQUE or a PK doesn't get dropped, even if it's never used for searching: it's guaranteeing a constraint.
3. Foreign-key indexes can read 0 and be indispensable: referential integrity checks don't always add to the counter.
4. On a read-only replica the counters are different. Check both.

And from the leftmost-prefix rule the other problem follows: if an index on (a, b) exists, one on (a) is redundant.

The honest nuance: the short index is smaller and, if your most frequent query only filters by customer_id, it'll be marginally faster. It's almost never worth keeping both.

• Running a normal CREATE INDEX in production. It blocks writes for the whole build. CONCURRENTLY exists precisely for that.
• Not checking that a CONCURRENTLY finished properly. An INVALID index is the worst of both worlds: it costs to maintain and nobody uses it.
• Putting the composite index's columns in the order they appear in the WHERE. The order is dictated by the equality → range rule, not by the order in which you wrote the conditions.
• Creating (a) and (a, b). The first is surplus. It's the most frequent redundant index there is.
• Creating an expression index that doesn't match the query. LOWER(email) is no good for LOWER(TRIM(email)). Copy and paste the expression from the query.
• Using an expression index on the year instead of rewriting the query as a range. The range index serves many more questions.
• Creating a UNIQUE key's index by hand. Declare the constraint; the index comes with it and stays associated with it.
• Tip: create the foreign-key indexes right away. It's the set of indexes with the best benefit/risk ratio of any PostgreSQL schema.
• Tip: review pg_stat_user_indexes once a quarter. Dropping three dead indexes can speed up writes more than any query optimization. And name your indexes from day one: a plan full of orders_customer_id_order_date_idx1 is a plan nobody reads.

GreenStore's application runs these three queries on the customer detail screen:

Design the minimal set of indexes that covers them well, write the CREATE INDEX statements and justify the column order of each one.

A team has accumulated these five indexes on order_lines:

1. Which are redundant and why?
2. Which is almost certainly useless, and what query would you need to see to confirm it?
3. Cut the set down to two indexes and write the corresponding DROP INDEX statements.

Write the indexes that solve these three situations and explain in one sentence why you choose that variant:

1. The application searches for customers by typing the email regardless of upper or lower case.
2. The admin panel continuously displays the orders not yet delivered, which in production are around 200 out of 2 million.
3. A report lists customer_id, order_date and shipping_cost for a specific customer, and you want it not to touch the table.

Solution 1

The first covers a) and b). customer_id goes first because it's compared by equality, and order_date afterwards because it's the range and also the sort criterion: the engine reads a contiguous, already sorted stretch, with no Sort. And since ORDER BY order_date DESC is resolved by walking the index backwards, there's no need to declare DESC.

The second covers c). It could be done with a normal index on (status, order_date), but the partial one is far better: in GreenStore today it would index 1 row out of 20, and in production around 200 out of 2 million. The indexed column is order_date because status is already fixed by the index's condition: repeating it would waste space. There's no need for a standalone index on customer_id: it would be redundant with the first.

Solution 2

1. Redundant: idx_ol_1 is surplus because idx_ol_2 starts with order_id and covers everything it solves. idx_ol_3 is surplus because idx_ol_4 has the same key (product_id) and additionally includes two columns to avoid the table access.

2. Almost certainly useless: idx_ol_5, on quantity. It's a low-cardinality column (values from 1 to 8 across the 47 lines) and nobody searches for lines "by quantity": it's a figure that gets displayed and added up, not one you filter by. To confirm it, pg_stat_user_indexes and its idx_scan, with section 10's four caveats.

3. DROP INDEX idx_ol_1; DROP INDEX idx_ol_3; DROP INDEX idx_ol_5;. What's left is idx_ol_2 on (order_id, product_id), which serves "the lines of this order" and "this product within this order", and idx_ol_4 on (product_id) INCLUDE (quantity, unit_price), which resolves "every sale of this product" with an Index Only Scan. The two foreign keys are covered, which was the starting objective.

Solution 3

1. An expression index, because the index has to contain exactly what the query compares. Careful with the golden rule: if the application switches to ILIKE or starts normalising with TRIM, this index stops being useful and nobody finds out.
2. Partial, because the condition is constant and applies to a minority: the index fits in memory and isn't touched when inserting orders that are born delivered.
3. INCLUDE instead of (customer_id, order_date, shipping_cost), because shipping_cost isn't used for filtering or for sorting: putting it in INCLUDE only fattens the leaves and not the upper levels, and the tree keeps its fanout.

You now know how to build indexes, not just ask for them:

• CREATE INDEX has eight pieces, and the important ones are CONCURRENTLY, the column list, INCLUDE and WHERE. The default method is btree.
• CONCURRENTLY is mandatory in production: a normal CREATE INDEX blocks writes. In exchange it's slower and it can leave an INVALID index that has to be dropped and redone. The locking model behind it is module 9.
• A composite index serves the leftmost prefixes of its column list. The order is decided by one rule: equality first, range next, sorting last. And (a, b) makes (a) redundant.
• Partial indexes index only a subset of rows: tiny, fast and cheap to maintain. They're the ideal tool for work queues and for excluding nulls.
• Expression indexes close module 6's warning, with a golden rule that admits no nuance: the index's expression must match the query's literally, and the function has to be IMMUTABLE.
• INCLUDE builds covering indexes without fattening the key, and it's the only way to add columns to a UNIQUE index without changing what it guarantees.
• Managing is as important as creating: \di and pg_indexes to list, pg_relation_size to measure, REINDEX to rebuild, DROP INDEX to drop — and pg_stat_user_indexes to discover the ones nobody uses and the prefix rule to discover the redundant ones.

With this you can create any B-tree index you need. But the B-tree isn't the only one: PostgreSQL offers six access methods, and there are problems —LIKE '%text%', JSONB, date ranges, historical tables of billions of rows— that a B-tree doesn't solve well or doesn't solve at all. In lesson 08-03, Index Types and When Not to Index, you'll see the comparison table of the six methods, you'll close the pg_trgm promise that 04-01 left pending and —the most important part and the one least often taught— you'll learn when an index is useless or gets in the way: small tables, low-cardinality columns, poorly selective filters, write-intensive tables, and the antipattern of indexing everything just in case.