I measured my old index notes on PostgreSQL 18. Five of them were wrong.

A few years ago I kept a folder of study notes on database indexing: B-trees, composite indexes, when an index helps and when it does not. They read well. I reused them to prepare for interviews. Recently I went back and ran every claim in the PostgreSQL file against a real server, because a note that has never been run is only a guess with good formatting.

Five of them did not survive. One was never true for PostgreSQL, one contradicted another note in the same file, one was true until PostgreSQL 18, one only holds under a collation most servers do not use, and one was right about the symptom and wrong about the fix.

The setup: PostgreSQL 18.6, plus 17.10 where the version matters, both in Docker with default settings and the image's default database collation, en_US.utf8. An orders table with 2,000,000 rows (177 MB): 200 shops, 200,000 customers, one year of created_at, inserted in random order. A customers table with 1,000,000 emails. Every query ran five times warm; I report the median time and the number of 8 kB pages the query touched, from EXPLAIN (ANALYZE, BUFFERS). Trust the pages more than the milliseconds: pages do not depend on my laptop.

0. First, a tell: the plan names

My notes described index access as INDEX UNIQUE SCAN, INDEX RANGE SCAN and TABLE ACCESS FULL. PostgreSQL never prints any of those. They are Oracle's plan operations. PostgreSQL's vocabulary is different, and it is the one you will be reading at 2 a.m.:

Seq Scan              read the whole table, page by page
Index Scan            walk the index, fetch each matching row from the table
Index Only Scan       answer from the index alone (visibility map permitting)
Bitmap Index Scan     collect matching row locations from the index first,
  Bitmap Heap Scan    then visit the table pages in physical order

That alone was a warning. If a set of notes uses another database's vocabulary, some of its conclusions probably come from that database too. They did.

1. “A table can have one clustered index”: PostgreSQL has none

The note said PostgreSQL has clustered and non-clustered indexes, chosen with a CLUSTER or NONCLUSTER keyword. That is SQL Server's model. PostgreSQL rejects both spellings:

CREATE CLUSTERED INDEX x ON orders_c (customer_id);
-- ERROR:  syntax error at or near "CLUSTERED"
CREATE NONCLUSTERED INDEX x ON orders_c (customer_id);
-- ERROR:  syntax error at or near "NONCLUSTERED"

Every PostgreSQL table is a heap: rows go wherever there is room, and every index points into it. What PostgreSQL does have is a command, CLUSTER table USING index, which rewrites the table once in index order. The difference between a property and a one-off command is the whole story, so I measured it on a copy of orders, reading 100 customers' orders by range:

Right after CLUSTER the rows sit together and the query touches 17 pages instead of 1,003. Then ordinary traffic arrives: new orders land at the end of the table, updated rows move to wherever there is space, and after a single round of writes the same query is back up to 283 pages. The planner's correlation statistic fell from 1.000 to 0.673. Meanwhile pg_index.indisclustered still says true: it only remembers which index to use the next time you run CLUSTER without naming one. The documentation says it plainly: clustering is a one-time operation, and later changes are not clustered.

The cost is the part a note should never leave out. CLUSTER takes an ACCESS EXCLUSIVE lock, which blocks even plain reads. While it held the lock, a SELECT with a two-second lock timeout got nothing:

SET lock_timeout = '2s';
SELECT count(*) FROM orders_c WHERE customer_id = 4242;
-- ERROR:  canceling statement due to lock timeout      (after 2.07 s)

So CLUSTER is a maintenance job for tables that are mostly read and rarely written, run in a window when nobody is reading them. It is not a design decision you make once in a migration and forget.

2. “Put the most selective column first”: put the equality column first

One note advised leading a composite index with the column that has the most distinct values. Further down, the same file stated the opposite rule: equality columns first, range columns after. Both cannot be right, so I asked the question they disagree on. Orders from one shop in one month:

SELECT * FROM orders
WHERE shop_id = 42
  AND created_at >= '2025-06-01' AND created_at < '2025-07-01';
-- 842 rows; the month alone holds 164,243 orders

created_at is almost unique; shop_id has 200 values. The “most selective first” rule says (created_at, shop_id). I built each index alone and ran the query against it:

A hundred times more index pages for the “selective” order. The reason is how a B-tree range scan works. With created_at first, the scan starts at June 1st and has to walk every entry until July 1st, all 164,243 of them from every shop, checking shop_id as it goes. With shop_id first, it jumps to shop 42 and reads only that shop's June, which is contiguous in the index.

What makes this easy to miss is that the plan hides it. Both plans print the same conditions:

-- (created_at, shop_id)                      -- (shop_id, created_at)
Bitmap Index Scan  pages=632                  Bitmap Index Scan  pages=6
  Index Cond: created_at >= ... AND             Index Cond: shop_id = 42 AND
              created_at <  ... AND                         created_at >= ... AND
              shop_id = 42                                  created_at <  ...

