Filtering rows: WHERE, precedence, and sargability

Precedence: AND binds tighter than OR

WHERE evaluates a boolean expression per row. The trap is precedence: AND binds tighter than OR, exactly like * binds tighter than +. So this:

SELECT * FROM orders
WHERE status = 'paid' OR status = 'shipped' AND total > 100;

does not mean “(paid or shipped) and total over 100”. It means status = 'paid' OR (status = 'shipped' AND total > 100) — every paid order comes back regardless of total, which is almost never what was intended. The fix is mechanical and non-negotiable in review: parenthesize any mixed AND/OR.

SELECT * FROM orders
WHERE (status = 'paid' OR status = 'shipped') AND total > 100;

NOT binds tightest of the three. When you mix all three, the safe habit is: never trust precedence, always group with parentheses. A misplaced precedence bug doesn’t error — it silently returns the wrong rows, which is the worst kind of bug.

The convenience predicates: BETWEEN, IN, LIKE/ILIKE

• BETWEEN a AND b is inclusive on both ends: total BETWEEN 100 AND 200 is total >= 100 AND total <= 200. The classic mistake is using BETWEEN on timestamps — created_at BETWEEN '2026-01-01' AND '2026-01-31' excludes almost all of Jan 31 because the upper bound is midnight. For time ranges prefer a half-open interval: created_at >= '2026-01-01' AND created_at < '2026-02-01'.
• IN (…) is a tidy OR chain: status IN ('paid','shipped'). (IN with a subquery and NULL has a famous trap — that’s the next lesson.)
• LIKE does pattern matching: % = any run of characters, _ = one character. ILIKE is the case-insensitive version (a Postgres extension).

-- users(id, email, display_name, created_at)
SELECT id, email FROM users
WHERE email LIKE 'support@%'              -- anchored prefix: index-friendly
  AND created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';         -- half-open range, no BETWEEN trap

Sargability: the word that explains the 900× gap

A predicate is sargable (Search-ARGument-able) if the planner can use it to drive an index instead of scanning every row. The rule of thumb: the indexed column must appear bare on one side, compared to a constant. The moment you wrap the column in a function or transform it, the B-tree — which is sorted on the raw column values — becomes useless, because the index has no idea what lower(email) sorts like.

Two non-sargable patterns dominate production incidents:

1. A function on the column. WHERE lower(email) = 'a@b.com' can’t use the index on email, because the index stores email, not lower(email). The fix is an expression index that indexes exactly what you query: CREATE INDEX ON users (lower(email));. Now lower(email) = … is sargable again. (Index internals — B-trees, leading-column rules, expression indexes — are taught in depth by the databases track’s indexes chapter; here you only need the predicate-shape consequence.)
2. A leading wildcard in LIKE. WHERE email LIKE 'abc%' is sargable — the prefix abc pins a contiguous range in the sorted B-tree, so Postgres walks straight to it. WHERE email LIKE '%abc' is not — there’s no fixed prefix to seek on, so every value must be checked. Leading-wildcard search at scale needs a different tool (a trigram GIN index or full-text search), not a B-tree.

Together these two patterns account for the majority of “the index exists but isn’t used” bugs. When you see a query that should be fast but isn’t, checking whether the column appears bare in WHERE is the first thing to do — before adding new indexes.

Parameterize — for safety AND for plan reuse

Never build a WHERE value by string-concatenating user input. WHERE email = '" + input + "' is the textbook SQL-injection hole: an input of ' OR '1'='1 turns your filter into a tautology that returns the whole table. The fix is parameterized queries (bind parameters / prepared statements): the value travels separately from the SQL text, so it can never change the query’s structure.

-- Parameterized: the $1 placeholder is bound to the value by the driver,
-- never interpolated into the SQL string. Injection becomes impossible.
SELECT id, email FROM users WHERE email = $1;

Parameterization also lets Postgres cache and reuse the parsed/planned statement across calls, which removes parse and plan overhead on hot paths. Safety and performance point the same way: bind, don’t concatenate.