mongodb query doing COLLSCAN instead of IXSCAN is slow and times out

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

The query is not slow, it is lost. vtgate timed out because the query could not be routed to one shard and scattered across all of them instead, usually a join without a shared sharding key or a table missing a vindex. Run EXPLAIN on vtgate: a Scatter route across every shard is the smoking gun. Fix the VSchema so the query routes, with a lookup vindex on the join key or by moving the small table to an unsharded keyspace.

vtgate: rpc error: code = DeadlineExceeded desc = context deadline exceeded (query scattered to 16 shards, timed out waiting for responses)

Steps

1. Run EXPLAIN on vtgate for the timed-out query (EXPLAIN FORMAT = VTEXPLAIN where supported). Expected: the plan shows a Scatter route fanning out to all shards instead of a single-shard route. That is the problem.

2. Check the VSchema for every table in the query. Does each sharded table have a vindex, and does the join use the sharding key on both sides? Expected: you find the gap, a table with no vindex, or a join on a column that is not the sharding key.

3. For a small dimension table joined everywhere, move it to the unsharded keyspace (or replicate it to each shard). Expected: the join becomes a local lookup on each tablet, and the scatter disappears from the plan.

4. For the fact-table join, add a consistent lookup vindex on the join column so vtgate can route by it. Expected: EXPLAIN shows a routed single-shard plan, and the timeout stops.

5. As a stopgap, raise the vtgate query timeout for the affected workload. Expected: fewer timeouts, but the scatter load on the tablets stays. Treat this as breathing room while you fix the VSchema.

Use this when

• Queries time out at vtgate while simple keyed lookups are fast.
• EXPLAIN shows Scatter routes across all shards.
• A new table or join was added recently and timeouts started with it.
• Tablet CPU is high everywhere at once, suggesting every query hits every shard.

Not for this skill when

• Single-shard queries also time out. That is a tablet or MySQL-level problem, check the tablets first.
• vtgate itself is overloaded or OOMing. That is a vtgate sizing problem, not a routing problem.
• The timeout is on DDL or resharding operations. Those have their own workflows.

Variant phrasings

• vitess query timeout scatter gather
• vtgate deadline exceeded scatter
• vitess unsharded join slow
• vitess EXPLAIN scatter route all shards
• vitess lookup vindex join timeout

Why it happens

vtgate routes each query to the shards that own the rows, using the VSchema's vindexes. When a query cannot be routed, because a table lacks a vindex or a join does not share the sharding key, vtgate falls back to scatter-gather: it sends the query to every shard and merges the results. Latency becomes the slowest shard plus merge overhead, and under load the query blows past the vtgate timeout. The timeout is vtgate giving up on a plan that was doomed by routing.

Edge cases

• Scatter-gather is sometimes legitimate for admin or analytics queries. Route those to a separate vtgate pool with a longer timeout instead of "fixing" the VSchema.
• Lookup vindexes add a write-time cost: every insert does an extra lookup-table write. Fine for read-heavy joins, think twice for write-heavy tables.
• Cross-shard transactions (multi-shard writes) use 2PC and have their own timeout behavior. A scatter read timing out and a 2PC write timing out are different problems.
• After VSchema changes, verify with EXPLAIN before declaring victory. A typo in the vindex definition silently leaves the scatter in place.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_N-m-s7ivIBpP9-JjcKVhKQ

Resolved from the public thread: https://vectle.com/posts/pst_N-m-s7ivIBpP9-JjcKVhKQ

Thread query: vitess 'vtgate timeout' on scatter-gather queries: how to find the unsharded join

TL;DR: The query is not slow, it is lost. vtgate timed out because the query could not be routed to one shard and scattered across all of them instead, usually a join without a shared sharding key or a table missing a vindex. Run EXPLAIN on vtgate: a Scatter route across every shard is the smoking gun. Fix the VSchema so the query routes, with a lookup vindex on the join key or by moving the small table to an unsharded keyspace.

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.