[SERVER-14070] Compound index not providing sort if equality predicate given on sort field Created: 27/May/14  Updated: 16/Jul/15  Resolved: 12/Dec/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 2.6.11, 2.8.0-rc3

Type: Improvement Priority: Major - P3
Reporter: J Rassi 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-14034 Sorted $in query with large number of... Closed
Backwards Compatibility: Fully Compatible
Backport Completed:
Participants:

 Description   

Queries that can use a compound index to provide a sort on a subset of fields in the key pattern are sometimes given an unnecessary in-memory sort if an equality predicate is given on the sort field.

Reproduce with:

> db.foo.ensureIndex({a:1,b:1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.find({a:1}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:1,b:{$gt:0}}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:1,b:/hello/}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:1,b:1}).sort({b:1}).explain().scanAndOrder
true // INCORRECT



 Comments   
Comment by Githook User [ 16/Jul/15 ]

Author:

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

Message: SERVER-14070 sort analysis computes additional sort orders provided by an index scan

(cherry picked from commit 5120700460ef7a3240603a4307a0b1be2c358c3c)
Branch: v2.6
https://github.com/mongodb/mongo/commit/ae6e9f7e728056a21fa3d392a13aabf770579f34

Comment by Githook User [ 12/Dec/14 ]

Author:

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

Message: SERVER-14070 fix planner_analysis_test compile on OS X
Branch: master
https://github.com/mongodb/mongo/commit/625a098b71e7217bad59ad483fa32d4abcc66436

Comment by Githook User [ 12/Dec/14 ]

Author:

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

Message: SERVER-14070 sort analysis computes additional sort orders provided by an index scan
Branch: master
https://github.com/mongodb/mongo/commit/5120700460ef7a3240603a4307a0b1be2c358c3c

Comment by J Rassi [ 27/May/14 ]

I presume that the same issue is affecting the following set of examples also, in which specifying an equality predicate on the sort field is preventing the "explode for merge sort" logic from being applied:

> db.foo.ensureIndex({a:1,b:1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.find({a:{$in:[0,1]}}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:{$in:[0,1]},b:{$gt:0}}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:{$in:[0,1]},b:/hello/}).sort({b:1}).explain().scanAndOrder
false // CORRECT
> db.foo.find({a:{$in:[0,1]},b:1}).sort({b:1}).explain().scanAndOrder
true // INCORRECT

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