PostgreSQL Deep Dive
by Sergey Okinchuk
Transaction ID WraparoundB-tree Basics

Chapter Eight · Indexes

Indexes

Cartwheel's orders table carries nine indexes and three of them have never been scanned. Six topics take the index set apart on the real schema: what a B-tree costs on every write, how column order and INCLUDE decide whether a query touches the table, which access method implements which operator, and how to build and drop indexes on a live table without stopping checkout.

6 topics

Chapter 7 left the cleanup machinery understood: dead row versions, autovacuum, and the freezing that keeps transaction ids from running out. Indexes are the other thing that machinery has to keep in step. An index entry names a physical row version, so every superseded version leaves entries behind, and every index on a table is more work for the vacuum that has to remove them. That connection runs the wrong way round in most people's heads — an index is filed under reads, and half of its cost is on the write path.

The evidence is on orders right now. Nine indexes, added one at a time across four years, each one reasonable on the day it appeared. Three have not been chosen by a plan since the statistics were last reset. The customer-history screen has an index that finds the right twenty rows and then reads the table for every one of them, because nothing in its definition says which columns the screen displays. On delivery_events, a 40 GB B-tree on occurred_at does a job that 90 MB of a different index type would do better. And the product search runs ILIKE '%straw%' against 200,000 rows on every keystroke, which no B-tree can help with at all.

Each topic fixes one of those, on the real schema, with the numbers attached. What this chapter deliberately does not do is teach you to read a plan — that is Chapter 9's subject, and it arrives immediately afterwards for a reason. An index is a bet about how the planner will price a query, and the chapter after this one is where you learn to check whether the bet paid.

An index is filed under reads. Half of its cost is on the write path.
Nine indexes on ordersadded one at a time across four years
→
Every write maintains all ninethe row itself, then one entry per index
→
Every superseded version leaves nine behindone dead entry per index
→
Vacuum removes thema full pass over every index, every run
→
Three return nothingnot chosen by a plan since the statistics were reset

Topics in This Chapter

Topic 38
B-tree — The Default, and What It Costs
The tree's shape, the operators it serves, and what nine indexes do to a single insert on a Saturday morning. Deduplication since 13, skip scan since 18, and why a v4 UUID key produces an index twice the size it needs to be.
Index Basics
Topic 39
Multicolumn, Covering, and Index-Only Scans
Why equality columns come before the range column, and what INCLUDE can and cannot do. The visibility map decides whether the scan skips the table, so a high Heap Fetches count is a vacuum report rather than an index report.
Index Design
Topic 40
Partial and Expression Indexes
An index over 2% of orders, and an index over a computed value. The implication proof the planner needs before it will use either, why a parameterized predicate breaks it, and what a mislabelled IMMUTABLE function does to your results.
Index Design
Topic 41
GIN, GiST, BRIN, and Friends
Containment on jsonb, overlap on ranges, and the block-range index that replaces 40 GB with 90 MB on an append-only table. Choosing by the operator your query already uses, and what each access method costs to keep maintained.
Access Methods
Topic 42
Full-Text Search and Trigram Matching
A stored tsvector column with the configuration pinned, websearch_to_tsquery for what users actually type, and pg_trgm making a leading wildcard indexable. Where the two techniques separate, and where Postgres stops and a search engine starts.
Search
Topic 43
Building Indexes Safely, and the Ones You Don't Need
The lock a plain CREATE INDEX takes, the two passes CONCURRENTLY makes instead, and the invalid index a failed build leaves behind. Then the audit: finding the indexes nothing has scanned, without deleting the one the month-end report needs.
Index Operations