Loading...

Type: Improvement
Resolution: Done
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 2.2.0
Component/s: Index Maintenance, Performance
Labels:
- indexing
- performance
Environment:
Ubuntu 12.04 LTS

Explain function show a difference of index's usage with embedded document. nscanned is increase with enbedded document.

Example :

Embedded document

 
db.test.insert({"code" : "000001", doc : {"code1" : "000001", "type" : [ "1" ,"3" ]}});
db.test.insert({"code" : "000002", doc : {"code1" : "000001", "type" : [ "1" ]}});
db.test.insert({"code" : "000003", doc : {"code1" : "000001", "type" : [ "1", "2"] }});
db.test.insert({"code" : "000004", doc : {"code1" : "000001", "type" : [ "2" ]}});
db.test.insert({"code" : "000005", doc : {"code1" : "000001", "type" : [ ]}});
db.test.insert({"code" : "000006", doc : {"code1" : "000001", "type" : [ "3" ]}});
db.test.insert({"code" : "000007", doc : {"code1" : "000001" }});
db.test.insert({"code" : "000001", doc : {"code1" : "000002", "type" : [ "1" ,"3" ]}});
db.test.insert({"code" : "000002", doc : {"code1" : "000002", "type" : [ "1" ]}});
db.test.insert({"code" : "000003", doc : {"code1" : "000002", "type" : [ "1", "2"] }});
db.test.insert({"code" : "000004", doc : {"code1" : "000002", "type" : [ "2" ]}});
db.test.insert({"code" : "000005", doc : {"code1" : "000002", "type" : [ ]}});
db.test.insert({"code" : "000006", doc : {"code1" : "000002", "type" : [ "3" ]}});
db.test.insert({"code" : "000007", doc : {"code1" : "000002" }});
db.test.insert({"code" : "000001", doc : {"code1" : "000003", "type" : [ "1" ,"3" ]}});
db.test.insert({"code" : "000002", doc : {"code1" : "000003", "type" : [ "1" ]}});
db.test.insert({"code" : "000003", doc : {"code1" : "000003", "type" : [ "1", "2"] }});
db.test.insert({"code" : "000004", doc : {"code1" : "000003", "type" : [ "2" ]}});
db.test.insert({"code" : "000005", doc : {"code1" : "000003", "type" : [ ]}});
db.test.insert({"code" : "000006", doc : {"code1" : "000003", "type" : [ "3" ]}});
db.test.insert({"code" : "000007", doc : {"code1" : "000003" }});

db.test.ensureIndex( { "doc.code1": 1, "doc.type": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test"} )

db.test.find( { "$or":[ { "doc.code1" : "000001" }, { "doc.code1" : "000003" } ], "doc.type" : "2" } ).explain()
{
	"clauses" : [
		{
			"cursor" : "BtreeCursor index_test",
			"isMultiKey" : true,
			"n" : 2,
			"nscannedObjects" : 9,
			"nscanned" : 9,
			"nscannedObjectsAllPlans" : 9,
			"nscannedAllPlans" : 9,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nYields" : 0,
			"nChunkSkips" : 0,
			"millis" : 0,
			"indexBounds" : {
				"doc.code1" : [
					[
						"000001",
						"000001"
					]
				],
				"doc.type" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				],
				"code" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor index_test",
			"isMultiKey" : true,
			"n" : 2,
			"nscannedObjects" : 9,
			"nscanned" : 9,
			"nscannedObjectsAllPlans" : 9,
			"nscannedAllPlans" : 9,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nYields" : 0,
			"nChunkSkips" : 0,
			"millis" : 0,
			"indexBounds" : {
				"doc.code1" : [
					[
						"000003",
						"000003"
					]
				],
				"doc.type" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				],
				"code" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				]
			}
		}
	],
	"n" : 4,
	"nscannedObjects" : 18,
	"nscanned" : 18,
	"nscannedObjectsAllPlans" : 18,
	"nscannedAllPlans" : 18,
	"millis" : 0,
	"server" : "xxx:27017"
}

