1

I see in mongo doc

Starting in MongoDB 6.0.7, if a usable clustered index exists, the MongoDB query planner evaluates the clustered index against secondary indexes in the query planning process. When a query uses a clustered index, MongoDB performs a bounded collection scan.

Prior to MongoDB 6.0.7, if a secondary index existed on a clustered collection and the secondary index was usable by your query, the query planner selected the secondary index instead of the clustered index by default. In MongoDB 6.1 and prior, to use the clustered index, you must provide a hint because the query optimizer does not automatically select the clustered index.

But I can't imagine the scenario of this, can any one give me real query example of both cases? I mean a query when the planner will prefer the secondary over clustered and vica verse.

Improve this question
1
  • The first thing that comes to mind is when one index is a prefix of the other. Commented May 21 at 16:16

1 Answer 1

Reset to default
1

I think this simply means that before, in the following case, the secondary index was used, but now both are evaluated:

db.createCollection(
   "demo",
   { clusteredIndex: { "key": { _id: 1 }, "unique": true } }
);  
  
db.demo.createIndex({username: 1});

You can verify the candidate plans:

db.setProfilingLevel(2 );
db.demo.find({_id: 1, username: "franck"});  
db.demo.getPlanCache().list();

On MongoDB 8.0 I see CLUSTERED_IXSCAN and IXSCAN in the plan cache

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.