[SERVER-12328] Query: index intersection causes regression when index key set has low cardinality Created: 10/Jan/14 Updated: 11/Jul/16 Resolved: 16/Jan/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Concurrency, Querying |
| Affects Version/s: | 2.5.4 |
| Fix Version/s: | 2.5.5 |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Davide Italiano | Assignee: | David Storch |
| Resolution: | Done | Votes: | 0 |
| Labels: | 26qa | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Linux ip-10-237-131-70 3.11.0-12-generic #19-Ubuntu SMP Wed Oct 9 16:20:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux |
||
| Attachments: |
|
| Operating System: | ALL |
| Participants: |
| Description |
|
Queries of the type
on documents which schema is
are about 2x slower on 2.6 with respect to 2.4, in case one of the two field key set has low cardinality, index intersection is on, and two indexes are built (one on each field, x and y). Analysis of the problem:
and the two aforementioned indexes.
your query engine will likely scan the whole index for y (and part of the index for x), causing the huge performance drawback. This is somewhat confirmed by experimental results, see attached screenshot + script. There's a point after which index intersection is a clear win – unfortunately I think it's hard to choose such value without statistics/histograms. |
| Comments |
| Comment by Githook User [ 07/Mar/14 ] |
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: |
| Comment by Githook User [ 16/Jan/14 ] |
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: |
| Comment by Githook User [ 16/Jan/14 ] |
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: |
| Comment by Githook User [ 15/Jan/14 ] |
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: |