[SERVER-11839] index is not used to provide sort for $not query Created: 23/Nov/13  Updated: 11/Jul/16  Resolved: 04/Dec/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 2.5.5

Type: Bug Priority: Major - P3
Reporter: Paul Klimashkin Assignee: Benety Goh
Resolution: Done Votes: 0
Labels: query, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows 7, Gentoo


Attachments: File server11839.js    
Issue Links:
Depends
depends on SERVER-11446 don't punt all not/nor to collscan. Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Participants:

 Description   

explain output from 2.5.4 indicates that an in-memory sort (scanAndSort=true) is used to order results of $not query. The result set is large enough that it is hitting the internal limit of the in-memory sort - see assertion message. This is different from 2.5.3 where the query optimizer is able to utilize the index (scanAndSort=false).


After updating from 2.5.3 to 2.5.4 start to get error messages for quires with sorting:

query:
{[db.photos.find({ $or: [

{ r1: 1, s: 5 }

, { r1: 3, s:

{ $ne: 9 }

} ] }).sort(

{sdate: -1}

, {_id:0, cid: 1}).limit(30).explain()}}

indexes
{ "v" : 1, "key" :

{ "sdate" : 1 }

, "name" : "sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r0" : 1 }

, "name" : "r0_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r1" : 1 }

, "name" : "r1_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r2" : 1 }

, "name" : "r2_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r3" : 1 }

, "name" : "r3_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r4" : 1 }

, "name" : "r4_1", "ns" : "pastvu.photos", "sparse" : true, "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "s" : 1 }

, "name" : "s_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r0" : 1, "sdate" : 1 }

, "name" : "r0_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r1" : 1, "sdate" : 1 }

, "name" : "r1_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r2" : 1, "sdate" : 1 }

, "name" : "r2_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r3" : 1, "sdate" : 1 }

, "name" : "r3_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }
{ "v" : 1, "key" :

{ "r4" : 1, "sdate" : 1 }

, "name" : "r4_1_sdate_1", "ns" : "pastvu.photos", "background" : true, "safe" : null }

2.5.3:
{
"cursor" : "BtreeCursor sdate_1 reverse",
"isMultiKey" : false,
"n" : 30,
"nscannedObjects" : 270,
"nscanned" : 270,
"nscannedObjectsAllPlans" : 270,
"nscannedAllPlans" : 270,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 116,
"indexBounds" : {
"sdate" : [
[

{ "$maxElement" : 1 }

,

{ "$minElement" : 1 }

]
]
}
}

2.5.4:
2013-11-23T19:37:02.040+0400 error: {
"$err" : "Runner error, memory limit for sort probably exceeded",
"code" : 17144
} at src/mongo/shell/query.js:131


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