# Things You Didn't Know About Indexes

A practical guide to database indexes using a Pokémon table as the running example. Covers the trade-offs most tutorials skip and three index types that solve specific problems.

## The fundamental trade-off

> Reads get faster, writes get slower.

Every INSERT, UPDATE, or DELETE must update all indexes on the table. A table with eight indexes has nine structures to maintain. The query planner also weighs more options with more indexes — planning time can exceed execution time on fast lookups.

## Why your index isn't working

### Composite indexes care about order

An index on `(type_1, type_2)` creates a structure sorted first by `type_1`, then by `type_2` within each group:

```
Bug      → Flying   → [Butterfree, ...]
         → Poison   → [Venonat, ...]
Electric → Flying   → [Zapdos, ...]
Fire     → Flying   → [Charizard, ...]
Water    → Flying   → [Wingull, ...]
```

This helps queries on `type_1` alone, or `type_1 AND type_2` together. But `type_2` alone? The Flying entries are scattered under Bug, Electric, Fire, Water — no single place to jump to. The index won't help.

If you query on `type_2` alone as often as `type_1`, create a second index.

### Functions defeat your index

```sql
SELECT * FROM pokemon WHERE lower(name) = 'pikachu';
```

The index is on `name`, not `lower(name)`. As far as the database is concerned, those are different things. It falls back to a full table scan, lowercasing each name as it goes.

This applies to any function wrapping the column. Implicit type conversions count too — comparing text against integer triggers a silent conversion with the same effect.

### Don't guess, measure

```sql
EXPLAIN SELECT * FROM pokemon WHERE name = 'Pikachu';
```

`Index Scan` = using your index. `Seq Scan` = reading every row. Use `EXPLAIN ANALYZE` to run the query and get actual timings, not just planner estimates.

## Lesser-known index types

### Functional indexes

Index the expression instead of the column:

```sql
CREATE INDEX ON pokemon (lower(name));
```

Now `WHERE lower(name) = 'pikachu'` uses the index. Works with any deterministic, immutable function.

Caveat: if you're indexing `lower(name)` constantly, ask why you haven't stored names in lowercase.

### Partial indexes

Index only rows matching a condition:

```sql
CREATE INDEX ON pokemon (name) WHERE is_legendary = true;
```

80 entries instead of 1000. Smaller, faster to query, cheaper to maintain. Queries that don't match the condition (`WHERE is_legendary = false`) fall back to a table scan, which is fine — they match most rows anyway.

Good for soft-delete patterns:

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

### Covering indexes

When the index contains every column the query needs, the database can answer from the index alone — no trip to the table. EXPLAIN shows this as `Index Only Scan`.

Use `INCLUDE` to add columns without sorting by them:

```sql
CREATE INDEX ON pokemon (name) INCLUDE (base_attack);
```

Now this query never touches the table:

```sql
SELECT name, base_attack FROM pokemon WHERE name = 'Pikachu';
```

Why not put `base_attack` in the indexed columns? Because the database would sort by it on every write — work with no benefit when you only search by name.

## Further reading

[Use The Index, Luke](https://use-the-index-luke.com/) — comprehensive guide to SQL indexing.

[[database-design-and-implementation]] — Sciore's book builds the machinery this page describes from the outside: B-tree index, buffer pool, lock manager, planner, one chapter at a time. The fastest cure for treating `EXPLAIN` output as an oracle is having written the planner that produces it.

[[readings-in-database-systems]] — the Red Book, free online. Where to go when the practical questions run out and you want the field: annotated papers on storage, query optimization, and transactions, with the editors arguing about what mattered.

See also [[inverted-index]] for full-text search indexes, [[lsm-tree]] for write-optimized storage where index maintenance is particularly expensive.