Aggregation projection on "_id" field going for COLLSCAN

Hello,

I am trying to get all the data projection with “id” field, but execution plan shows it is going for COLLSCAN.
Ideally it should consider the “_id” index. please advise, if I am missing anything here -

db.getCollection("summary").explain().aggregate([ {

                "$match" : {                }
            }, 
            {
                "$project" : {
                    "_id" : NumberInt(1)
                }
            }])


"winningPlan" : {
                        "stage" : "COLLSCAN", 
                        "direction" : "forward"
                    },

Hi @Amit_G,

MongoDB collection scan is much more optimized than full index scans even for one field projection. Therefore optimzer prefer this method over full index scans.

Additionally, in aggregation frame work the stages pass documents in memory from one to another therefore second stage might not use an index that is not used in first.

Instead of empty match , consider using { $sort : { _id : 1} } in first stage.

You can try to hint the _id index and see if it works better but I doubt it

Thanks
Pavel

1 Like