p24-infra Docs

mongodb-compound-wildcard-indexes

5 min read

Playbook: MongoDB Compound Wildcard Indexes — Pinbox24 Search Performance

Issue: #1198
Applied: 2026-06-24
Operator: Claude (p24-infra admin role)
Target: PRIMARY bms-2 (145.239.133.104:27017), rs0

What was done

Four compound wildcard indexes were created on MongoDB rs0 PRIMARY (bms-2) to address slow $or regex search queries affecting RESO EUROPA (w3_db) and Keller (w4_db).

Root cause: the Pinbox24 “search records” feature issues $or queries with 7–100+ regex conditions across flat/nested fields. After {officeId, regId} narrows to ~23,800 documents, MongoDB evaluates every regex against every document (full in-memory scan), causing 60–223 second queries.

Indexes created

CollectionIndex nameKeyType
w4_db.regRecordsp24_search_recorddata_wildcard{officeId:1, regId:1, deleted:1, "recordData.$**":1}Path-scoped compound wildcard
w4_db.businessaddressbooksp24_addr_recorddata_wildcard{officeId:1, regId:1, "recordData.$**":1}Path-scoped compound wildcard
w3_db.regRecordsp24_search_compound_wildcard{officeId:1, regId:1, "$**":1} with wildcardProjection (37 fields)Global compound wildcard + projection
w3_db.businessAddressbookp24_addr_compound_wildcard{officeId:1, regId:1, "$**":1} with wildcardProjection (12 fields)Global compound wildcard + projection
w3_db.businessAddressbookp24_addr_AND_companyAdress_companyCity{officeId:1, regId:1, companyAdress:1, companyCity:1}DROPPED — for non-anchored regex AND queries, random I/O on sorted companyAdress keys is slower (4195ms) than the existing officeId index (1452ms) when results are returned. Covered query only helped for 0-result case.
w3_db.businessAddressbookp24_addr_city_adress{officeId:1, regId:1, companyCity:1, companyAdress:1}AND query: filters two regex conditions at index-key level → fetches only matching docs. Combined with planCacheSetFilter (see below).

w3_db.regRecords wildcardProjection (exact fields)

{
  "LettersCosts": 1, "Uwagi": 1, "ZPO": 1, "adresopis": 1, "barcode": 1,
  "boxBarcode": 1, "budynek": 1, "case_desc": 1, "clientAddress": 1, "clientCode": 1,
  "companyAdress": 1, "companyCity": 1, "companyCountry": 1, "companyName": 1,
  "concerns": 1, "creatorLogin": 1, "doc_code": 1, "en_status": 1, "forwarded_to": 1,
  "from_who": 1, "importUniqueId": 1, "kategoria": 1, "letterCost": 1, "list": 1,
  "longName": 1, "nadawca": 1, "nr_szkody": 1, "nrpaczki": 1,
  "param36_varchar_inputcompanyName": 1, "postalcode": 1, "processStatusLabel": 1,
  "recordMainDocument": 1, "registredDataTime": 1, "shortName": 1,
  "signatura": 1, "signature": 1, "typlistu": 1
}

w3_db.businessAddressbook wildcardProjection (exact fields)

{
  "companyAdress": 1, "companyCity": 1, "companyContactPerson": 1, "companyName": 1,
  "companyPhone1": 1, "companyPhone2": 1, "email": 1, "longName": 1,
  "postalcode": 1, "registeredDataTime": 1, "rodzaj_danych": 1, "shortName": 1
}

AND query plan pinning — planCacheSetFilter (w3_db.businessAddressbook)

Why needed: MongoDB’s cost model does not correctly account for index-key-level regex filtering. For non-anchored regex queries on {officeId, regId, companyAdress, companyCity}, the planner prefers the old officeId index ({officeId,regId,postalcode}) and fetches ALL 903k documents before applying regex. The p24_addr_city_adress index is 5.6× faster (691ms vs 3847ms) and fetches only matching docs (157 docs vs 903k). planCacheSetFilter pins the better plan.

⚠ IMPORTANT: planCacheSetFilter is in-memory only — lost after mongod restart. Re-apply after any MongoDB restart on bms-2. See “After MongoDB restart” section below.

Performance results (AND query — {officeId, regId, companyAdress regex, companyCity regex})

ScenarioTimeDocs fetchedIndex
Before (auto)3847ms903,238officeId ({officeId,regId,postalcode})
After (pinned)691ms157p24_addr_city_adress ({officeId,regId,companyCity,companyAdress})
Improvement5.6×5747× fewer—

Apply the filter (run on PRIMARY bms-2 after each mongod restart)

ssh ubuntu@145.239.133.104
mongosh "mongodb://ADMIN_USER:ADMIN_PASS@127.0.0.1:27017/admin?authSource=admin" --quiet --eval '
var r = db.getSiblingDB("w3_db").runCommand({
  planCacheSetFilter: "businessAddressbook",
  query: {
    officeId:      "",
    regId:         "",
    companyAdress: {$regex: "", $options: "i"},
    companyCity:   {$regex: "", $options: "i"}
  },
  indexes: [{officeId: 1, regId: 1, companyCity: 1, companyAdress: 1}]
});
print("ok=" + r.ok);
 
// Verify
var fl = db.getSiblingDB("w3_db").runCommand({planCacheListFilters: "businessAddressbook"});
print("filters: " + fl.filters.length + " (expect 1)");
db.getSiblingDB("w3_db").businessAddressbook.getPlanCache().clear();
'

Key detail: {$regex: "", $options: "i"} (with "i") MUST be used — $options: "" does NOT match the query shape because MongoDB treats different $options values as different plan cache key shapes. Queries from Pinbox24 always use $options: "i".