Not embedded document

 
db.test2.insert({"code" : "000001", "code1" : "000001", "type" : [ "1" ,"3" ]});
db.test2.insert({"code" : "000002", "code1" : "000001", "type" : [ "1" ]});
db.test2.insert({"code" : "000003", "code1" : "000001", "type" : [ "1", "2"] });
db.test2.insert({"code" : "000004", "code1" : "000001", "type" : [ "2" ]});
db.test2.insert({"code" : "000005", "code1" : "000001", "type" : [ ]});
db.test2.insert({"code" : "000006", "code1" : "000001", "type" : [ "3" ]});
db.test2.insert({"code" : "000007", "code1" : "000001" });
db.test2.insert({"code" : "000001", "code1" : "000002", "type" : [ "1" ,"3" ]});
db.test2.insert({"code" : "000002", "code1" : "000002", "type" : [ "1" ]});
db.test2.insert({"code" : "000003", "code1" : "000002", "type" : [ "1", "2"] });
db.test2.insert({"code" : "000004", "code1" : "000002", "type" : [ "2" ]});
db.test2.insert({"code" : "000005", "code1" : "000002", "type" : [ ]});
db.test2.insert({"code" : "000006", "code1" : "000002", "type" : [ "3" ]});
db.test2.insert({"code" : "000007", "code1" : "000002" });
db.test2.insert({"code" : "000001", "code1" : "000003", "type" : [ "1" ,"3" ]});
db.test2.insert({"code" : "000002", "code1" : "000003", "type" : [ "1" ]});
db.test2.insert({"code" : "000003", "code1" : "000003", "type" : [ "1", "2"] });
db.test2.insert({"code" : "000004", "code1" : "000003", "type" : [ "2" ]});
db.test2.insert({"code" : "000005", "code1" : "000003", "type" : [ ]});
db.test2.insert({"code" : "000006", "code1" : "000003", "type" : [ "3" ]});
db.test2.insert({"code" : "000007", "code1" : "000003" });

db.test2.ensureIndex( { "code1": 1, "type": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test2"} )

db.test2.find( { "$or":[ { "code1" : "000001" }, { "code1" : "000003" } ], "type" : "2" } ).explain()
{
	"clauses" : [
		{
			"cursor" : "BtreeCursor index_test2",
			"isMultiKey" : true,
			"n" : 2,
			"nscannedObjects" : 2,
			"nscanned" : 2,
			"nscannedObjectsAllPlans" : 2,
			"nscannedAllPlans" : 2,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nYields" : 0,
			"nChunkSkips" : 0,
			"millis" : 0,
			"indexBounds" : {
				"code1" : [
					[
						"000001",
						"000001"
					]
				],
				"type" : [
					[
						"2",
						"2"
					]
				],
				"code" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor index_test2",
			"isMultiKey" : true,
			"n" : 2,
			"nscannedObjects" : 2,
			"nscanned" : 2,
			"nscannedObjectsAllPlans" : 2,
			"nscannedAllPlans" : 2,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nYields" : 0,
			"nChunkSkips" : 0,
			"millis" : 0,
			"indexBounds" : {
				"code1" : [
					[
						"000003",
						"000003"
					]
				],
				"type" : [
					[
						"2",
						"2"
					]
				],
				"code" : [
					[
						{
							"$maxElement" : 1
						},
						{
							"$minElement" : 1
						}
					]
				]
			}
		}
	],
	"n" : 4,
	"nscannedObjects" : 4,
	"nscanned" : 4,
	"nscannedObjectsAllPlans" : 4,
	"nscannedAllPlans" : 4,
	"millis" : 0,
	"server" : "xxx:27017"
}

related to

SERVER-7509 Combine index ranges for dotted fields within a $elemMatch expression, when possible

Closed

Details

Description

Attachments

Issue Links

Activity

People

Dates