Compressed reference. Pairs with Lesson 1: Composite Indexes.
| Term | Meaning |
|---|---|
| B-tree index | Default Postgres index type. Stores sorted keys in a balanced tree — fast equality & range lookups, and rows come back already sorted by the indexed column(s). |
| Composite index | One B-tree built over multiple columns, e.g. (org_id, status, created_at). Sorted by the first column, then the second within each value of the first, and so on. |
| Selectivity | Fraction of rows a condition matches. Low selectivity (few matching rows) = good index candidate. High selectivity (most rows match) = index often skipped. |
| Seq Scan | Reads the whole table, row by row. Chosen when no useful index exists, or the planner estimates it's cheaper than using one (e.g. low selectivity). |
| Index Scan | Walks the B-tree to find matching rows directly, then fetches each row from the table heap. |
| Index-Only Scan | Answers the query from the index alone, no heap fetch needed — because every requested column is in the index. |
| Bitmap Heap Scan / BitmapAnd | Planner's fallback when it has two separate single-column indexes to combine: builds a bitmap of matching row locations from each index, ANDs them, then fetches rows. Works, but can't provide sort order. |
| Sort node | Explicit sort step in a query plan, shown in EXPLAIN when the data isn't already in the order ORDER BY needs. Cost grows with row count — expensive at scale. |
| EXPLAIN ANALYZE | Runs the query for real and shows actual time/rows per plan node, not just estimates. Always prefer this over plain EXPLAIN when diagnosing. |
Equality columns first, range/sort column last — in that order, matching the ORDER BY direction.
For WHERE org_id = ? AND status = ? ORDER BY created_at DESC:
CREATE INDEX ON table (org_id, status, created_at DESC);Why this order: the B-tree is sorted by org_id first, then by status within each org_id, then by created_at DESC within each (org_id, status) pair. Since both org_id and status are equality filters, all matching rows sit in one contiguous, already-created_at-sorted block. No separate sort step, no combining multiple indexes.
EXPLAIN ANALYZE on the slow query.