B-Tree Skip Scan Lets an Index Serve a Query That Omits the Leading Column
Before PostgreSQL 18, an index on (tenant_id, created_at) was useless to a query that filtered only on created_at. PostgreSQL 18 added skip scan: when the leading column has few distinct values, the engine walks each of them in turn and searches the rest of the index underneath, so the index still helps. It is a rescue for an index whose column order is slightly wrong, not a reason to stop caring about column order, and it only pays off when that leading column really has few values.
Questions this Concept answers
Why does skip scan only pay off when the leading column has few distinct values?
J
jeremy
Video
B-Tree Skip Scan on Composite Indexes in PostgreSQL 18
A composite (multi-column) B-tree index in PostgreSQL has traditionally obeyed a leading-column rule: the index can only be used when the query filters on its first column, because the index keys are…