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

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

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:

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:

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:

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:

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

Now this query never touches the table:

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.