MongoDB Indexing Playbook (Profiler + Slow Query Analysis)¶

This document explains how we identify slow MongoDB queries, group similar query shapes, and turn findings into index improvements using system.profile.

Be careful in production

The MongoDB profiler adds overhead and can grow the system.profile collection. Keep it enabled only for short, targeted windows and disable it when done.

Purpose and Scope¶

Enable/disable MongoDB Profiler to capture slow operations.
Inspect slow operations in system.profile:
List individual worst offenders.
Group similar query shapes (filter/sort/limit/plan) to prioritize work.
Convert grouped findings into actionable index work.

Prerequisites¶

Access to mongosh with appropriate permissions.
List of databases to inspect.
A slow threshold (example: slowms=1000 for 1 second).

1) Enable Profiler to Capture Slow Queries¶

1.1 Single database: enable/disable¶

// Enable profiling for slow operations (>= 1s)
db.setProfilingLevel(1, { slowms: 1000 });

// Disable profiling and show the current status
db.setProfilingLevel(0) && db.getProfilingStatus();

Profiler levels

setProfilingLevel(1, { slowms: X }): records only slow operations.
setProfilingLevel(2): records all operations (generally not recommended for production).

1.2 Multiple databases: print status + apply a change¶

The script below:

Prints current profiler status per DB.
Applies a change (example shown: disable profiler for each DB).

const dbs = [
  "buildStore",
  "notificationStore",
  "distributionStore",
  "licenseStore",
  "reportingStore",
  "storeSubmitStore",
  "resourceStore",
  "enterpriseStore",
  "publishStore",
  "signingIdentityStore",
  "webhookStore",
  "resignStore",
  "agentCacheStore"
];

// 1) Print current profiler settings
print("=== BEFORE ===");
for (const name of dbs) {
  const d = db.getSiblingDB(name);
  const st = d.getProfilingStatus();
  print(name + " => was: " + st.was + " slowms: " + st.slowms + " sampleRate: " + st.sampleRate);
}

// 2) Apply profiler changes (example: disable)
print("=== APPLY ===");
for (const name of dbs) {
  const d = db.getSiblingDB(name);
  d.setProfilingLevel(0); // change to (1, { slowms: 1000 }) when you want to enable
  const st = d.getProfilingStatus();
  print(name + " => now: " + st.was + " slowms: " + st.slowms + " sampleRate: " + st.sampleRate);
}

Enabling across DBs

To enable profiling across DBs for slow ops, replace setProfilingLevel(0) with:

d.setProfilingLevel(1, { slowms: 1000 });

2) List Individual Slow Operations¶

Use this query to see the slowest operations (example: >= 3000ms), including planSummary and the full command payload.

db.system.profile.find(
  { millis: { $gte: 3000 }, op: { $in: ["query","command"] } },
  { ts:1, ns:1, millis:1, planSummary:1, keysExamined:1, docsExamined:1, nreturned:1, client:1, user:1, command:1 }
).sort({ millis: -1 }).limit(10)

What to look at first

planSummary: COLLSCAN vs IXSCAN (and which index)
keysExamined vs docsExamined: indicates scan/selectivity issues
nreturned: large result sets may need limit / pagination
command: filter + sort + limit shape

3) Group Similar Query Shapes (Aggregation)¶

Instead of reading slow queries one by one, group similar operations to prioritize index work.

This pipeline:

Extracts filter keys, sort keys, limit, and some operator info (logTimeOps example).
Builds a compact cmdStr representation.
Groups by a “query shape” tuple:
ns, planSummary, find/aggregate, limit, filterKeys, sortKeys, logTimeOps
Outputs frequency and timing statistics per group.

