Which shortcut to build

The shortcut most indexes are: a sorted tree whose leaves point at rows in the table. This lesson is about the other shortcuts Postgres can build, how to pick the right one for a query, and what every one of them costs on the next write.

Before you start. B-Tree on Disk showed what an index is: a second, sorted copy of one column that points back at the table, so a lookup reads a few pages instead of all of them. MVCC & Vacuum showed that an update does not change a row in place; it writes a new version and leaves the old one for vacuum. The sentence that says what set up the bench this lesson runs on, and The Query Planner showed how Postgres chooses between a scan and an index. Here you choose the index.

The idea in 30 seconds

An index is a trade, not a fix. Each one speeds some reads and adds work to writes, and the work depends on what the index mentions.
Match the shortcut to the question: a plain B-tree for a column compared as stored; a partial index for a small slice; a covering index (INCLUDE) to answer without visiting the table; an expression index for a function of a column; GIN for what is inside a value; BRIN for a column that follows insert order.
No index makes a query cheap if it still matches a large share of the table. Selectivity comes first.
A HOT update skips every index, but only if no indexed column changes (and the page has room). A column added to a covering index can take HOT away from a column that changes often.
To choose, run EXPLAIN (ANALYZE, BUFFERS) before and after and compare pages touched. To audit, find indexes nothing has used.

Read the story · Skip to the numbers