[SERVER-2982] too much data for sort() with no index, but index does exists, and run explain() it does show using BtreeCursor Created: 22/Apr/11 Updated: 30/Mar/12 Resolved: 21/Jun/11 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | 1.8.0 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Critical - P2 |
| Reporter: | Stone, Gao | Assignee: | Aaron Staple |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
ubuntu 10.04 64 bit |
||
| Operating System: | Linux |
| Participants: |
| Description |
|
> db.error_types.getIndexes() }, , , , > db.error_types.find( { is_resolved: false, app_id: ObjectId('4d15ce1a9ea1a313dd017dba')}).sort( { updated_at: -1 }) but if I run the query with explain, BtreeCursor does used. (but it seems that mongodb picked the wrong one) > db.error_types.find( { is_resolved: false, app_id: ObjectId('4d15ce1a9ea1a313dd017dba')}).sort( { updated_at: -1 }).explain() , { "$maxElement" : 1 } ] I added hint to the query, but still the same error >db.error_types.find( { is_resolved: false, app_id: ObjectId('4d15ce1a9ea1a313dd017dba')}).sort( { updated_at: -1 }).hint("app_id_1_app_version_-1_is_resolved_1_updated_at_-1") > db.error_types.find( { is_resolved: false, app_id: ObjectId('4d15ce1a3ea7a313dd017dba')}).sort( { updated_at: -1 }).hint("app_id_1_app_version_-1_is_resolved_1_updated_at_-1") <4d15ce1a9ea1a313dd017dba')}).sort( { updated_at: -1 }).hint("app_id_1_app_version_-1_is_resolved_1_updated_at_-1").explain() , { "$minElement" : 1 } ] "$maxElement" : 1 ] |
| Comments |
| Comment by Aaron Staple [ 21/Jun/11 ] |
|
No prob - feel free to reopen this ticket if you have further questions. |
| Comment by Aaron Staple [ 20/Jun/11 ] |
|
Hi Stone, The second index will work but not the first. Since app_version does not have an equality match in your query, it cannot appear in the index before the fields your are matching and sorting on. Aaron |
| Comment by Stone, Gao [ 16/Jun/11 ] |
|
Sorry for the inconvenience. I mean if I create an index : { app_version : 1, app_id : 1, is_resolved : 1, updated_at: -1 }or { app_id: 1, is_resolved: 1, updated_at: -1 }it will work? |
| Comment by Aaron Staple [ 15/Jun/11 ] |
|
The index names you provided don't match names that would come from our normal name generation methods. If you want to ask about specific indexes, please use the standard key format, eg {a:1}or {b:1,c:-1}. Thanks. |
| Comment by Stone, Gao [ 15/Jun/11 ] |
|
Thanks for the help. So if I create an index : app_version_app_id_1_-1_is_resolved_1_updated_at_-1 or app_id_1_-1_is_resolved_1_updated_at_-1, it will work? |
| Comment by Aaron Staple [ 14/Jun/11 ] |
|
Hi Stone, Do you have any more questions related to this ticket? |
| Comment by Aaron Staple [ 26/Apr/11 ] |
|
I think the main issue here is that the index is not ordered with respect to the sort spec that has been requested. The query specifies equality constraints on app_id and is_resolved but not app_version, and the sort requested is on updated_at. Since the only index containing updated_at is app_id_1_app_version_-1_is_resolved_1_updated_at_-1, the data must be sorted in memory to match the requested sort order. |