Index Cond lists every condition checked inside the index, whether or not it narrowed the part of the index being read. Only BUFFERS shows the difference, which is why I no longer read a plan without it. The rule that holds: columns you compare with = first, the one range column last. Distinct-value counts only matter to break ties between equality columns.

3. “An index is useless without its first column”: true until PostgreSQL 18

The classic rule: an index on (shop_id, created_at) cannot help a query that filters only on created_at, because the entries are sorted by shop first. On PostgreSQL 17 that is exactly what happens. On 18 it is not. One day of orders, 5,396 rows, same index on both servers:

PostgreSQL 18 added skip scan for B-tree indexes. Instead of reading the whole index, it searches it once per distinct shop_id, and the plan reports it directly:

Bitmap Index Scan on ix_skip  (actual rows=5396)
  Index Cond: (created_at >= '2025-06-01' AND created_at < '2025-06-02')
  Index Searches: 202
  Buffers: shared hit=652

202 searches for 200 shops, 652 index pages instead of 7,665, and within a quarter of a millisecond of a dedicated index. Now repeat it with a leading column that has many values, (customer_id, created_at) with about 200,000 customers:

No skip scan. Two hundred thousand searches would cost more than reading everything, and the planner knows it. So the updated rule is narrower than the old one: on PostgreSQL 18 a composite index can serve its second column when the first column has few distinct values. It rescues an index you already have. It does not replace one you need, and on 17 or earlier the old rule still applies in full.

4. “LIKE can use an index if % is not first”: not your default index

The note said a pattern such as 'abc%' can use an index, and '%abc' cannot. The second half is right. The first half depends on the collation, and the one most servers run with is not the one where it works. On the en_US.utf8 database, with a plain B-tree index on email:

The plain index answers equality instantly and ignores the prefix search entirely. A linguistic collation does not sort strings byte by byte, so “everything starting with abc” is not guaranteed to be one contiguous range of that index. An index built with text_pattern_ops, or with COLLATE "C", is sorted byte-wise, and the planner turns the prefix into a plain range: email ~>=~ 'abc' AND email ~<~ 'abd'. The operator class docs say the same: the pattern classes exist for databases that do not use the C locale.

That index has a price: it is sorted in the wrong order for everything else. ORDER BY email LIMIT 10 could not use it and fell back to a scan and sort, 44 ms. If you need both prefix search and sorting by email, that is two indexes. For a pattern with a leading wildcard no B-tree helps. The note suggested full-text search, but full-text search matches words, not fragments of an address. The tool for LIKE '%fragment%' is pg_trgm: 0.11 ms, and the same GIN index also handles the prefix case. It costs 49 MB here against 39 MB for the pattern index.

5. “Don't index a boolean”: unless one value is rare, and then do it partially

The note's reasoning was that a column like is_active has only two values, so an index cannot narrow anything down. That is true when the values are balanced and false when they are not. is_mobile is true for half the orders; is_refunded for 1%. Both got an ordinary index, 13 MB each:

For the balanced column the planner ignores the index, which still has to be updated on every write. For the rare value the same kind of index is 37 times faster. The note was right about the symptom and wrong about the rule: what matters is not how many values a column has but how rare the value you query is.

And when you only ever query the rare value, you do not need to index the other 99%. The real query was “refunded orders in June”:

CREATE INDEX ON orders (is_refunded, created_at);         -- 60 MB,  0.32 ms
CREATE INDEX ON orders (created_at) WHERE is_refunded;   -- 456 kB, 0.44 ms

The partial index covers only the 20,001 refunded rows. It is 135 times smaller, costs nothing on writes to orders that were never refunded, and answers the query in about the same time. The planner uses it whenever the query's WHERE implies the index's condition.

What the notes say now

• PostgreSQL has no clustered index. CLUSTER sorts the table once, under a lock that blocks reads, and decays with the next writes.
• Equality columns first, the range column last. And read plans with BUFFERS: Index Cond shows what was checked, not what was skipped.
• On PostgreSQL 18, a composite index can serve its second column if the first has few distinct values. Look for Index Searches in the plan. On 17 and earlier, it cannot.
• Prefix LIKE needs text_pattern_ops or a "C" collation unless the database already uses C. Fragments need pg_trgm.
• Index a rare value, not a column. Usually as a partial index.
• Check which database a note was written for. Several of the wrong ones here were correct somewhere else.

None of this took long to check: two Docker containers and a few dozen EXPLAIN (ANALYZE, BUFFERS) runs. The notes had been wrong for years. That trade is why I now measure a claim before I write it down, rather than after I have repeated it in an interview.