[SERVER-15985] plan change in 2.6.5 causes query to run slower than in 2.6.4 Created: 06/Nov/14  Updated: 08/Feb/23  Resolved: 06/Nov/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: yosi oren Assignee: Ramon Fernandez Marina
Resolution: Done Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File mongodb-bug-2.6.5.js    
Issue Links:
Related
related to SERVER-14618 Wrong index bounds when using "hint" Closed
related to SERVER-15279 Disable hash-based index intersection... Closed
Operating System: ALL
Participants:

 Description   

when same query and index run on 2.6.4 it is much faster then on 2.6.5 , looking at explain show second scan to not use the indexes.

the attached js file has both query and explain results for both versions, can see millis difference . 150015 on 2.6.5 compared to 0 on 2.6.4 server.

follow is [ObjectId()]

query use $in and $nin .

the affect is noticed when users is more then 110000 documents on a server with 1GB RAM.



 Comments   
Comment by Ramon Fernandez Marina [ 06/Nov/14 ]

nabulaer, this query is performing faster in 2.6.4 because hash-based index intersection is being used. Here's the relevant part of the explain() output that shows what's going on:

db.users.find({follow: { $in: [ObjectId("5374578af88919d05eee318e")], $nin: [ObjectId("5374578af88919d05eee318f")] }}).explain()
{
    "cursor" : "Complex Plan",
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 2,
    [...]
                "children" : [ 
                    {
                        "type" : "AND_HASH",
                        [...]
                        "children" : [ 
                            {
                                "type" : "IXSCAN",
                                "isEOF" : 0,
                                "keyPattern" : "{ follow: 1 }",
                                "boundsVerbose" : "field #0['follow']: [MinKey, ObjectId('5374578af88919d05eee318f')), (ObjectId('5374578af88919d05eee318f'), MaxKey]",
                            }, 
                            {
                                "type" : "IXSCAN",
                                "isEOF" : 1,
                                "keyPattern" : "{ follow: 1 }",
                                "boundsVerbose" : "field #0['follow']: [ObjectId('5374578af88919d05eee318e'), ObjectId('5374578af88919d05eee318e')]",
                            }
    [...]
}

The query planner runs two index scans (IXSCAN), one for the {$in:[...318e]} predicate and one for the {$nin:[...318f']} predicate, with the goal of intersecting the results at the end (see the ComplexPlan and AND_HASH markers above). Pretty soon it finds out there's no documents where the follow field contains the ObjectID ending in 318e (see isEOF field), and the intersection of the empty set with anything else is the empty set so the query planner returns almost instantly with 0 results.

Hash-based index intersection was disabled by default in 2.6.5 because we found it could cause significant performance degradation in specific cases (see SERVER-14961 for further details), so the query planner picks one of the query predicates to perform an index scan on. In this case it's picking the $nin predicate, which doesn't seem to be the most selective for your dataset. Additionally, the query planner can't intersect bounds on multi-key indexes (see this comment on SERVER-14618 for details), so it's currently not possible to choose better bounds for the index scan.

You can always enable hash-based index intersection as described above if it helps you achieve better performance (or stay with 2.6.4), but please be aware of the potential implications described in SERVER-14961. Another alternative is to modify your query to include only a predicate for the most selective condition, and then filter for the least selective condition at the application level.

Regards,
Ramón.

Comment by Ramon Fernandez Marina [ 06/Nov/14 ]

This is related to hash-based index intersection, which was disabled on 2.6.5 as described in SERVER-15279.

Can you try re-enabling hash-based index intersection and running your queries again? Here's how:

db.adminCommand({setParameter: 1, internalQueryPlannerEnableHashIntersection: 1});

Also, can you please send us the output of the following command?

db.users.getIndexes()

Thanks,
Ramón.

Generated at Thu Feb 08 03:39:36 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.