[SERVER-8300] Covered multi-key query still scans Created: 23/Jan/13 Updated: 07/Mar/14 Resolved: 23/Jan/13 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.2.2 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Critical - P2 |
| Reporter: | Nic Cottrell (Personal) | Assignee: | Aaron Staple |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
RHEL5 |
||
| Issue Links: |
|
||||||||
| Participants: | |||||||||
| Description |
|
index exists: { t:1, lc:1, wpm:1}through the Java library I use the with keys = {lc: 1, t:1, wpm:1, _id: 0}and this query is logged as Wed Jan 23 17:31:09 [conn5342] query jerome5.Unit query: { wpm: { $gte: 0.008999999612569809 }, t: { $in: [ "Central", "Commission", "Kenya", "CKRC", "East", "Mandera", "Constituency", "Report", "North", "Province", "Rhamu" ] }, lc: { $in: [ "por", "est", "pol", "vie", "isl", "nor", "ita", "deu", "ces", "eng", "hun", "sqi", "slk", "hin", "slv", "nld", "tur", "dan", "cat", "fra", "fin", "esl", "sve", "lav" ] }} ntoreturn:0 ntoskip:0 nscanned:210 keyUpdates:0 numYields: 1 locks(micros) r:106836 nreturned:83 reslen:3694 126ms I then ran an explain on a similar query: db.Unit.find({ wpm: { $gte: 0.008999999612569809 }, t: { $in: [ "knocking", "witnesses", "care", "off", "you", "systematically", "and" ] }, lc: { $in: [ "ukr", "slk", "cat", "zho", "deu", "fra", "por", "fin", "hin", "ces", "slv", "nld", "kor", "est", "jap", "rus", "ara", "pol", "eng", "vie", "sve", "esl", "hun", "isl", "lav", "ell", "tur", "bel", "nor", "bul", "dan", "sqi", "ita", "mkd" ] }}, {t: 1, lc: 1, wpm: 1, _id: 0}).explain(); I don't understand why nscannedObjects is > 0 especially is indexOnly is true. Since i'm not asking for _id, everthing to return is already in the index, so why are any object scans happening? |
| Comments |
| Comment by Aaron Staple [ 24/Jan/13 ] |
|
Hi Nic - If you have a multikey index (isMultiKey:true) we don't support covered indexing. This is |
| Comment by Nic Cottrell (Personal) [ 24/Jan/13 ] |
|
I have a related issue, but which reports "indexOnly: false" ... > db.Example.find( { lc: "sve", group: "all", "indices.textLc": "bräcklig" } ).explain(); Even if I only retrieve the "group" field, it still reports indexOnly: false |
| Comment by Aaron Staple [ 23/Jan/13 ] |
|
Hi Nic - Well, the indexes are going to return their own BSON (representing the indexed fields extracted from the original doc) which is going to be scanned. But the original document in the main document store won't be scanned. It's just a reporting issue. |
| Comment by Nic Cottrell (Personal) [ 23/Jan/13 ] |
|
So, no BSON is being scanned here - it's just a reporting issue? |
| Comment by Aaron Staple [ 23/Jan/13 ] |
|
Hi Nic - Prior to 2.3.0, nscannedObjects was not reported correctly when covered indexes were used. In these cases, the full documents were not being read to return query results, even though the explain output reported nscannedObjects as if the documents were being read. The pertinent ticket is |