[SERVER-15059] index on date range on embedded collection is not used correctly Created: 27/Aug/14  Updated: 03/Sep/14  Resolved: 03/Sep/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.4.8, 2.6.3
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Roman Gavrilov Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-15086 Allow for efficient range queries ove... Closed
Related
related to SERVER-6720 Range query with compound multikey in... Closed
related to SERVER-14618 Wrong index bounds when using "hint" Closed
Operating System: ALL
Participants:

 Description   

given the following collection:

db.test.find().pretty()
{
	"_id" : ObjectId("53fe2798a3cfa2769eb8ca96"),
	"name" : "jon",
	"a" : [
		{
			"someDate" : ISODate("2011-02-28T12:49:00Z")
		},
		{
			"someDate" : ISODate("2011-03-31T11:56:00Z")
		},
		{
			"someDate" : ISODate("2012-12-03T20:22:00Z")
		}
	]
}

and the following indexes:

db.test.getIndexes()
[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"ns" : "db.test",
		"name" : "_id_"
	},
	{
		"v" : 1,
		"key" : {
			"a.someDate" : 1
		},
		"ns" : "db.test",
		"name" : "a.someDate_1"
	}
]

here is the explain of the query:

db.test.find({'a' :{$elemMatch : {'someDate':{$gte:ISODate("2011-03-01T00:00:00"), $lte:ISODate("2011-05-01T00:00:00")}}}}).explain()
{
	"cursor" : "BtreeCursor a.someDate_1",
	"isMultiKey" : true,
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a.someDate" : [
			[
				ISODate("2011-03-01T00:00:00Z"),
				ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
			]
		]
	},
	"server" : "local":27017"
}

1. the collection is being scanned twice
2. upper index bound is NaN

I would expect the index to be used for this range query with proper lower/upper bounds.



 Comments   
Comment by Ramon Fernandez Marina [ 03/Sep/14 ]

rgavrilov@remedypartners.com, the explain() output on 2.6 has the right numbers for nscanned and nscannedObjects, so the difference with the explain() output you posted is probably a 2.4 issue when collecting these statistics that got fixed for 2.6.

In 2.6 the output of explain() looks as follows:

db.test.find({'a' :{$elemMatch : {'someDate':{$gte:ISODate("2011-03-01T00:00:00"), $lte:ISODate("2011-05-01T00:00:00")}}}}).explain()
{
        "cursor" : "BtreeCursor a.someDate_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 6,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 1,
        "indexBounds" : {
                "a.someDate" : [
                        [
                                true,
                                ISODate("2011-05-01T00:00:00Z")
                        ]
                ]
        },
        "server" : "skye.local:27017",
        "filterSet" : false
}

As for the index bounds, this has already been reported in tickets such as SERVER-6720 or SERVER-14618, and it is expected behavior for multikey indexes.

We think there's room for improvement in this front though, and we're using SERVER-15086 for that – feel free to tune into that ticket for progress updates.

Regards,
Ramón.

Comment by Roman Gavrilov [ 03/Sep/14 ]

@ramon what is the expected response/confirmation time on tickets?
this is a major blocker for us.

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