db.system.profile.aggregate([
  { $match: { millis: { $gte: 1000 }, op: { $in: ["query","command"] } } },

  { $project: {
      ns: 1, millis: 1, planSummary: 1, ts: 1, client: 1, user: 1,
      find: "$command.find",
      limit: "$command.limit",
      command: 1,

      // filter keys array
      filterKeys: {
        $cond: [
          { $ne: ["$command.filter", null] },
          { $map: { input: { $objectToArray: "$command.filter" }, as: "kv", in: "$$kv.k" } },
          []
        ]
      },

      // logTime ops array ($lt, $gte, ...)
      logTimeOps: {
        $cond: [
          { $ne: ["$command.filter.logTime", null] },
          { $map: { input: { $objectToArray: "$command.filter.logTime" }, as: "kv", in: "$$kv.k" } },
          []
        ]
      },

      // sort keys array
      sortKeys: {
        $cond: [
          { $ne: ["$command.sort", null] },
          { $map: { input: { $objectToArray: "$command.sort" }, as: "kv", in: "$$kv.k" } },
          []
        ]
      },

      // ---- Build a command string (join arrays) ----
      cmdStr: {
        $let: {
          vars: {
            dbn: "$command.$db",
            cmdName: { $ifNull: ["$command.find", { $ifNull: ["$command.aggregate", "?"] }] },

            fkStr: {
              $reduce: {
                input: {
                  $cond: [
                    { $ne: ["$command.filter", null] },
                    { $map: { input: { $objectToArray: "$command.filter" }, as: "kv", in: "$$kv.k" } },
                    []
                  ]
                },
                initialValue: "",
                in: { $concat: ["$$value", { $cond: [{ $eq: ["$$value", ""] }, "", ","] }, "$$this"] }
              }
            },

            skStr: {
              $reduce: {
                input: {
                  $cond: [
                    { $ne: ["$command.sort", null] },
                    { $map: { input: { $objectToArray: "$command.sort" }, as: "kv", in: "$$kv.k" } },
                    []
                  ]
                },
                initialValue: "",
                in: { $concat: ["$$value", { $cond: [{ $eq: ["$$value", ""] }, "", ","] }, "$$this"] }
              }
            },

            ltOpsStr: {
              $reduce: {
                input: {
                  $cond: [
                    { $ne: ["$command.filter.logTime", null] },
                    { $map: { input: { $objectToArray: "$command.filter.logTime" }, as: "kv", in: "$$kv.k" } },
                    []
                  ]
                },
                initialValue: "",
                in: { $concat: ["$$value", { $cond: [{ $eq: ["$$value", ""] }, "", ","] }, "$$this"] }
              }
            },

            limStr: { $toString: { $ifNull: ["$command.limit", 0] } }
          },
          in: {
            $concat: [
              "db=", { $ifNull: ["$$dbn", "?"] },
              " cmd=", "$$cmdName",
              " filterKeys=[", "$$fkStr", "]",
              " logTimeOps=[", "$$ltOpsStr", "]",
              " sortKeys=[", "$$skStr", "]",
              " limit=", "$$limStr"
            ]
          }
        }
      }
  }},

  { $group: {
      _id: {
        ns: "$ns",
        planSummary: "$planSummary",
        find: "$find",
        limit: "$limit",
        filterKeys: "$filterKeys",
        logTimeOps: "$logTimeOps",
        sortKeys: "$sortKeys"
      },
      count: { $sum: 1 },
      avgMs: { $avg: "$millis" },
      maxMs: { $max: "$millis" },
      commandSample: { $first: "$cmdStr" },
      commandObjSample: { $first: "$command" },
      sample: { $first: "$$ROOT" }
  }},

  { $sort: { maxMs: -1 } },
  { $limit: 20 }
]).forEach(d => print(JSON.stringify(d)))

How to read the grouped output

_id.ns: DB collection (db.collection)
_id.planSummary: plan summary (COLLSCAN / IXSCAN ...)
_id.filterKeys: filter fields (index candidates)
_id.sortKeys: sort fields (compound index order)
count: frequency (helps prioritization)
avgMs, maxMs: performance impact
commandObjSample: one raw command example (most important)

4) Example Output and Interpretation¶

Example group result (trimmed):

