Recap — Writing Index-Friendly Queries

# Writing Index-Friendly Queries You won't (usually) create indexes. But understanding them changes which SQL you write. ## What an index actually is A B-tree of values, sorted, with pointers back to the table rows. `CREATE INDEX orders_customer_id_idx ON orders(customer_id)` builds a tree of customer_id values, each pointing to the corresponding order row. When you write `WHERE customer_id = 12345`, the engine walks the tree (O(log n) — milliseconds for a billion rows) instead of scanning every row (O(n) — seconds). ## Rule 1 — Filters on indexed columns are fast ```sql SELECT * FROM orders WHERE customer_id = 12345; -- uses index SELECT * FROM orders WHERE total > 1000; -- if no index on total, Seq Scan ``` ## Rule 2 — A function on the column kills the index ```sql WHERE UPPER(email) = 'X@Y.COM' -- index on email NOT used WHERE LOWER(email) = LOWER('X@Y.COM') -- still doesn't work WHERE email = 'x@y.com' -- uses index (if data is lowercase) ``` Either lowercase your data on write, or create a functional index: `CREATE INDEX email_lower_idx ON users(LOWER(email))`. ## Rule 3 — Composite indexes are left-to-right Index `(customer_id, status, created_at)` helps: - `WHERE customer_id = X` - `WHERE customer_id = X AND status = 'shipped'` - `WHERE customer_id = X AND status = 'shipped' AND created_at > Y` It does NOT help: - `WHERE status = 'shipped'` (missing leftmost column) - `WHERE created_at > Y` (missing leftmost columns) Rule of thumb: order columns in the index from "most equality-filtered" to "least equality-filtered". Equality first, range last. ## Rule 4 — Range filters end the usable index In `(customer_id, status, created_at)`, if you do: ```sql WHERE customer_id = 1 AND status > 'a' AND created_at = '2026-05-25' ``` The index uses `customer_id` (equality), then `status` (range), then **stops**. The `created_at` filter is applied after the index lookup, not inside it. Rule of thumb: put equality columns before range columns in composite indexes. ## Rule 5 — Indexes help ORDER BY too ```sql SELECT * FROM orders ORDER BY created_at DESC LIMIT 10; ``` With an index on `created_at`, this reads exactly 10 rows from the end of the index. No sort. No full scan. Same for `ORDER BY a, b DESC` with a composite index `(a, b DESC)`. ## Rule 6 — Too many indexes slow down writes Every INSERT/UPDATE has to update every relevant index. Tables with 15 indexes have noticeably slower writes than tables with 3. Production teams typically allow 3-7 indexes per table and resist additions. This is why "just add an index" isn't free — it's a write-time cost in exchange for read-time speed. ## When to ask engineering for an index You've identified a slow query and: 1. The WHERE column has high cardinality (many distinct values). 2. The filter is selective (returns ON ( );`, naming the columns and ideally an INCLUDE list: > "Could we add an index on `orders(customer_id, created_at)` INCLUDE (`total_amount`, `status`)? It would let the orders-by-customer dashboard skip the heap entirely." That's the level of specificity engineering wants. Vague "the dashboard is slow" gets ignored. ## When NOT to ask - One-time analyses → don't bother engineering. - Tables that get many writes per second → index cost might exceed read benefit. - Filters returning >20% of rows → Seq Scan is probably faster anyway. ## Common analyst-side index pitfalls 1. **Wrapping the column in a function** — kills the index. Rewrite predicates. 2. **Using LIKE '%text%'** — leading wildcard prevents B-tree index use. (Postgres can use trigram indexes for this; not default.) 3. **OR across different columns** — Postgres may need to scan both indexes. UNION ALL of two queries is often faster. 4. **NULL in indexed columns** — Postgres indexes do include NULLs (unlike some other DBs), but `WHERE col IS NULL` may or may not use the index depending on stats. ## Takeaway You don't manage indexes, but you do write queries that either use them or defeat them. The six rules above let you write index-friendly SQL on the first try. When you need a new index, asking for it precisely (with column order and INCLUDE list) gets you taken seriously.