Clustered Index vs Secondary Index in MongoDB

Question

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.

• Joe: The first thing that comes to mind is when one index is a prefix of the other.

Answer 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