{"_id":{"ns":"notificationStore.ClientNotifications","planSummary":"IXSCAN { organizationId: 1, clientMessageType: 1, deletedBy: 1 }","filterKeys":null,"logTimeOps":null,"sortKeys":null},"count":14,"avgMs":2064.5,"maxMs":6574,"commandSample":null,"commandObjSample":{"aggregate":"ClientNotifications","pipeline":[{"$match":{"$or":[{"organizationId":"...","userId":"..."},{"organizationId":"...","isBroadcastToOrganization":true,"userId":{"$ne":"..."}}],"deletedBy":{"$ne":"..."},"clientMessageType":1}}, ...],"$db":"notificationStore"}}

What this suggests:

An index is being used (IXSCAN), but the query group still has high latency (avg ~2s, max ~6.5s).
The first $match stage in the pipeline drives index selection. In this example, fields like:
organizationId
userId
isBroadcastToOrganization
deletedBy (note $ne)
clientMessageType appear in the match stage.

Aggregate pipelines and index decisions

The highest ROI is usually improving the $match stage selectivity.
$or queries often require indexes that support each branch.
Negative predicates like $ne may reduce index efficiency and sometimes require a query/model adjustment.

5) Turning Findings into Index Work¶

5.1 Confirm with `explain()`¶

Profiler data shows symptoms. Always confirm with explain("executionStats") before committing index changes.

db.getCollection("ClientNotifications")
  .explain("executionStats")
  .aggregate([
    { $match: { /* match from commandObjSample */ } },
    { $project: { /* ... */ } },
    { $group: { /* ... */ } }
  ])

Key signals:

totalKeysExamined vs totalDocsExamined
executionTimeMillis
presence of COLLSCAN, large FETCH, or poor selectivity

5.2 Practical index heuristics¶

Equality filters (field: value) usually come first in a compound index.
Sort fields should be included in the index in the right order if sorting is required.
Range predicates ($gt/$lt/$gte/$lte) typically come later.
With $or, consider each branch separately.
$ne / $nin can limit index usefulness.

5.3 Operational safety¶

Create/modify indexes during low-traffic windows.
Observe the query plans after rollout.
Keep profiler on for a short period to measure impact.
Disable profiling afterwards.

6) Disable Profiler and Wrap Up¶

db.setProfilingLevel(0) && db.getProfilingStatus();

Retention of profiler data

If profiling is enabled for extended periods, system.profile can grow significantly. Keep profiling time-boxed and consider an operational retention strategy.

Appendix: Quick Command Set¶

Enable profiling for slow ops (>= 1s)¶

db.setProfilingLevel(1, { slowms: 1000 });

Disable profiling + status¶

db.setProfilingLevel(0) && db.getProfilingStatus();

Top 10 slow operations (>= 3s)¶

db.system.profile.find(
  { millis: { $gte: 3000 }, op: { $in: ["query","command"] } },
  { ts:1, ns:1, millis:1, planSummary:1, keysExamined:1, docsExamined:1, nreturned:1, client:1, user:1, command:1 }
).sort({ millis: -1 }).limit(10)

Group query shapes (>= 1s)¶

Use the aggregation pipeline in Section 3.

MongoDB Indexing Playbook (Profiler + Slow Query Analysis)¶

Purpose and Scope¶

Prerequisites¶

1) Enable Profiler to Capture Slow Queries¶

1.1 Single database: enable/disable¶

1.2 Multiple databases: print status + apply a change¶

2) List Individual Slow Operations¶

3) Group Similar Query Shapes (Aggregation)¶

4) Example Output and Interpretation¶

5) Turning Findings into Index Work¶

5.1 Confirm with explain()¶

5.2 Practical index heuristics¶

5.3 Operational safety¶

6) Disable Profiler and Wrap Up¶

Appendix: Quick Command Set¶

Enable profiling for slow ops (>= 1s)¶

Disable profiling + status¶

Top 10 slow operations (>= 3s)¶

Group query shapes (>= 1s)¶

5.1 Confirm with `explain()`¶