[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()
[
{
"name" : "id",
"ns" : "prod_ec.error_types",
"key" :

{ "_id" : 1 }

},
{
"key" :

{ "app_id" : 1, "digest" : 1 }

,
"ns" : "prod_ec.error_types",
"background" : true,
"name" : "app_id_1_digest_1"
},
{
"key" :

{ "app_id" : 1 }

,
"ns" : "prod_ec.error_types",
"background" : true,
"name" : "app_id_1"
},
{
"key" :

{ "app_id" : 1, "app_version" : -1, "is_resolved" : 1, "updated_at" : -1 }

,
"ns" : "prod_ec.error_types",
"background" : true,
"name" : "app_id_1_app_version_-1_is_resolved_1_updated_at_-1"
}
]

> db.error_types.find(

{ is_resolved: false, app_id: ObjectId('4d15ce1a9ea1a313dd017dba')}

).sort(

{ updated_at: -1 }

)
error:

{ "$err" : "too much data for sort() with no index", "code" : 10129 }

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()
{
"cursor" : "BtreeCursor app_id_1_digest_1",
"nscanned" : 82,
"nscannedObjects" : 82,
"n" : 82,
"scanAndOrder" : true,
"millis" : 52,
"indexBounds" : {
"app_id" : [
[
ObjectId("4d15ce1a9ea1a313dd017dba"),
ObjectId("4d15ce1a9ea1a313dd017dba")
]
],
"digest" : [
[

{ "$minElement" : 1 }

,

{ "$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")
error:

{ "$err" : "too much data for sort() with no index", "code" : 10129 }

> 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")
error:

{ "$err" : "too much data for sort() with no index", "code" : 10129 }

<4d15ce1a9ea1a313dd017dba')}).sort(

{ updated_at: -1 }

).hint("app_id_1_app_version_-1_is_resolved_1_updated_at_-1").explain()
{
"cursor" : "BtreeCursor app_id_1_app_version_-1_is_resolved_1_updated_at_-1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"scanAndOrder" : true,
"millis" : 0,
"indexBounds" : {
"app_id" : [
[
ObjectId("4d15ce1a9ea1a313dd017dba"),
ObjectId("4d15ce1a9ea1a313dd017dba")
]
],
"app_version" : [
[

{ "$maxElement" : 1 }

,

{ "$minElement" : 1 }

]
],
"is_resolved" : [
[
false,
false
]
],
"updated_at" : [
[

"$maxElement" : 1
},

{ "$minElement" : 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.

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