-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 3.4.20, 4.0.3
-
Component/s: Index Maintenance, Performance, Querying
-
None
-
ALL
I've run into a performance issue when using min/limit/hint without max that I'd like some insight on.
I've got an index called sort_updated_at on my collection:
{ client_id: 1, updated_at: 1, _id: 1 }
Note: there are 15 total entries in the db with client_id : "cl_cjr7ucmwi00030xqip5ez34ym"
If I make the following query
db.appointments.find( {client_id: 'cl_cjr7ucmwi00030xqip5ez34ym'} ) .sort({updated_at: 1}) .hint('sort_updated_at') .min({ client_id : "cl_cjr7ucmwi00030xqip5ez34ym", updated_at : ISODate("2019-03-15T17:04:08.095+0000"), _id : ObjectId("5c8bdb07553f1800058a8ab0"), }) .max({ client_id : "cl_cjr7ucmwi00030xqip5ez34ym", updated_at : MaxKey, _id : MaxKey, }) .limit(20);
This has very good performance
"nReturned": 15.0, "executionTimeMillis": 19.0, "totalKeysExamined": 15.0, "totalDocsExamined": 15.0,
(explain1)
However, if I remove the max configuration I get the following despite the client_id restriction in the original query:
"nReturned" : 15.0, "executionTimeMillis" : 7582.0, "totalKeysExamined" : 116046.0, "totalDocsExamined" : 116046.0,
(explain2)
Finally, if I also remove the min configuration I see the performance improve again
"nReturned" : 15.0, "executionTimeMillis" : 13.0, "totalKeysExamined" : 15.0, "totalDocsExamined" : 15.0,
(explain3)
I've attached {[explain}} results for each of the above three scenarios using mongodb 3.4.20 and 4.0.3 as well as the corresponding server info.
What am I missing?
- duplicates
-
SERVER-8045 Extend predicate language to support $min/$max use cases
- Backlog
- related to
-
SERVER-17580 Warn when using only one of $min or $max index bounds
- Open
-
SERVER-15168 Allow intersection of $min/$max bounds with computed query indexBounds
- Closed