Every index in the two previous lessons has been a B-tree, and that's no coincidence: it's the default method and the one you'll use 95 % of the time. But there are questions a B-tree can't answer. How do you search for '%organic%' with no prefix? How do you index a JSONB document? How do you index a historical table of three billion rows without the index taking up a hundred gigabytes? That's what PostgreSQL's six access methods are for, and the first half of this lesson is the map for choosing between them.

The second half is more important and is almost never taught: when not to index. Because the expensive index mistake isn't forgetting one —that's spotted with EXPLAIN and fixed in a minute—, it's accumulating twenty that nobody uses, that fatten every write and that slow maintenance down. You'll see an index's real cost, the five cases where it gets in the way —including GreenStore's, demonstrated—, the selectivity rule that settles most of the doubtful cases, and a checklist to run through before writing CREATE INDEX.

1. PostgreSQL's six access methods
2. B-tree, Hash and why the second one rarely pays off
3. GIN: lists of things inside a column
4. GiST, SP-GiST and BRIN
5. pg_trgm: speeding up LIKE '%text%' at last
6. What an index really costs
7. The five cases where an index is useless or gets in the way
8. The selectivity rule
9. How to decide: start from the queries, not from intuition
10. Checklist: before creating an index, ask yourself…
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

The table that sums up when to use each one:

Read it with this idea in mind: the B-tree indexes one value per row; the others index something else. GIN indexes the parts of a value (the words of a text, the keys of a JSON, the trigrams of a string). GiST indexes regions that can overlap. BRIN doesn't index rows at all: it indexes disk blocks.

You already know the B-tree from 08-01: sorted, logarithmic, good for equalities, ranges, prefixes, sorting and extremes. It's CREATE INDEX's default value and the right answer unless you have a specific reason for something else.

The Hash stores the result of a hash function of the key. That makes it very fast for =… and completely useless for everything else:

The hash's real advantage is a single one: with very long keys (a 500-character URL, a SHA-256 hash as text) it stores 4 bytes instead of the whole value, and the index comes out considerably smaller. Outside that case, the B-tree does the same and a great deal more for a similar cost. (Historical context for its bad reputation: until PostgreSQL 9.6 they weren't written to the transaction log, so they got corrupted after a crash and weren't replicated. Since version 10 they're safe.)

GIN (Generalized Inverted Index) is an inverted index, the same idea as a book's index taken to the extreme: instead of one entry per row, it stores one entry for each element contained in the row, and in each one the list of rows where it appears. It's useful when a column contains many things and you want to search by one of them:

Its three natural territories:

Its two costs, which have to be kept in mind: it's slow to build and slow to update, because a single row can generate dozens of entries. For write-intensive loads over GIN columns, PostgreSQL cushions things with a pending list (fastupdate), but the pattern is still "write little, read a lot".

GiST (Generalized Search Tree) is a tree where each node describes a region containing its children, and those regions can overlap. That makes it the natural tool for data with extent:

That EXCLUDE constraint —which can only be implemented with GiST— rejects any insert whose date range overlaps with another promotion for the same product. It's a business rule neither UNIQUE nor CHECK can express. GiST is also the basis of PostGIS (maps, coordinates, "the ten warehouses closest to Valencia") and of the neighbour operator <->.

SP-GiST (Space-Partitioned GiST) is its cousin for structures that split into disjoint and very uneven pieces: prefix trees for strings, quadtrees for points, IP addresses. It's specialised; you'll know you need it when you need it.

BRIN (Block Range INdex) deserves more attention, because it's the most surprising one. It doesn't store one entry per row: it stores, for each group of 128 disk blocks, the column's minimum and maximum value. Nothing more.

With an order history inserted in chronological order, block 40,000 contains dates from June 2024 and only from June 2024. So for WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30' the engine discards 99.9 % of the blocks by reading a tiny summary, and sequentially walks the few that remain.

That last row is both the condition and the trap: if the data isn't physically sorted by the column, a BRIN is no use whatsoever. If every block contains dates from 2019 to 2026, all the summaries overlap and they all have to be read. That's why BRIN is the perfect answer for history or log tables that only grow at the end, and a bad idea for a column updated out of order.

The moment has come to close the promise 04-01 left open. A B-tree can't resolve LIKE '%organic%' because with no prefix there's no entry point in the order. The solution is to stop indexing the string and start indexing its trigrams: every group of three consecutive characters.

The extension lowercases the string, adds two spaces in front and one behind, and chops it up. Now a GIN index over those trigrams turns searching for '%organic%' into searching for rows containing all the trigrams of 'organic':

Three rows. With 20 products the engine will do a Seq Scan anyway; with 40,000 references in the shop's search box, the difference is between 300 ms and 3 ms. And there's an additional prize: the same index speeds up similarity search, which is what you want when the customer misspells the name:

The % operator means "similar enough" according to the pg_trgm.similarity_threshold setting (0.3 by default). On the usual question, GIN or GiST for trigrams: gin_trgm_ops searches faster but takes up more space and writes worse; gist_trgm_ops is smaller, cheaper to maintain and the only one that supports neighbour search with <->. GIN by default; GiST if you write a lot or need to sort by distance.

