[SERVER-7959] Potentially unexpected scans with compound indexes when some fields are multikey Created: 17/Dec/12  Updated: 03/Sep/14  Resolved: 02/Sep/14

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

Type: Bug Priority: Major - P3
Reporter: Max Schireson Assignee: hari.khalsa@10gen.com
Resolution: Duplicate Votes: 13
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File server7959.js    
Issue Links:
Duplicate
duplicates SERVER-15086 Allow for efficient range queries ove... Closed
is duplicated by SERVER-8454 Multi-key index prevents bounds being... Closed
Related
related to SERVER-12470 document and audit multikey bounds be... Closed
is related to SERVER-3173 Planner should use path-level multike... Closed
is related to SERVER-6050 Consider allowing $elemMatch applied ... Closed
Operating System: ALL
Participants:

 Description   

Lets say you are querying for { a:{ $gt:10, $lt:20 } }

and you have an index on a, b

If b is multikey, the query optimizer will scan a much wider range of index and it will have to scan the objects to complete query evaluation. This is because the optimizer doesn't know if all the values of a are scalar; if they are not a document like { _id:2, { a:[ 5, 25 ] } } would match the query.

This makes the advice that an index on a is not needed if you have an index on a, b incorrect and makes performance optimization confusing.



 Comments   
Comment by Thomas Rueckstiess [ 02/Sep/14 ]

Closing this ticket as duplicate of SERVER-15086.

Even though this ticket precedes SERVER-15086, there is more information and recent discussion on the new ticket.

Comment by Zack Slayton [ 06/Jun/14 ]

@Max Schireson

What was the workaround provided? I've been wrestling with this issue for months. I'd love to see a fix, even if it's a temporary solution.

Comment by Benety Goh [ 30/Oct/13 ]

smoke test script to reproduce issue

Comment by Max Schireson [ 18/Dec/12 ]

There is a very simple special case that may be worth doing if the bigger fix will take a while: at least we know that _id is scalar even when it is a part of a multi-key compound index. In the specific case that inspired this ticket, that would have solved the problem.

Comment by Max Schireson [ 17/Dec/12 ]

Workaround provided, but this issue is at the root of that case.

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