[SERVER-7509] Combine index ranges for dotted fields within a $elemMatch expression, when possible Created: 30/Oct/12  Updated: 14/Apr/16  Resolved: 27/Jan/15

Status: Closed
Project: Core Server
Component/s: Performance, Querying
Affects Version/s: 2.3.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Lucas Pouzac Assignee: David Storch
Resolution: Duplicate Votes: 3
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu 12.04 LTS


Issue Links:
Duplicate
duplicates SERVER-10026 New query system Closed
Related
is related to SERVER-3104 index bound improvements for elemMatc... Closed
is related to SERVER-7354 Index with enbedded document Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Participants:

 Description   

Implement the SERVER-3104 optimization for dotted field cases where it can be supported. Note that it cannot be supported in some cases such as the following:

// Cases with double dotted index field names.
t.drop();
index = { 'a.b.x':1, 'a.b.y':1 };
t.ensureIndex( index );
t.save( { a:{ b:{ x:1, y:1 } } } );
t.save( { a:[ { b:{ x:1 } }, { b:{ y:1 } } ] } );
t.save( { a:[ { b:[ { x:1 }, { y:1 } ] } ] } );
t.save( { a:[ { b:[ { x:1, y:1 } ] } ] } );
assert.eq( 4, t.count() );
// No $elemMatch.
assertResultsAndIndexBounds( { 'a.b.x':[[ 1, 1 ]], 'a.b.y':[[ MIN, MAX ]] },
                             { 'a.b.x':1, 'a.b.y':1 } );
// $elemMatch with dotted children.
assertResultsAndIndexBounds( { 'a.b.x':[[ 1, 1 ]], 'a.b.y':[[ MIN, MAX ]] },
                             { a:{ $elemMatch:{ 'b.x':1, 'b.y':1 } } } );
// $elemMatch with undotted children.
assertResultsAndIndexBounds( { 'a.b.x':[[ 1, 1 ]], 'a.b.y':[[ 1, 1 ]] },
                             { 'a.b':{ $elemMatch:{ x:1, y:1 } } } );

Aaron

-----------------------------------------------------------

the SERVER-3104 optimization will not work correctly when there are dotted fields within a $elemMatch expression

 
db.test.insert({"code" : "000001", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "1" ,"3" ]}}]});
db.test.insert({"code" : "000002", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "1" ]}}]});
db.test.insert({"code" : "000003", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "1", "2"] }}]});
db.test.insert({"code" : "000004", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "2" ]}}, {"code1" : "000002", "type" : { "subtype" : [ "3" ]}} ]});
db.test.insert({"code" : "000005", doc : [ {"code1" : "000001", "type" : { "subtype" : [ ]}}]});
db.test.insert({"code" : "000006", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "3" ]}}]});
db.test.insert({"code" : "000007", doc : [ {"code1" : "000001" }]});
 
db.test.insert({"code" : "000001", doc : [ {"code1" : "000002", "type" : { "subtype" : [ "1" ,"3" ]}}]});
db.test.insert({"code" : "000002", doc : [ {"code1" : "000002", "type" : { "subtype" : [ "1" ]}}]});
db.test.insert({"code" : "000003", doc : [ {"code1" : "000002", "type" : { "subtype" : [ "1", "2"] }}]});
db.test.insert({"code" : "000004", doc : [ {"code1" : "000002", "type" : { "subtype" : [ "2" ]}}, {"code1" : "000001", "type" : { "subtype" : [ "3" ]}} ]});
db.test.insert({"code" : "000005", doc : [ {"code1" : "000002", "type" : { "subtype" : [ ]}}]});
db.test.insert({"code" : "000006", doc : [ {"code1" : "000002", "type" : { "subtype" : [ "3" ]}}]});
db.test.insert({"code" : "000007", doc : [ {"code1" : "000002" }]});
 
db.test.insert({"code" : "000001", doc : [ {"code1" : "000003", "type" : { "subtype" : [ "1" ,"3" ]}}]});
db.test.insert({"code" : "000002", doc : [ {"code1" : "000003", "type" : { "subtype" : [ "1" ]}}]});
db.test.insert({"code" : "000003", doc : [ {"code1" : "000003", "type" : { "subtype" : [ "1", "2"] }}]});
db.test.insert({"code" : "000004", doc : [ {"code1" : "000003", "type" : { "subtype" : [ "2" ]}}]});
db.test.insert({"code" : "000005", doc : [ {"code1" : "000003", "type" : { "subtype" : [ ]}}]});
db.test.insert({"code" : "000006", doc : [ {"code1" : "000003", "type" : { "subtype" : [ "3" ]}}]});
db.test.insert({"code" : "000007", doc : [ {"code1" : "000003" }]});
db.test.ensureIndex( { "doc.code1": 1, "doc.type.subtype": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test"} )
 
db.test.find( { "doc" : { "$elemMatch" : { "code1" : { "$in" :[ "000001", "000003" ] }, "type.subtype" : "2" } } } ).explain()
{
    "cursor" : "BtreeCursor index_test multi",
    "isMultiKey" : true,
    "n" : 4,
    "nscannedObjects" : 19,
    "nscanned" : 20,
    "nscannedObjectsAllPlans" : 39,
    "nscannedAllPlans" : 40,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "doc.code1" : [
            [
                "000001",
                "000001"
            ],
            [
                "000003",
                "000003"
            ]
        ],
        "doc.type.subtype" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ],
        "code" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "xxx:27017"
}



 Comments   
Comment by David Storch [ 27/Jan/15 ]

This has been fixed by the query system rewrite for 2.6:

> db.version()
2.6.7
> db.test.drop()
> db.test.insert({"code" : "000001", doc : [ {"code1" : "000001", "type" : { "subtype" : [ "1" ,"3" ]}}]});
> db.test.ensureIndex( { "doc.code1": 1, "doc.type.subtype": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test"} )
> db.test.find( { "doc" : { "$elemMatch" : { "code1" : { "$in" :[ "000001", "000003" ] }, "type.subtype" : "2" } } } ).explain()
{
	"cursor" : "BtreeCursor index_test",
	"isMultiKey" : true,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"doc.code1" : [
			[
				"000001",
				"000001"
			],
			[
				"000003",
				"000003"
			]
		],
		"doc.type.subtype" : [ // Here's the fix: this field would have bounds [[MIN, MAX]] in 2.4; now it has bounds [[2, 2]]
			[
				"2",
				"2"
			]
		],
		"code" : [
			[
				{
					"$maxElement" : 1
				},
				{
					"$minElement" : 1
				}
			]
		]
	},
	"server" : "MacBook-Pro:27017",
	"filterSet" : false
}

Closing as a duplicate of SERVER-10026.

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