Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
3.4.20, 4.0.3
-
None
-
ALL
Description
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,
|
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,
|
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,
|
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?
Attachments
Issue Links
- 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
-