COLLSCAN vs IXSCAN

COLLSCAN reads every document in the collection and applies the filter. IXSCAN walks index keys, then usually FETCHes the documents those keys point at. The interview before/after is: same query, createIndex, explain again.

db.users.find({ city: "Bangalore" }).explain("executionStats")
// winningPlan.stage: COLLSCAN
// totalDocsExamined: 42   nReturned: 8

db.users.createIndex({ city: 1 })
db.users.find({ city: "Bangalore" }).explain("executionStats")
// winningPlan: FETCH → IXSCAN on city_1
// totalKeysExamined: 8   totalDocsExamined: 8   nReturned: 8

A COLLSCAN is not always wrong. Forty users, or a query that returns most of the collection ({ isActive: true } at 80%), can be cheaper as a scan than bouncing through a weak index. The planner knows that. Your job is to notice COLLSCAN on a selective filter on a large collection. { $hint: { city: 1 } } forces an index for experiments — not for production defaults.