-
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
-
-
- Open
-
-
-
- Closed
-