Query Anti-Patterns: Unbounded Regex, Huge $in Lists, Client Filtering, and Accidental Collection Scans

Learning outcomes

A query can be logically correct and still be operationally dangerous. AtlasMart's incident review found four recurring patterns: an unbounded regex used as pseudo-search, a huge $in list generated from another system, application code that fetched everything and filtered in Python, and a new predicate shipped without an index or explain review. This lesson teaches how to detect those patterns with explain("executionStats"), repair them, and document what the evidence proves.

1. Unbounded regex is a poor substitute for search

AtlasMart's support UI once used {name:/.*cam.*/i} for substring search. It works functionally on a tiny collection, but the leading wildcard gives the B-tree no literal prefix to bound, and case-insensitive regex has additional limitations. A case-sensitive prefix such as /^AtlasCam/ is fundamentally different because the engine can derive a prefix range from the index. Neither form provides relevance scoring, analyzers, stemming, fuzzy matching, or the operational model of MongoDB Search.

db.query_perf.drop()const docs=[];for (let i=0;i<3000;i++) { docs.push({_id:i,sku:`sku-${String(i).padStart(4,"0")}`,  name:i%10===0?`AtlasCam ${i}`:`Product ${i}`,  category:i%3===0?"camera":"accessory",priceCents:1000+(i%200)*25,active:i%7!==0});}db.query_perf.insertMany(docs); db.query_perf.createIndex({name:1});for (const [label,q] of [["prefix",{name:/^AtlasCam/}],["substring",{name:/Cam/i}]]) { const e=db.query_perf.explain("executionStats").find(q,{_id:1}).limit(20); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,  docs:e.executionStats.totalDocsExamined,winningPlan:e.queryPlanner.winningPlan});}

Do not run deliberately pathological backtracking expressions against production merely to “test” them. Use isolated fixtures, query timeouts, and representative patterns. The repair for a true search workload is often a search index or a different product path, not a cleverer regex.

2. Huge $in lists can turn one query into hundreds of probes

An indexed $in predicate can be excellent for a small bounded set of IDs. The cost changes when an API sends hundreds or thousands of values: the server must process the list, perform many index/range operations, allocate command/query structures, and return a union of candidates. MongoDB's documentation specifically warns that hundreds of parameters can hurt performance and recommends limiting the list to tens where practical.

db.query_perf.createIndex({sku:1})const small=["sku-0001","sku-0042","sku-0100","sku-0200"];const large=Array.from({length:600},(_,i)=>`sku-${String(i).padStart(4,"0")}`);for (const [label,values] of [["small",small],["large",large]]) { const q={sku:{$in:values}}; const e=db.query_perf.explain("executionStats").find(q,{_id:1,sku:1}).limit(50); printjson({label,inputValues:values.length,nReturned:e.executionStats.nReturned,  keys:e.executionStats.totalKeysExamined,docs:e.executionStats.totalDocsExamined});}

A large list is not automatically wrong. It may be the correct bounded batch for a background job. The production question is whether the shape is frequent, user-controlled, latency-sensitive, and whether another ownership model, temporary staging collection, join-like pipeline, or batch API would be safer.

3. Client-side filtering wastes network and bypasses server optimization

The anti-pattern list(coll.find({})) followed by a Python list comprehension transfers every document to the application and only then rejects most of them. MongoDB cannot use a selective index to avoid that transfer because the server was asked for everything. Projection suffers the same issue: fetching whole documents and dropping fields in Python wastes bandwidth and can expose fields the endpoint never needed.

from pymongo import MongoClientclient = MongoClient("mongodb://127.0.0.1:27036/?directConnection=true", serverSelectionTimeoutMS=3000)coll = client.atlasmart.query_perf# Wrong shape: fetch all, then filter locally.all_docs = list(coll.find({}))local = [d for d in all_docs if d.get("active") and d.get("category") == "camera" and d.get("priceCents", 0) < 2500]print("client_received", len(all_docs), "client_kept", len(local))# Safer shape: predicate + projection + deterministic order + bound on the server.flt = {"active": True, "category": "camera", "priceCents": {"$lt": 2500}}server = list(coll.find(flt, {"_id":1,"sku":1,"priceCents":1}).sort([("priceCents",1),("_id",1)]).limit(50))print("server_returned", len(server))client.close()

