Index Selection for Complex Queries

Multi-Column WHERE with Indices

Real queries rarely filter on just one column. Once your planner can choose between table scans and index scans (previous two lessons), the next question is: what happens when WHERE has two conditions, and only one of them is indexed?

SELECT * FROM users WHERE age > 20 AND name = 'Alice'

The split: access predicate vs. filter predicate

The planner's job is to split a compound predicate into two roles:

• Access predicate — the condition used to find candidate rows via an index. Here, name = 'Alice' is an equality test on an indexed column, so it drives an index seek straight to the matching rowids.
• Residual filter — everything else, applied after the candidate rows are fetched, by directly evaluating the condition on each candidate row in memory. age > 20 isn't serviced by the index at all — it's just a cheap post-check on however many rows the index scan already narrowed things down to.

.explain SELECT * FROM users WHERE age > 20 AND name = 'Alice'
→ SEARCH TABLE users USING INDEX idx_name (name='Alice') FILTER (age>20)

Compare that to what would happen without an index on name: the planner has no fast access path at all, so it must fall back to a full table scan with both conditions applied as filters on every row.

Why this ordering matters

Picking name='Alice' as the access predicate first is far better than scanning every row and checking age > 20 first, if the index narrows the candidate set a lot — going from "check age on every row, then check name on the survivors" to "jump straight to the ~handful of rows named Alice, then just check their age" can be an enormous win. The general principle: use whatever predicate has an index to shrink the candidate set as early as possible, then evaluate cheaper/unindexed predicates on the smaller remaining set.

Implementation sketch

1. Parse the WHERE clause into a list of individual conditions (splitting on AND).
2. For each condition, check whether it's an equality test on a column with an index.
3. If any condition qualifies, pick one (per your rules — often "the first indexed one" or "the most selective one") as the access predicate; index-seek on it to get candidate rowids.
4. Fetch those rows, then apply every other condition as an in-memory filter.
5. If no condition is indexed, fall back to a table scan with the full compound predicate as a filter.
6. Format the .explain output to show both the access path and the residual filter, exactly matching the expected syntax.

Edge cases to watch

• Both columns indexed — decide (and be consistent about) which one becomes the access predicate. Real planners use cost/selectivity estimates for this (the subject of the next lesson); here a simple deterministic rule (e.g. "leftmost indexed predicate") is usually enough.
• Predicate order in the SQL shouldn't matter to correctness (age > 20 AND name = 'Alice' must behave the same as name = 'Alice' AND age > 20), even if your .explain output happens to normalize the display order.
• A residual filter that itself would benefit from an index on a different query shape (e.g. a range predicate) still can't use an equality-only index — don't try to force it.
• Zero-condition or single-condition WHERE clauses should degrade cleanly to the simpler cases you already built.