Recap — 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

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

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:

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

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 <10% of rows).
3. The query runs frequently or on a hot path (dashboards, customer-facing).

Then ask for CREATE INDEX <name> ON <table>(<column>);, 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.