[SERVER-15300] Wrong index is being used, causing inefficient queries Created: 18/Sep/14  Updated: 28/Oct/14  Resolved: 28/Oct/14

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

Type: Bug Priority: Major - P3
Reporter: Baruch Oxman Assignee: Ramon Fernandez Marina
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-14961 Plan ranker favors intersection plans... Closed
Operating System: ALL
Participants:

 Description   

I have a collection with two fields. One is a string and another is an ObjectId.
I have indices on both fields.
When doing a query such as {'string_field' : 'some value', 'other_obj_id' : {$exists : true}}, the index used for the query is consistently the one on 'other_obj_id' and not the one on 'string_field', although that one would be much faster.

This staretd to happen as soon as I've upgraded to 2.6.4 and is a degradation from 2.6.1 !



 Comments   
Comment by Ramon Fernandez Marina [ 28/Oct/14 ]

Understood, thanks for letting us know baruchoxman.

Comment by Baruch Oxman [ 28/Oct/14 ]

Indeed looks like a duplicate of SERVER-14961. I've upgraded to 2.6.5, so
feel free to close and I'll reopen with the additional information if I
still encounter it after the upgrade.

On Tue, Oct 28, 2014 at 8:50 PM, Ramon Fernandez (JIRA) <jira@mongodb.org>

Comment by Ramon Fernandez Marina [ 28/Oct/14 ]

baruchoxman, we haven't heard back from you for a while? Is this still an issue for you? Have you had a chance to look into SERVER-14961? If this is still an issue, can you please post the output of explain() for one of those queries where you see degraded performance?

Thanks,
Ramón.

Comment by Ramon Fernandez Marina [ 25/Sep/14 ]

baruchoxman, can you post the output of explain() for one of these queries? I'm suspecting you may be running into SERVER-14961. If the plan ranker is choosing to do index intersection that could explain the performance degradation you're seeing. Note also that SERVER-14961 offers a possible workaround – could you please try to run your queries before and after disabling index intersection to see if this is the root cause of the issue?

Thanks,
Ramón.

Comment by Baruch Oxman [ 22/Sep/14 ]

I don't have it ready now, as I changed my queries in the meantime, by creating a compound index on both fields (which solved the problem).

I read about the way mongo decides which index to use, and that it has a "preferred index" for each query, that gets invalidated after 1000 writes to the collection, so could be that the slow instances I saw were these queries where the queries optimizer had to decide which index to use.
But still, I am sure it should do better when one part of the query is a specific value, and the other is an $exists clause.

To my understanding, this would be reproduced with any collection with a large amount of documents (let's say 500,000), where there are two fields: "a" - with a string value, and an additional "obj_id" value, which might either exist or not, and there are individual indices on both fields.

Comment by Ramon Fernandez Marina [ 22/Sep/14 ]

baruchoxman, do you have a reproducer with a few sample documents that shows the behavior you describe?

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