The output counts are the simplest evidence: the wrong version receives all 3,000 fixtures while keeping a small subset; the server-side version only transmits matching projected documents. In production, command monitoring/APM, network bytes, latency percentiles, and server query metrics provide richer evidence.

4. A collection scan is evidence to investigate, not an automatic crime

COLLSCAN means the winning plan scans collection records rather than using an index for the main access path. On a tiny collection or a query that returns most records, a collection scan can be rational. On a large latency-sensitive API where docsExamined dwarfs nReturned, it is a red flag. Conversely, an IXSCAN is not automatically efficient if it examines huge numbers of keys for a low-selectivity predicate such as broad $nin.

db.query_perf.createIndex({category:1,priceCents:1})const good={category:"camera",priceCents:{$lt:1800}};const broad={active:{$nin:[false]}};for (const [label,q] of [["selective",good],["broad-nin",broad]]) { const e=db.query_perf.explain("executionStats").find(q,{_id:1}).limit(100); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,  docs:e.executionStats.totalDocsExamined,winningPlan:e.queryPlanner.winningPlan});}

Use maxTimeMS as a bounded-failure guardrail for appropriate operations, not as a performance fix. A query that repeatedly reaches the deadline still needs a better predicate, index, data model, workload boundary, or search architecture.

5. AtlasMart query-review capstone

docker rm -f atlasmart-mongo-ch04-l5docker run --name atlasmart-mongo-ch04-l5 -p 127.0.0.1:27036:27017 -d mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimdocker logs atlasmart-mongo-ch04-l5 --tail 25

mongosh "mongodb://127.0.0.1:27036/atlasmart?directConnection=true" --quiet --eval 'db.query_perf.drop(); const docs=[];for(let i=0;i<3000;i++){docs.push({_id:i,sku:`sku-${String(i).padStart(4,"0")}`,name:i%10===0?`AtlasCam ${i}`:`Product ${i}`,category:i%3===0?"camera":"accessory",priceCents:1000+(i%200)*25,active:i%7!==0});}db.query_perf.insertMany(docs);db.query_perf.createIndex({name:1}); db.query_perf.createIndex({sku:1}); db.query_perf.createIndex({category:1,priceCents:1});const tests=[ ["prefix-regex",{name:/^AtlasCam/}], ["substring-regex",{name:/Cam/i}], ["selective-range",{category:"camera",priceCents:{$lt:1800}}], ["broad-nin",{active:{$nin:[false]}}]];for(const [label,q] of tests){const e=db.query_perf.explain("executionStats").find(q,{_id:1}).limit(100); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,docs:e.executionStats.totalDocsExamined,plan:e.queryPlanner.winningPlan});}const ids=Array.from({length:600},(_,i)=>`sku-${String(i).padStart(4,"0")}`);const ie=db.query_perf.explain("executionStats").find({sku:{$in:ids}},{_id:1}).limit(50); printjson({hugeIn:ids.length,nReturned:ie.executionStats.nReturned,keys:ie.executionStats.totalKeysExamined,docs:ie.executionStats.totalDocsExamined});' 

Verification checklist

• The lab creates a deterministic 3,000-document fixture and explicit indexes before measuring.
• Prefix and substring regex queries are compared through execution statistics instead of assumed latency.
• The 600-value $in query reports its input cardinality and examined-key/document counts.
• The selective compound-index query and broad $nin query demonstrate why selectivity matters.
• The Python example distinguishes documents received by the client from documents actually needed.
• No pathological regex, production URI, global server setting, or external search service is required.

docker rm -f atlasmart-mongo-ch04-l5

Chapter 05 now applies the same precision to state changes: update operators, array mutations, upserts, find-and-modify operations, and bulk writes.

Authoritative references

• MongoDB release notes — Official current stable server series and patch notes.
• MongoDB 8.3 release notes — Official 8.3 patch history; 8.3.8 is the latest released patch at review time.
• MongoDB query documents — Official find/query behavior and cursor semantics.
• MongoDB query optimization — Official selectivity, index, and explain guidance.
• PyMongo query documents — Official Python driver query-filter behavior.
• $regex query operator — Regular-expression syntax and documented index-performance behavior.
• $in query operator — Membership semantics and guidance about large parameter arrays.
• Query optimization — Selectivity, covered queries, indexes, and efficient query principles.
• Explain results — Interpret executionStats, examined keys/documents, and winning plans.
• PyMongo query documents — Official Python driver filtering/query patterns.