A search endpoint uses WHERE (:city IS NULL OR city = :city) for each optional filter — what does that cost, and how do you fix it?

## The shape and why it is tempting A search screen has six optional filters. Writing six statements looks like duplication, so the "clever" single statement appears: ```sql SELECT * FROM customers WHERE (:city IS NULL OR city = :city) AND (:status IS NULL OR status = :status) AND (:country IS NULL OR country = :country); ``` It is genuinely one statement, it is parameterised, and it returns the right rows. The problem is entirely about access paths. ## Why no index gets used An index seek needs a value range derived from the predicate. `city = :city` supplies one. `(:city IS NULL OR city = :city)` does not: the row qualifies either when it matches or when the parameter is absent, and "the parameter is absent" says nothing about `city` at all. The engine must produce a plan that is correct for every parameter combination, and the only access path correct for all of them is one that examines the rows and evaluates the expression. With one such filter you might still get lucky through another predicate; with all filters optional there is nothing left to anchor on. There is a second-order effect that senior candidates should name: the single statement text means one shared plan across callers with wildly different filter combinations. A caller who supplied a highly selective filter is served by whatever plan was appropriate for someone else's filter set. ## The COALESCE variant is worse The compact spelling looks smarter and quietly loses rows: ```sql WHERE city = COALESCE(:city, city) -- silently drops NULL cities ``` When `:city` is NULL the predicate becomes `city = city`, which is **unknown** for any row whose `city` is NULL, and `WHERE` keeps only rows that are true. So the "no filter supplied" case drops every row with a missing city — a correctness bug that survives review because the expression reads like a default. It also wraps the column in a function, so even the supplied-parameter case has nothing bare to seek on. ## The fix: build the statement from the supplied filters Send only the predicates that exist: ```sql -- caller supplied city and status only SELECT * FROM customers WHERE city = ? AND status = ?; ``` Each predicate is a bare-column equality, so an index on `(city, status)` — or on either column — can be used, and the optimizer can estimate row counts for the filters actually present. Two disciplines make this safe and sustainable: **Allowlist the columns and operators.** The statement is assembled from a fixed map of filter name to column and comparison; only the *values* are ever bound as parameters. Nothing user-supplied reaches the SQL text. **Bound the combinatorics.** Six independent optional filters imply sixty-four possible statements. In practice the distribution is heavily skewed and a handful of combinations serve most traffic; keep the filter list short, and prefer a design where one selective filter is mandatory. A search that must be able to scan the whole table with no filter at all is a requirement worth challenging. ## Other mitigations and their limits **A required anchor.** Many real endpoints already have one: a tenant id, an owner, a date window. If every variant carries it, every variant can start from a composite index leading with that column, and the optional filters become residual conditions on a small row set. This is often the cheapest fix in a multi-tenant system. **Separate statements for the common cases.** Hand-write the three or four combinations that dominate traffic and let the generic form serve the tail. Less elegant, entirely predictable. **Engine features.** Some engines offer a statement-level option to re-optimise per execution rather than reuse a shared plan, which addresses the shared-plan half of the problem but not the unsargable-predicate half. The spelling is dialect-specific, so treat it as a local mitigation rather than the portable answer. **Union of branches.** Splitting the disjunction into `UNION` branches, as you would for an ordinary `OR` across columns, does not scale here: the branch count grows with the number of optional filters and the statement becomes unmaintainable. ## Diagnosing it in production The fingerprint is distinctive: one statement dominating database time, latency that barely varies with how selective the caller's filters were, and plans showing a full scan with a long list of filter conditions. Compare a hand-written statement for one common filter combination against the generic one on the same inputs; if the hand-written one is orders of magnitude faster, the generic predicate is the cause and the row estimates in the generic plan will usually be visibly wrong. ## How to answer State the mechanism first (a parameter-dependent disjunction yields no seekable range, and one plan must serve every combination), then the correctness trap in the `COALESCE` spelling, then the fix — assemble from supplied filters, allowlist the columns, bind the values, bound the variants, prefer a mandatory selective anchor. Mention per-execution recompilation as an engine-specific mitigation rather than the design.