Verify the filter is active

mongosh "mongodb://ADMIN_USER:ADMIN_PASS@127.0.0.1:27017/admin?authSource=admin" --quiet --eval '
var ep = db.getSiblingDB("w3_db").businessAddressbook.find({
  officeId: "590c6d334704d811efc9fd5a", regId: "59f22ea506d9cf3d52a53024",
  companyAdress: {$regex: "ul", $options: "i"}, companyCity: {$regex: "Lublin", $options: "i"}
}).explain("executionStats");
print("indexFilterSet: " + ep.queryPlanner.indexFilterSet);        // must be true
print("winner: " + ep.queryPlanner.winningPlan.inputStage.indexName);  // must be p24_addr_city_adress
print("docs: " + ep.executionStats.totalDocsExamined + " (expect <<903238)");
'

Remove the filter (rollback)

mongosh "mongodb://ADMIN_USER:ADMIN_PASS@127.0.0.1:27017/admin?authSource=admin" --quiet --eval '
db.getSiblingDB("w3_db").runCommand({planCacheClearFilters: "businessAddressbook"});
db.getSiblingDB("w3_db").businessAddressbook.getPlanCache().clear();
print("filter removed — planner will revert to auto selection");
'

After MongoDB restart procedure

After any mongod restart on bms-2, run the “Apply the filter” command above. To check if the filter is still active: planCacheListFilters — if filters.length === 0, re-apply.

How to verify indexes are active

SSH to bms-2 (PRIMARY) and run:

ssh ubuntu@145.239.133.104
mongosh "mongodb://ADMIN_USER:ADMIN_PASS@127.0.0.1:27017/admin?authSource=admin" --quiet --eval '
var targets = [
  ["w4_db","regRecords","p24_search_recorddata_wildcard"],
  ["w4_db","businessaddressbooks","p24_addr_recorddata_wildcard"],
  ["w3_db","regRecords","p24_search_compound_wildcard"],
  ["w3_db","businessAddressbook","p24_addr_compound_wildcard"]
];
targets.forEach(function(t) {
  var found = db.getSiblingDB(t[0]).getCollection(t[1]).getIndexes().filter(function(i){ return i.name === t[2]; });
  print(t[0]+"."+t[1]+" ["+t[2]+"]: " + (found.length ? "EXISTS" : "MISSING"));
});
'

Expected output: all four lines show EXISTS.

Check replication to bms-3 (SECONDARY):

ssh ubuntu@51.68.155.224
# same mongosh command — indexes replicate automatically from PRIMARY

How to reverse (drop indexes)

Run on PRIMARY (bms-2) only — replicates to bms-3 automatically:

ssh ubuntu@145.239.133.104
mongosh "mongodb://ADMIN_USER:ADMIN_PASS@127.0.0.1:27017/admin?authSource=admin" --quiet --eval '
db.getSiblingDB("w4_db").regRecords.dropIndex("p24_search_recorddata_wildcard");
db.getSiblingDB("w4_db").businessaddressbooks.dropIndex("p24_addr_recorddata_wildcard");
db.getSiblingDB("w3_db").regRecords.dropIndex("p24_search_compound_wildcard");
db.getSiblingDB("w3_db").businessAddressbook.dropIndex("p24_addr_compound_wildcard");
db.getSiblingDB("w3_db").businessAddressbook.dropIndex("p24_addr_AND_companyAdress_companyCity");
db.getSiblingDB("w3_db").businessAddressbook.dropIndex("p24_addr_city_adress");
db.getSiblingDB("w3_db").runCommand({planCacheClearFilters: "businessAddressbook"});
print("done");
'

dropIndex is instant (metadata operation) — the index files are removed in the background.

Side effects and risks

RiskDetails
Write overheadEach write to indexed collections now updates 1 extra index entry. Expected impact: minimal for Pinbox24 (read-heavy search workload).
Disk usageWildcard indexes on 2.5M docs (w4_db.regRecords) may use 0.5–2 GB extra. Monitor df -h on bms-2.
Index conflictw3_db.businessAddressbook already has several compound indexes on companyName/companyCity/postalcode. The new wildcard is additive; both exist in parallel.
Replication lagbms-3 applies the index build through replication — brief lag expected during build propagation. No query impact.

Expected improvement

MongoDB’s OR index union: when the query planner sees {officeId, regId} prefix + $or with regex conditions, it can use each wildcard sub-path as a separate index scan branch and union the results — instead of loading 23,800 full documents and evaluating all 34 regexes in memory.

Worst case for non-anchored regex (e.g. /keller/i) remains a per-index partial scan, but the working set shrinks from “all documents for this office” to “documents that might match this field” — reducing both CPU and RAM pressure.

Trigger / prevention

These indexes address issue #1198 but do NOT fix the root cause in the application:

  1. Application fix needed (Pinbox24): Replace $or regex search with a MongoDB text index + $text operator, or reduce $limit: 10000 to $limit: 500 before the $facet stage.
  2. Monitor: Once Mezmo agent is deployed on bms-2+bms-3 (issue #1196), slow query spikes will appear in Grafana automatically.

Admin credentials

MONGODB_RS0_ADMIN_USER and MONGODB_RS0_ADMIN_PASSWORD — decrypt from SOPS:

$env:SOPS_AGE_KEY_FILE = "C:\Users\konar\.age\p24-infra-keys.txt"
sops --decrypt --input-type dotenv --output-type dotenv secrets\monitoring.env.sops | Select-String "MONGODB_RS0_ADMIN"

Graph View

Backlinks