[SERVER-12825] Don't generate index intersection plans if you can use a compound index instead Created: 21/Feb/14  Updated: 11/Jul/16  Resolved: 26/Feb/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0-rc0
Fix Version/s: 2.6.0-rc1

Type: Bug Priority: Major - P3
Reporter: David Storch Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-12673 index bounds incorrect on compound in... Closed
Operating System: ALL
Participants:

 Description   

Repro:

> t = db.t
> t.drop()
> t.ensureIndex({a: 1})
> t.ensureIndex({b: 1})
> t.ensureIndex({a: 1, b: 1})
> t.find({a: 1, b: 1})

Using the compound index is always better than index intersection for the query above. However, we generate index intersection solutions, e.g.:

FETCH
---fetched = 1
---sortedByDiskLoc = 0
---getSort = []
---Child:
------KEEP_MUTATIONS
---------filter:
                $and
                    a == 1.0
                    b == 1.0
---------fetched = 0
---------sortedByDiskLoc = 0
---------getSort = []
---------Child:
------------AND_HASH
---------------fetched = 0
---------------sortedByDiskLoc = 0
---------------getSort = [{ a: 1 }, { a: 1, b: 1 }, { b: 1 }, ]
---------------Child 0:
---------------IXSCAN
------------------keyPattern = { b: 1.0 }
------------------direction = 1
------------------bounds = field #0['b']: [1.0, 1.0]
------------------fetched = 0
------------------fetched = 0
------------------sortedByDiskLoc = 1
------------------getSort = [{ b: 1 }, ]
---------------Child 1:
---------------IXSCAN
------------------keyPattern = { a: 1.0, b: 1.0 }
------------------direction = 1
------------------bounds = field #0['a']: [1.0, 1.0], field #1['b']: [MinKey, MaxKey]
------------------fetched = 0
------------------fetched = 0
------------------sortedByDiskLoc = 0
------------------getSort = [{ a: 1 }, { a: 1, b: 1 }, { b: 1 }, ]

We perhaps shouldn't even generate index intersection solutions that we know will perform worse than the compound index solution.



 Comments   
Comment by Githook User [ 26/Feb/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-12825 don't use ixisect if a compound index is available
Branch: master
https://github.com/mongodb/mongo/commit/997798bde77060ce9d3990d3f4caabbb9e5058df

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