skip to content

A search builder with eight optional filters emits many distinct statement shapes - what does that shape count cost you in practice?

level: seniorimportance: should knowfreq 50%

answer

  1. combinatorial, not linear
  2. two to the n, times the sorts
  3. you reviewed one member of a family
  4. measure which signatures actually occur
  5. assert emitted text over a subset matrix

basics

~20 s

Eight independent optional filters give 256 where-clause shapes before ordering multiplies them. Nobody has read most of them, each is compiled separately by the engine, index coverage differs per shape, and the failing combination is usually one no test ever assembled.

solid answer

~50 s

The count is combinatorial, not linear: eight independent optional filters give 2^8 = 256 predicate combinations, and each sort option and direction multiplies that again. Three costs follow. **Review**: the SQL you inspected is one shape out of hundreds, so reasoning about *the* query is meaningless. **Server side**: each distinct statement text is compiled and cached on its own, and how the engine handles that population is a database-side concern with real limits. **Correctness**: the defect lives in a combination nobody assembled, so tests that exercise one filter at a time prove very little. The response is to measure which combinations actually occur - usually a handful carry nearly all traffic - then pin those, require at least one selective filter, retire filters nobody sends, and test the builder's *output* over a generated matrix of subsets rather than testing one query.

go deeper

for a junior

Take away the arithmetic: independent optional filters combine, so a handful of them means hundreds of possible statements rather than one you can read.

for a middle

Explain the three consequences - unreviewed text, one compiled form per distinct text, and index coverage that differs per combination - and why testing one filter at a time misses the defect.

for a senior

Show the operating loop: record a signature per call, pin the dominant combinations, require a selective filter to bound the worst case, and assert emitted text plus bind list over a matrix of subsets.

for a principal

Frame the filter set as a surface with a per-entry cost, and decide deliberately how much of the family the service will promise to serve well rather than letting the builder promise all of it.

## Count the shapes before arguing about them A builder with **n** independent optional filters can emit `2^n` distinct predicate combinations, because each filter is independently present or absent. At n = 8 that is 256. Multiply by the ordering options - four sort fields in two directions is eight - and the builder can produce roughly two thousand distinct statement texts. Add a set filter whose in-list length changes the text, and each distinct list size is another shape. The number matters because most reasoning about a dynamic query silently assumes there is one. There is not. There is a **family** of statements, and every claim about performance, index use or correctness is a claim about a member of that family, usually the one that happened to be in front of you. ## What the count actually costs - **Nobody has read most of them.** A reviewer looking at the builder sees fragments; a reviewer looking at a log sees one assembly. The pathological shape - the one where two filters together drive a scan - is statistically unlikely to be either. - **Each distinct text is compiled on its own.** Engines key their compiled-plan storage on the statement text, so a family of hundreds of shapes is a population of entries rather than one. What that does to reuse, eviction and CPU is a database-side subject with its own depth; the number the application owns is *how many shapes it can emit* against *how few actually occur*. - **Index coverage is per shape, not per builder.** An index that serves the filter-on-owner shape may be useless for owner-plus-date-range, and the composed query gives you no single place where that is visible. - **The failing combination was never assembled.** Unit tests usually cover each filter alone, because that is what the code reads like. Production assembles subsets, and the interesting bug - a missing parenthesis around an OR group, a join added twice, a predicate that contradicts another - only shows up in a subset. - **Support conversations get harder.** *The search is slow* is not a reproducible statement until you know which combination the user sent, which means the combination has to be recorded. ## Shrink the space, do not just tolerate it 1. **Measure the real distribution.** Record a normalised signature per call - the sorted list of filter tokens plus the sort token. Almost always a handful of signatures carry the great majority of traffic, and a long tail is used a few times a week. 2. **Pin the head.** For the two or three dominant signatures, a hand-written statement, tuned and indexed and reviewed like ordinary code, is often better than whatever the builder assembles. The builder stays for the tail. 3. **Retire the tail you can.** Filters that nobody sends are pure shape count. Pre-production or not, an unused filter is a commitment to shapes and indexes you get nothing back for. 4. **Require selectivity.** Refuse a request with no filter at all, or demand at least one filter from a *selective* group (tenant, owner, an indexed date range). This turns an unbounded family into one whose worst member is still bounded. 5. **Cap the set filters.** A maximum in-list length caps both statement size and the number of distinct shapes list length can generate. | Mitigation | What it buys | What it costs | |---|---|---| | Pin the top signatures | Reviewed, indexable statements for most traffic | Two code paths to keep in step | | Require a selective filter | A bounded worst case | A request shape callers can no longer send | | Retire unused filters | Fewer shapes, fewer indexes | A contract change for callers | | Cap in-list length | Bounded text size and shape count | Callers must page their own lists | ## Test the builder, not one query The testable unit is the **assembly**, and it is cheap to test because it needs no engine: - Assert the **emitted text plus the ordered bind list** over a generated matrix of filter subsets - all subsets up to size three, plus the full set, catches grouping and separator defects fast. - Property-style checks over random subsets are effective here: the invariants (*every supplied filter contributes exactly one predicate*, *no bind value is ever absent from the list*, *the unconditional predicates are present in every emitted shape*) hold for every subset by construction. - Snapshot the emitted text for the pinned signatures, so a change to the builder that alters the head of the traffic is visible in a diff. - Keep a small number of end-to-end assertions for row-level correctness, especially where a filter adds a join. The shape count never becomes small. What changes is whether you know the number, know which members actually run, and have made the worst member survivable.

  • How would you record which filter combinations production actually uses?
    Log a normalised signature per call - the sorted list of supplied filter tokens plus the sort token - as a low-cardinality label rather than the statement text. It costs almost nothing, groups cleanly, and turns the shape family into a ranked list you can act on: pin the head, retire the tail.
  • Why does requiring at least one selective filter help more than adding indexes?
    Indexes are per shape, and the family grows faster than you can index it. A required selective filter bounds every member at once: whatever else the caller combines, the driving access path is one you chose. It is a contract restriction, which is why it belongs in the API design rather than in tuning.
  • Does a builder with a single reusable statement shape avoid this problem?
    It trades one problem for another. A catch-all shape keeps the family at size one, but then a single compiled form serves every combination, and no member gets an access path chosen for it. Which trade is right depends on the workload; the engine-side consequences of that reuse are a database-side topic.

saying these in an interview costs you the question

  • Counts the filters instead of their combinations when sizing the problem.
  • Reasons about the query as if the builder emitted only one.
  • Tests each filter alone and calls the combinations covered.
  • Assumes an index that serves one shape serves the rest.
  • Accepts a request with no filter at all on a large table.
  • Never records which filter combinations callers actually send.