Major - P3 I have a collection and compound index on it
IX_checkin {checkin:1, nights:1, people_count:1, idx_price:1}
When I run a query with filter like this:
db.cat_claim .explain("queryPlanner") .find({ "checkin" : { "$in" : [ ISODate("2019-11-26T03:00:00.000+03:00"), ISODate("2019-11-27T03:00:00.000+03:00"), ISODate("2019-11-28T03:00:00.000+03:00"), ISODate("2019-11-29T03:00:00.000+03:00"), ISODate("2019-11-30T03:00:00.000+03:00"), ISODate("2019-12-01T03:00:00.000+03:00"), ISODate("2019-12-02T03:00:00.000+03:00"), ISODate("2019-12-03T03:00:00.000+03:00"), ISODate("2019-12-04T03:00:00.000+03:00"), ISODate("2019-12-05T03:00:00.000+03:00"), ISODate("2019-12-06T03:00:00.000+03:00"), ISODate("2019-12-07T03:00:00.000+03:00"), ISODate("2019-12-08T03:00:00.000+03:00"), ISODate("2019-12-09T03:00:00.000+03:00"), ISODate("2019-12-10T03:00:00.000+03:00"), ISODate("2019-12-11T03:00:00.000+03:00"), ISODate("2019-12-12T03:00:00.000+03:00"), ISODate("2019-12-13T03:00:00.000+03:00"), ISODate("2019-12-14T03:00:00.000+03:00"), ISODate("2019-12-15T03:00:00.000+03:00"), ISODate("2019-12-16T03:00:00.000+03:00"), ISODate("2019-12-17T03:00:00.000+03:00"), ISODate("2019-12-18T03:00:00.000+03:00"), ISODate("2019-12-19T03:00:00.000+03:00"), ISODate("2019-12-20T03:00:00.000+03:00"), ISODate("2019-12-21T03:00:00.000+03:00"), ISODate("2019-12-22T03:00:00.000+03:00"), ISODate("2019-12-23T03:00:00.000+03:00"), ISODate("2019-12-24T03:00:00.000+03:00"), ISODate("2019-12-25T03:00:00.000+03:00"), ISODate("2019-12-26T03:00:00.000+03:00") ] }, "nights" : { "$in" : [ 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 ] }, "people_count" : 2 }) .sort({"idx_price":1}) .limit(600)
Query planner shows SORT_KEY_GENERATOR and following SORT input stage instead of SORT_MERGE input stage. If I replace $in operators with array of $or conditions with the same conditions, query planner produces SORT_MERGE input stage.