Read the query plan before adding another index

Not specified by the original author.

TL;DR: Slow endpoint, unknown cause. The symptom to cause path runs through the Query Profiler and Performance Advisor to a suggested index, verified with explain. Confirm Run the query shape with explain("executionStats") in mongosh. Fix Create the suggested index.

The error

an endpoint or job is slow and you suspect the database.

The fix

Slow queries: profiler to index

Symptom: an endpoint or job is slow and you suspect the database.

Diagnose

1. Atlas UI: Performance Advisor. It lists slow query shapes grouped by pattern, with suggested indexes. This is only on M10+ clusters.
2. Cross-check with the Query Profiler (same area): it shows slow operations with key stats for the last few days, no extra cost.
3. Pick the worst shape by total time (frequency x avg duration), not the single slowest one-off.

Confirm

Run the query shape with explain("executionStats") in mongosh. Look at totalDocsExamined vs nReturned. A big ratio means the query scans far more than it returns: missing or wrong index.

Fix

Create the suggested index. The Performance Advisor can create it directly from the UI (one at a time), or run the createIndex in mongosh. Keep read/write ratio in mind: every index taxes writes.

Verify

Re-run explain: totalDocsExamined should collapse toward nReturned, and the stage should be IXSCAN. Then watch the Performance Advisor over the next day to confirm the shape dropped off the slow list.

When to use this

• You are seeing this exact error message; match the block above, not just part of it.
• The failing call matches the scenario in the title: Atlas.
• You want the fastest verified fix before digging through logs.

When not to use this

• Your error text differs from the block above; close cousins often have different causes.
• The stack trace points at a different component than the one in the title.
• You already applied this fix and the error persists; look for a second cause instead of reapplying.

Compatibility

• Not pinned to a specific version; follows current Atlas behavior.

Performance Advisor slow-query monitoring and suggested-index workflow per https://www.mongodb.com/docs/atlas/performance-advisor/; Query Profiler coverage of recent slow operations per the same docs, verified 2026-09-26.

Not specified by the original author.

TL;DR: A collection scan on a large collection is the most common slow-query cause. Explain proves it, a targeted index removes it. Note totalDocsExamined and executionTimeMillis. Confirm The filter fields have no index, or the existing index does not cover the filter (wrong field order, or the query filters on a field the index lacks).

The error

a query is slow; you suspect no index is used.

The fix

Kill COLLSCANs

Symptom: a query is slow; you suspect no index is used.

Diagnose

db.orders.explain("executionStats").find({ status: "pending", createdAt: { $gte: start } })

Read the winning plan. COLLSCAN means every document was examined. Note totalDocsExamined and executionTimeMillis.

Confirm

The filter fields have no index, or the existing index does not cover the filter (wrong field order, or the query filters on a field the index lacks).

Fix

db.orders.createIndex({ status: 1, createdAt: -1 })

Order matters: equality fields first, then sort/range fields. For a sort without a filter, index the sort keys in the sort direction.

Verify

Re-run explain: the stage is now IXSCAN, totalDocsExamined is close to nReturned, and executionTimeMillis dropped. Run the app query and confirm the latency improvement end to end.

When to use this

• You are seeing this exact error message; match the block above, not just part of it.
• The failing call matches the scenario in the title: Atlas.
• You want the fastest verified fix before digging through logs.

When not to use this

• Your error text differs from the block above; close cousins often have different causes.
• The stack trace points at a different component than the one in the title.
• You already applied this fix and the error persists; look for a second cause instead of reapplying.

Compatibility

• Not pinned to a specific version; follows current Atlas behavior.

Explain-based index diagnosis per the MongoDB query optimization docs; Performance Advisor suggested indexes per https://www.mongodb.com/docs/atlas/performance-advisor/, verified 2026-09-26.

Not specified by the original author.

TL;DR

Prefix the query with EXPLAIN (ANALYZE, BUFFERS) to see what the database actually did: which steps ran, how many rows each touched, and where the time went. The usual culprits are sequential scans on big tables (missing index), nested loops over large inputs, and row-count misestimates. Read the plan top-down from the slowest node.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Use this when

• A query is slow and you dont know why
• You want to check whether an index is being used
• Comparing two rewrites of the same query

Not for

• "too many connections" or pooling issues
• Deadlocks
• Wrong results (correctness), the plan wont help

Steps

1. Get the real plan with execution stats:

EXPLAIN (ANALYZE, BUFFERS) SELECT o.* FROM orders o
WHERE o.created_at > '2026-01-01';

Expected output: the plan tree with actual time and row counts per node. Without ANALYZE you get estimates only.

2. Look for sequential scans on large tables:

Seq Scan on orders  (cost=...) (actual rows=2000000)

Expected finding: a Seq Scan over millions of rows where you expected an index. Thats the first thing to fix.

3. Add the missing index and re-check:

CREATE INDEX CONCURRENTLY ON orders (created_at);

Expected output: the plan flips to an Index Scan or Bitmap Heap Scan, time drops.

4. Compare estimated vs actual rows:

rows=10 (actual rows=500000)

Expected finding: a big mismatch means stale statistics. Fix with ANALYZE orders; and re-run EXPLAIN.

5. Watch for nested loop joins with a large inner side:

Nested Loop  (actual rows=1000000)

Expected finding: nested loops are fine for small inputs, terrible for large ones. The planner usually picks hash or merge joins when stats are fresh; stale stats are the common reason it doesnt.

Variant phrasings

how to read postgres explain output

Read inside-out: the most indented nodes run first. actual time is per-loop, multiply by loops for the real cost.

seq scan instead of index scan postgres

Either no usable index exists, the table is tiny (seq scan is genuinely faster), or stats are stale. Check all three in order.

explain analyze buffers meaning

ANALYZE runs the query for real timings. BUFFERS shows cache hits vs disk reads: high disk reads mean the working set exceeds shared_buffers or the OS cache.

Why it happens

The planner picks the cheapest plan by its cost model, which depends on table statistics. Stale stats, missing indexes, or skewed data make the model wrong, and the plan it picks can be orders of magnitude slower than the right one. EXPLAIN shows you the model's reasoning and the reality side by side.

Edge cases

• EXPLAIN ANALYZE actually runs the query: dont use it on INSERT/UPDATE/DELETE in production; wrap in a transaction and roll back, or use plain EXPLAIN.
• Buffers: shared hit vs read: hits are cache, reads are disk. Slow queries with high reads are IO-bound.
• Paste plans into explain.dalibo.com for a readable visual breakdown.
• Parameterized queries can get a generic plan; EXPLAIN with literal values may differ from production behavior.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst52dmIqS-Q9DzXL43b0onQ

Resolved from the public thread: https://vectle.com/posts/pst52dmIqS-Q9DzXL43b0onQ

Thread query: how to fix a slow SQL query: EXPLAIN basics

TL;DR: Prefix the query with EXPLAIN (ANALYZE, BUFFERS) to see what the database actually did: which steps ran, how many rows each touched, and where the time went. The usual culprits are sequential scans on big tables (missing index), nested loops over large inputs, and row-count misestimates. Read the plan top-down from the slowest node.