Why is my query not using the index I created?

Usually one of three reasons. The filter is not selective enough, so the planner correctly prefers a sequential scan. The query does not match the index's leftmost columns, so the index cannot serve it. Or an expression blocks it, like WHERE lower(email) = ? when the index is on plain email, which needs an expression index instead. EXPLAIN ANALYZE shows which case you are in.

Should I just index every column to be safe?

No. Each index slows every INSERT, UPDATE, and DELETE on the table and consumes storage and cache space, and an index nobody queries is pure cost. Index the columns that appear in WHERE, JOIN, and ORDER BY clauses of queries you actually run, confirm with EXPLAIN that they get used, and periodically drop the ones usage stats show are dead.

When would I pick an LSM-based store over a B-tree one?

When sustained write volume is the defining constraint, like ingesting events, metrics, or messages at hundreds of thousands of rows per second. LSM-trees turn those writes into sequential disk I/O, which B-trees cannot match. If your workload is read-heavy with moderate writes, which describes most applications, B-tree engines give faster and more predictable lookups.

Databaseschapter 2 of 5 · 4 lessons

Indexing

Pre-sorted data structures that trade write overhead for dramatic read speedup.

← All chapters View in cheatsheet

Your users table has 50 million rows. Somebody logs in, and your database looks up their email address by reading every single row until it finds the match.

That takes about 3 seconds, and it happens on every login. Add one line creating an index on that column and the same lookup takes 3 milliseconds, a thousand times faster, for four minutes of work.

It is the best return on effort anywhere in database performance. It is also not free, and the bill arrives on every write you ever make to that table, which is why index everything is exactly as wrong as index nothing.

Lessons

4 in this chapter

What an Index Actually IsA second, sorted copy of some columns, with pointers back to the real rows.3 min
B-Trees, the DefaultHigh fan-out keeps any lookup within three or four page reads, even at billions of rows.3 min
Composite and Covering IndexesColumn order inside an index is a design decision, not a detail.3 min
What Writes Pay for Your ReadsEvery index is one more structure your INSERT has to update, forever.3 min