MongoDB sits at the crossroads between SQL, where developers can write queries on a logical model without naming indexes, and the NoSQL world, where developers are accustomed to querying directly by mentioning indexes in their queries.
Except for Atlas Search and Vector indexes, where you must explicitly indicate whether it uses approximate search, you typically query a collection, and the MongoDB query planner determines which index to use.
One exception to this rule happens when using an optimizer hint to reference an index. This feature is supported in most popular databases, except PostgreSQL, where it requires pg_hint_plan. When the hint isn't fully compatible with the query planner's execution plans, different databases may behave differently.
Here is my test case to show how it behaves in MongoDB:
mdb> db.demo.insertMany([
{ _id: 1, name: "Alice", age: 30 }, // has age
{ _id: 2, name: "Bob" }, // missing age
{ _id: 3, name: "Charlie", age: null }, // age is null
{ _id: 4, name: "Dave", age: 35, status: "active" }, // age + status
{ _id: 5, name: "Eve", age: 28, status: "inactive" } // age + status
]);
{
acknowledged: true,
insertedIds: { '0': 1, '1': 2, '2': 3, '3': 4, '4': 5 }
}
mdb> db.demo.find().sort({ age: 1 });
[
{ _id: 2, name: 'Bob' },
{ _id: 3, name: 'Charlie', age: null },
{ _id: 5, name: 'Eve', age: 28, status: 'inactive' },
{ _id: 1, name: 'Alice', age: 30 },
{ _id: 4, name: 'Dave', age: 35, status: 'active' }
]
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
);
[
{ name: 'Alice', age: 30 },
{ name: 'Dave', age: 35 },
{ name: 'Eve', age: 28 }
]
Without any index, the query scans the collection:
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 0,
totalDocsExamined: 5,
executionStages: {
isCached: false,
stage: 'PROJECTION_SIMPLE',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 6,
advanced: 3,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'COLLSCAN',
filter: { age: { '$gte': 28 } },
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 6,
advanced: 3,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
direction: 'forward',
docsExamined: 5
}
}
}
With an index, the same query does an index scan:
mdb> db.demo.createIndex({ age: 1 }, { name: "idx_age" })
idx_age
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 1,
totalKeysExamined: 3,
totalDocsExamined: 3,
executionStages: {
isCached: false,
stage: 'PROJECTION_SIMPLE',
nReturned: 3,
executionTimeMillisEstimate: 1,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'FETCH',
nReturned: 3,
executionTimeMillisEstimate: 1,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 3,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 3,
executionTimeMillisEstimate: 1,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { age: 1 },
indexName: 'idx_age',
isMultiKey: false,
multiKeyPaths: { age: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { age: [ '[28, inf.0]' ] },
keysExamined: 3,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
I create a better index, that covers the projection, and it is used:
mdb> db.demo.createIndex({ age: 1, name: 1 }, { name: "idx_age_name" })
idx_age_name
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 1,
totalKeysExamined: 3,
totalDocsExamined: 0,
executionStages: {
isCached: false,
stage: 'PROJECTION_COVERED',
nReturned: 3,
executionTimeMillisEstimate: 1,
works: 5,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'IXSCAN',
nReturned: 3,
executionTimeMillisEstimate: 1,
works: 5,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { age: 1, name: 1 },
indexName: 'idx_age_name',
isMultiKey: false,
multiKeyPaths: { age: [], name: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { age: [ '[28, inf.0]' ], name: [ '[MinKey, MaxKey]' ] },
keysExamined: 3,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
}
I can decide to force the other index with a hint:
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint("idx_age").explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 3,
totalDocsExamined: 3,
executionStages: {
isCached: false,
stage: 'PROJECTION_SIMPLE',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'FETCH',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 3,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { age: 1 },
indexName: 'idx_age',
isMultiKey: false,
multiKeyPaths: { age: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { age: [ '[28, inf.0]' ] },
keysExamined: 3,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
A hint() is more than a hint here. It forces the index scan. I can even force its bounds with min() and max():
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint("idx_age").max({age:31}) // scan from begining to age: 30
[ { name: 'Eve', age: 28 }, { name: 'Alice', age: 30 } ]
Rather than the bounds defined by the filter ([ '[28, inf.0]' ]), I forced an index scan of range [ '[-inf.0, 30]' ]. Then the filter { '$gte': 28 } applied and the result has only two documents.
This is visible in the execution plan.
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint("idx_age").max({age:31}).explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 2,
executionTimeMillis: 0,
totalKeysExamined: 4,
totalDocsExamined: 4,
executionStages: {
isCached: false,
stage: 'PROJECTION_SIMPLE',
nReturned: 2,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 2,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'FETCH',
filter: { age: { '$gte': 28 } },
nReturned: 2,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 2,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 4,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 4,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 4,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { age: 1 },
indexName: 'idx_age',
isMultiKey: false,
multiKeyPaths: { age: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: {},
keysExamined: 4,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
The bounds are not correctly displayed, but it shows that 4 keys were examined, the 4 keys before {age:31}.
The most important to remember is that hints in MongoDB replace the query planner decision, and then may may give a different results. This is different from SQL hints where they only prune the query planner possibilities.
This is also visible with sparse and partial indexes:
// Named sparse index
db.demo.createIndex(
{ age: 1 },
{ sparse: true, name: "age_sparse" }
)
// Named partial index (only for status: 'active')
db.demo.createIndex(
{ age: 1 },
{
partialFilterExpression: { status: "active" },
name: "age_active_partial"
}
);
The result depends not only on the query, but also the index definition:
mdb> db.demo.find(
).sort({ age: 1 }) // ORDER BY age NULLS FIRST
[
{ _id: 2, name: 'Bob' },
{ _id: 3, name: 'Charlie', age: null },
{ _id: 5, name: 'Eve', age: 28, status: 'inactive' },
{ _id: 1, name: 'Alice', age: 30 },
{ _id: 4, name: 'Dave', age: 35, status: 'active' }
]
mdb> db.demo.find(
).hint("age_sparse").sort({ age: 1 }) // sparse index
[
{ _id: 3, name: 'Charlie', age: null },
{ _id: 5, name: 'Eve', age: 28, status: 'inactive' },
{ _id: 1, name: 'Alice', age: 30 },
{ _id: 4, name: 'Dave', age: 35, status: 'active' }
]
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
).sort({ age: 1 })
[
{ _id: 5, name: 'Eve', age: 28, status: 'inactive' },
{ _id: 1, name: 'Alice', age: 30 },
{ _id: 4, name: 'Dave', age: 35, status: 'active' }
]
mdb> db.demo.find(
{ age: { $gte: 28 } },
).hint("age_active_partial").sort({ age: 1 }) // partial index
[ { _id: 4, name: 'Dave', age: 35, status: 'active' } ]
The second thing to remember is that hinting with an inexistent index produces an error:
mdb> db.demo.dropIndex("age_active_partial")
{
nIndexesWas: 5,
ok: 1,
'$clusterTime': {
clusterTime: Timestamp({ t: 1753203088, i: 18 }),
signature: {
hash: Binary.createFromBase64('sCmzLMPgc8DfACIN3cKBGpoQDys=', 0),
keyId: Long('7501774816190398507')
}
},
operationTime: Timestamp({ t: 1753203088, i: 18 })
}
mdb> db.demo.find({ age: { $gte: 28 } }).hint("age_active_partial")
Uncaught:
MongoServerError[BadValue]: error processing query: ns=mdb.demoTree: age $gte 28
Sort: {}
Proj: {}
planner returned error :: caused by :: hint provided does not correspond to an existing index
This also differs from many SQL databases, where hints are ignored if they cannot be applied because they don't force an index, but only select one within the possible query plans.
Finally, you may wonder how to force a full collection scan. Use $natural for this:
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint({$natural:1}).explain("executionStats").executionStats
{
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 0,
totalDocsExamined: 5,
executionStages: {
isCached: false,
stage: 'PROJECTION_SIMPLE',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 6,
advanced: 3,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
transformBy: { _id: 0, age: 1, name: 1 },
inputStage: {
stage: 'COLLSCAN',
filter: { age: { '$gte': 28 } },
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 6,
advanced: 3,
needTime: 2,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
direction: 'forward',
docsExamined: 5
}
}
}
As the COLLSCAN has a direction, you can choose it forward or backward:
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint({$natural:1})
[
{ name: 'Alice', age: 30 },
{ name: 'Dave', age: 35 },
{ name: 'Eve', age: 28 }
]
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint({$natural:-1})
[
{ name: 'Eve', age: 28 },
{ name: 'Dave', age: 35 },
{ name: 'Alice', age: 30 }
]
You can also use the index definition rather than the index name, however, you will get an error if multiple indexes have the same definition:
mdb> db.demo.find(
{ age: { $gte: 28 } }, // WHERE age >= 28
{ _id:0, age:1, name: 1 } // SELECT age, name
).hint({ age: 1 }).explain("executionStats").executionStats
Uncaught:
MongoServerError[IndexNotFound]: error processing query: ns=mdb.demoTree: age $gte 28
Sort: {}
Proj: { _id: 0, age: 1, name: 1 }
planner returned error :: caused by :: Hint matched multiple indexes, must hint by index name. Matched: kp: { age: 1 } sparse name: '(age_sparse, )' io: { v: 2, key: { age: 1 }, name: "age_sparse", sparse: true } and kp: { age: 1 } name: '(age_active_partial, )' filterExpr: status $eq "active"
io: { v: 2, key: { age: 1 }, name: "age_active_partial", partialFilterExpression: { status: "active" } }
If you still want to use hints, here are some tips:
- Hint by index key pattern only works if that pattern is unique. If multiple indexes share the pattern, you get an error
- MongoDB will only use the specified index, never falling back to another index or a collection scan. If it cannot be used to filter, it will be fully scanned
- Hinting a non-existent or ambiguous index causes errors.
- Hinting a sparse or partial index limits results to index-covered documents.
- .min()/.max() requires a hint.
- .hint({$natural: 1}) or .hint({$natural: -1}) forces a collection scan and ignores all indexes.
- Wildcard index hints only work if compatible. MongoDB refuses to do a full scan of a wildcard index if the query does not reference a specific indexed field.
- Index name hints are case-sensitive.
- Hinted queries skip the plan cache and always plan from scratch.
- Active index filters or query settings may override your hint.
MongoDB offers both high-level declarative queries and low-level index-driven operations. When allowing the query planner to choose, results align with your filter, similar to SQL. However, using .hint() or setting index bounds with .min() or .max(), the specified access adds to the filters and can alter the result. When using a hint in MongoDB, the index definition becomes part of your query. In general, SQL databases try to provide developer an abstraction of the database structures whereas MongoDB gives them more control on responsibility in data access. Like with any database, in case of doubts, look at the execution plan with execution statistics.
Top comments (0)