|
Thanks for the reply! After a lot of digging and experimenting I understand that the case is much more complicated then I've thought.
Both $in and $or conditions produce an array of IX_scan queries followed by SORT_MERGE stage but to a certain limit. On my server this limit seems to be 200. The problem: besides conditions on index our queries almost always have additional filtering conditions and that seems to confuse optimizer.
Example1:
db.cat_claim.explain("queryPlanner").find({
|
"checkin" : { "$in" : [ ISODate("2019-12-26T03:00:00.000+03:00"), ISODate("2019-12-28T03:00:00.000+03:00"), ISODate("2019-12-29T03:00:00.000+03:00"), ISODate("2019-12-30T03:00:00.000+03:00"), ISODate("2019-01-04T03:00:00.000+03:00"), ISODate("2019-01-05T03:00:00.000+03:00"), ISODate("2019-01-07T03:00:00.000+03:00"), ISODate("2019-01-09T03:00:00.000+03:00") ] },
|
"nights" : { "$in" : [ 7, 8, 9, 10, 11, 12, 13, 14, 15 ] },
|
"people_count" : { "$in" : [2,3] },
|
"adult" : { "$gte" : 2 },
|
"packet_type" : 0,
|
"hstop" : { "$exists" : false },
|
"for_delete" : { "$exists" : false } })
|
.sort({ idx_price:1 })
|
.limit(600)
|
The plan is good, Optimizer generates 144 IX_SCANs followed by SORT_MERGE followed by FILTER with TOP.
Lets add one more people_count value.
Example2:
db.cat_claim.explain("queryPlanner").find({
|
"checkin" : { "$in" : [ ISODate("2019-12-26T03:00:00.000+03:00"), ISODate("2019-12-28T03:00:00.000+03:00"), ISODate("2019-12-29T03:00:00.000+03:00"), ISODate("2019-12-30T03:00:00.000+03:00"), ISODate("2019-01-04T03:00:00.000+03:00"), ISODate("2019-01-05T03:00:00.000+03:00"), ISODate("2019-01-07T03:00:00.000+03:00"), ISODate("2019-01-09T03:00:00.000+03:00") ] },
|
"nights" : { "$in" : [ 7, 8, 9, 10, 11, 12, 13, 14, 15 ] },
|
"people_count" : { "$in" : [2,3,4] },
|
"adult" : { "$gte" : 2 },
|
"packet_type" : 0,
|
"hstop" : { "$exists" : false },
|
"for_delete" : { "$exists" : false } })
|
.sort({ idx_price:1 })
|
.limit(600)
|
Now we have 216 combinations of values and optimizer decides to use one IX_SCAN with arrays of bound values. Does such stage prevent using the index to sort result on "idx_price"? It abandons SORT_MERGE and uses FETCH to FILTER ALL found rows which is slow if number is big (my case) or filter is complicated. After that comes the in memory SORT stage.
Now I modified query to use $or array of "checkin" and "nights" pairs.
Example3:
db.cat_claim.explain("queryPlanner").find({ "$or" : [ { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : 15 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 7 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 8 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 9 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 10 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 11 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 12 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 13 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 14 }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : 15 } ], "people_count" : { "$in" : [ 2,3 ] }, "adult" : { "$gte" : 2 }, "packet_type" : 0, "hstop" : { "$exists" : false }, "for_delete" : { "$exists" : false }).sort({ idx_price:1 }).limit(600)
|
Optimizer generates 72 IX_SCANs instead of 144, and each of them is scan with array of bound values for "people_count". This seems to prevent using the index to sort on "idx_price".
Example4:
db.cat_claim.explain("queryPlanner").find({ "$or" : [ { "checkin" : ISODate("2019-12-26T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2019-12-28T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2019-12-29T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2019-12-30T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2020-01-04T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2020-01-05T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2020-01-07T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} }, { "checkin" : ISODate("2020-01-09T03:00:00.000+03:00"), "nights" : {"$in":[7,8,9,10,11,12,13,14,15]} } ], "people_count" : { "$in" : [ 2,3 ] }, "adult" : { "$gte" : 2 }, "packet_type" : 0, "hstop" : { "$exists" : false }, "for_delete" : { "$exists" : false }).sort({ idx_price:1 }).limit(600)
|
The same as example3. Only difference is the number of IX_SCANS.
If I remove additional filter
"adult" : { "$gte" : 2 }, "packet_type" : 0, "hstop" : { "$exists" : false }, "for_delete" : { "$exists" : false }
|
from Example3 or Example4 optimizer generates plan identical to Example1.
I attached generated plans for each example.
|