Dialect note: this is very divergent territory. MySQL 8 offers FULLTEXT indexes (with MATCH ... AGAINST), which aren't the same as trigrams and don't speed up a generic LIKE '%x%'. SQL Server has Full-Text Search as a separate component. SQLite has the FTS5 module. Oracle has Oracle Text. None of them replicates pg_trgm exactly: if your product depends on fuzzy search, it's a real factor in choosing an engine.

So far, the catalogue. From here on, the part that prevents disasters. Every index you create charges a permanent toll:

A PostgreSQL nuance worth knowing: an UPDATE writes a new version of the row, it doesn't modify the existing one. If the new row fits in the same block and no indexed column has changed, the engine applies an optimization called a HOT update that avoids touching the indexes. But it only takes one index covering a modified column to lose that optimization and have to update all the indexes. In other words: a badly chosen index can make even the UPDATEs that don't use it more expensive.

This is the easiest one to demonstrate, and you've already seen it hinted at twice:

All twenty products fit in a single 8 kB page. Reading that page costs one access. Using an index would cost: reading the index's metadata page, reading the root, getting the ctid and reading the table's page anyway. At least three accesses to do the work of one. That's why, however perfect an index you create on products.price, the planner will ignore it — and rightly so. You'll see it in the real plan in lesson 08-05.

The approximate boundary sits at a few hundred rows, or better put: as long as the table fits in a handful of pages and lives permanently in cache, there's nothing to optimize. The GreenStore indexes you created in 08-02 are an investment for the future, not an improvement today.

Cardinality is the number of distinct values. The closer it is to the number of rows, the more useful the index:

The boolean case is the clearest. products.active has 19 trues and 1 false. An index over that column would have two "blocks" of entries, and searching by active = TRUE would return 95 % of the table: there's nothing to discard. The only way to get anything out of a column like that is the other way round, with a partial index (08-02) that indexes only the minority side or that uses active as the condition and another column as the key.

products.cost, orders.shipping_cost, order_lines.discount, employees.salary. They're columns that get displayed, summed and computed, but that nobody puts a WHERE on. An index over them is pure cost: space, writes and VACUUM in exchange for zero accelerated reads.

Before indexing a column, the question is literal: can I write the real query, with its WHERE, that this index is going to speed up? If you can't come up with it, don't create it.

An event-log, audit or telemetry table receives thousands of INSERTs per second and is queried once a day. Every index multiplies the hot path's work to benefit the cold path. In those cases:

• Cut the indexes down to the bare minimum.
• If access is by date and the table only grows at the end, BRIN instead of B-tree: practically free to maintain.
• Consider creating the index only when it's going to be used (before the monthly report) and dropping it afterwards, however odd that sounds.

It's section 10 of 08-02's case: (a) is surplus if (a, b) exists. It's worth repeating here because it's the most frequent useless index in real databases: the simple one was created, months later somebody created the composite one for another query, and nobody dropped the first.

The previous five cases boil down to a single quantitative criterion, and it's the one the planner itself uses:

If the query is going to return more than 5–10 % of the table's rows, the sequential scan usually wins.

It sounds counterintuitive until you understand the reason, which is purely physical:

• A Seq Scan reads contiguous blocks. The disk (and above all the operating system's read-ahead) is optimized for that: reading 1,000 blocks in a row doesn't cost 1,000 times reading one.
• An Index Scan produces ctids in the index's order, not in the disk's. Each row can be in a different block and at any position: those are random accesses. PostgreSQL models it with two parameters, seq_page_cost = 1.0 and random_page_cost = 4.0: a random access is estimated to be four times more expensive than a sequential one.

With those numbers, if your filter returns half the table, going through the index means making half a million random accesses at four times the price to avoid reading a million sequential blocks. You lose.

Applied to GreenStore:

About random_page_cost: that default 4.0 comes from the era of mechanical disks. On an SSD the difference between sequential and random access is far smaller, and the usual recommendation is to lower it to 1.1. It's one of the configuration settings that most changes the plans chosen, and it explains why the same query can use the index on one server and not on another. You'll see how to check it with EXPLAIN (SETTINGS) in 08-05.

The antipattern has a name of its own: "let's index every column just in case". It sounds prudent and is exactly the opposite, because it swaps a visible, easy problem (a slow query EXPLAIN points at in ten seconds) for an invisible, hard one (writes 40 % slower, a VACUUM that never finishes, wasted cache, and a badly chosen plan every now and then).

The correct method goes the other way round: from the queries to the indexes. And the source of truth is the pg_stat_statements extension, which records every statement executed with its timings:

Sort by total time, not by average time: a 5 ms query run a million times a day does more damage than a 10-second one fired once. That ranking gives you the real list of candidates, and on each one you apply EXPLAIN ANALYZE (08-05).

In four steps:

1. Measure which queries consume the time (pg_stat_statements).
2. Diagnose each one with EXPLAIN ANALYZE and look for the Seq Scan over a large table with a selective filter.
3. Test the index and measure again: did the plan change? did the time drop?
4. Review a few weeks later with pg_stat_user_indexes: is anybody using it?

• Using hash "because equality lookups are faster". The B-tree resolves equality almost as well and additionally serves ranges, ordering and UNIQUE.
• Putting a BRIN on a column with no physical ordering. If the values are spread across the whole table, the summaries overlap and the index discards nothing.
• Expecting a GIN to speed up writes. It's the most expensive method to maintain; its place is "write little, read a lot".
• Indexing a boolean. Two distinct values discard nothing. The useful version is a partial index with that boolean in the WHERE.
• Creating an index with no specific query behind it. That's the definition of the "just in case" antipattern.
• Confusing "the index exists" with "the index is used". Only EXPLAIN and pg_stat_user_indexes answer the second.
• Sorting pg_stat_statements by average time. The real damage is done by total time: calls × cost.
• Tip: count the rows before indexing. SELECT COUNT(*) FROM table WHERE <your filter> against the total gives you the selectivity in five seconds and settles half the cases.
• Tip: when torn between two indexes, create one, measure and drop the worse one. DROP INDEX is instantaneous and doesn't touch the data: experimenting comes cheap.
• Tip: note in the schema itself what each index was created for. COMMENT ON INDEX idx_orders_pending IS 'Management panel: orders to be processed'; means that in two years' time somebody can drop it with a proper basis.

Choose the appropriate access method for each need and write the CREATE INDEX:

1. The shop's search box lets you type any fragment of a product's name.
2. A history of 500 million events, always inserted in date order, queried by ranges of days.
3. A product's attributes JSONB column, searched by key-value pairs.
4. The email field of a table of 50 million users, always queried by exact equality and never sorted.
5. A booking calendar where two bookings for the same room can't overlap in time.

For each proposal, say whether you'd create the index and why. Use GreenStore's real data.

An application with an events table of 800 million rows takes 4,000 INSERTs per second and has eleven indexes. The team complains that inserts are getting slower and slower and that VACUUM never finishes.

1. Explain the relationship between the eleven indexes and the two symptoms.
2. Propose a method for deciding which ones to drop, with the specific queries.
3. What alternative is there for the index on the date column if the table only grows at the end?

Solution 1

In number 4, a B-tree would be just as valid and more versatile; the hash only wins on size, and only because it's never sorted or searched by range. If there were the slightest doubt, B-tree.

Solution 2

Solution 3

1. The relationship. Every INSERT has to write into the table and into the eleven indexes: it's a multiplier over the 4,000 inserts per second, and it explains why the rate degrades as the trees grow and there are more page splits. And VACUUM walks each index separately to clean up the dead entries: with eleven indexes over 800 million rows, every pass is eleven times the work. Both symptoms have the same cause.

2. The method:

With 08-02's four caveats: check how long the counters have been accumulating (make sure the quarterly report isn't missing), don't touch the ones backing a PK or a UNIQUE, review the foreign-key ones, and look at the replicas too. Then cross-check with pg_stat_statements which queries actually run, and look for redundancies with the prefix rule: any (a) living alongside an (a, b) is surplus.

3. The alternative. A BRIN on date. If the table only grows at the end, the physical order coincides with the chronological one, which is exactly its requirement. You'd go from a B-tree of tens of gigabytes —which has to be maintained on each of the 4,000 INSERTs per second and walked in full on every VACUUM— to an index of a few megabytes with an almost nil maintenance cost, keeping the ability to filter by ranges of days.

You now have the complete map and, above all, the criterion for not using it:

• PostgreSQL offers six access methods. The B-tree solves 95 % of cases; the hash rarely pays off; GIN indexes the parts of a value (arrays, JSONB, text, trigrams); GiST indexes overlapping regions (ranges, geometry, EXCLUDE); BRIN summarises blocks and is tiny but demands physical ordering; SP-GiST is for hierarchical structures.
• pg_trgm + GIN closes 04-01's promise: LIKE '%organic%' and ILIKE finally have an index, with similarity search via similarity() and the % operator thrown in.
• An index costs: space, slower INSERT/UPDATE/DELETE, slower VACUUM, occupied cache. And it can make even the UPDATEs that don't use it more expensive, by preventing the HOT optimization.
• Don't index small tables —GreenStore's 20 products fit in one page, and the index takes up more than the table—, low-cardinality columns, columns nobody ever filters by, write-heavy tables, or anything another index already covers.
• The selectivity rule: above 5–10 % of rows returned, the Seq Scan wins, because a random access is estimated to be four times more expensive than a sequential one.
• The right method goes from the queries to the indexes, with pg_stat_statements sorted by total time, and never the other way round.

With the first three lessons you know what an index is, how it's created and when not to create it. But an index is only one of performance's tools, and often not even the one that's needed: there are queries that run slowly because of how they're written, and no index in the world fixes them. In lesson 08-04, Query Optimization Techniques, you'll see the writing rules that do matter —starting with sargability, that WHERE EXTRACT(YEAR FROM order_date) = 2025 that has to be turned into a range—, how the statistics that feed the planner work and what happens when they go stale, and the problem most often behind a slow screen and that isn't fixed in the database at all: the application's N+1.