[SERVER-40873] Poor performance on certain configurations of hint/limit/min/max Created: 27/Apr/19  Updated: 06/May/19  Resolved: 06/May/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Performance, Querying
Affects Version/s: 3.4.20, 4.0.3
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Ryan Keener Assignee: Eric Sedor
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File explain1_34.json     File explain1_40.json     File explain2_34.json     File explain2_40.json     File explain3_34.json     File explain3_40.json     File server_34.json     File server_40.json    
Issue Links:
Duplicate
duplicates SERVER-8045 Extend predicate language to support ... Backlog
Related
related to SERVER-17580 Warn when using only one of $min or $... Open
related to SERVER-15168 Allow intersection of $min/$max bound... Closed
Operating System: ALL
Participants:

 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,

(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?



 Comments   
Comment by Ryan Keener [ 01/May/19 ]

It seems odd that SERVER-15168 is marked as a duplicate of SERVER-8045, since it describes an index intersection whereas the other one talks about abandoning the min/max in favor of another index.
Regardless, I do believe this is a duplicate of SERVER-15168 and should be marked as such and closed.

And seeing as SERVER-8045 is about to turn 6.5 years old I'm guessing that this is not a top priority.

Comment by Kevin Pulo [ 01/May/19 ]

See also SERVER-15168/SERVER-8045, and SERVER-17580.

Comment by Eric Sedor [ 29/Apr/19 ]

Hi bendru, please check out this documentation. If you have questions, I encourage you to ask our community by posting on the mongodb-user group or on Stack Overflow with the mongodb tag. This is because the SERVER project is for bugs and feature suggestions for the MongoDB server.

Generated at Thu Feb 08 04:56:11 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.