mongo-explain-plans.md

Explain plans for activity queries

For help on interpreting see this reference.

All activities from a device

db.activity.find({"device": "sausages"}).explain()
{
    "cursor" : "BtreeCursor device_1_data.type_1_data.start_time_1_data.end_time_1",
    "isMultiKey" : false,
    "n" : 189,
    "nscannedObjects" : 189,
    "nscanned" : 189,
    "nscannedObjectsAllPlans" : 189,
    "nscannedAllPlans" : 189,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "device" : [
            [
                "sausages",
                "sausages"
            ]
        ],
        "data.type" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.start_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.end_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "existence:27017"
}

All walking activities from a device

db.activity.find({"device": "sausages", "data.type": "walking"}).explain()
{
    "cursor" : "BtreeCursor device_1_data.type_1_data.start_time_1_data.end_time_1",
    "isMultiKey" : false,
    "n" : 43,
    "nscannedObjects" : 43,
    "nscanned" : 43,
    "nscannedObjectsAllPlans" : 43,
    "nscannedAllPlans" : 43,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "device" : [
            [
                "sausages",
                "sausages"
            ]
        ],
        "data.type" : [
            [
                "walking",
                "walking"
            ]
        ],
        "data.start_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.end_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "existence:27017"
}

All of today's activities so far from a device

Note:

• end time strictly greater than start of today (in user's timezone)

db.activity.find({"device": "sausages", "data.end_time": {$gt: new Date("2015-01-22T00:00:00-06:00")}}).explain()
{
    "cursor" : "BtreeCursor device_1_data.type_1_data.start_time_1_data.end_time_1",
    "isMultiKey" : false,
    "n" : 189,
    "nscannedObjects" : 148,
    "nscanned" : 189,
    "nscannedObjectsAllPlans" : 217,
    "nscannedAllPlans" : 290,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "device" : [
            [
                "sausages",
                "sausages"
            ]
        ],
        "data.type" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.start_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.end_time" : [
            [
                ISODate("2015-01-22T06:00:00Z"),
                ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
            ]
        ]
    },
    "server" : "existence:27017"
}

All of today's walking activities so far from a device

Notes:

• end time strictly greater than start of today (in user's timezone)

db.activity.find({"device": "sausages", "data.type": "walking", "data.end_time": {$gt: new Date("2015-01-22T00:00:00-06:00")}}).explain()
{
    "cursor" : "BtreeCursor device_1_data.type_1_data.start_time_1_data.end_time_1",
    "isMultiKey" : false,
    "n" : 43,
    "nscannedObjects" : 43,
    "nscanned" : 43,
    "nscannedObjectsAllPlans" : 83,
    "nscannedAllPlans" : 86,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "device" : [
            [
                "sausages",
                "sausages"
            ]
        ],
        "data.type" : [
            [
                "walking",
                "walking"
            ]
        ],
        "data.start_time" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "data.end_time" : [
            [
                ISODate("2015-01-22T06:00:00Z"),
                ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
            ]
        ]
    },
    "server" : "existence:27017"
}

All of yesterday's walking activities from a device

Notes:

• start time strictly less than start of today (in user's timezone)
• end time strictly greater than start of today (in user's timezone)

db.activity.find({"device": "sausages", "data.type": "walking", "data.start_time": {$lt: new Date("2015-01-22T00:00:00-06:00")}, "data.end_time": {$gt: new Date("2015-01-21T00:00:00-06:00")}}).explain()
{
    "cursor" : "BtreeCursor device_1_data.type_1_data.start_time_1_data.end_time_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 0,
    "nscannedObjectsAllPlans" : 0,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "device" : [
            [
                "sausages",
                "sausages"
            ]
        ],
        "data.type" : [
            [
                "walking",
                "walking"
            ]
        ],
        "data.start_time" : [
            [
                true,
                ISODate("2015-01-22T06:00:00Z")
            ]
        ],
        "data.end_time" : [
            [
                ISODate("2015-01-21T06:00:00Z"),
                ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
            ]
        ]
    },
    "server" : "existence:27017"
}

Aggregation

Sum of all activities so far today

db.activity.aggregate(
...     [
...         { $match: { "device": "sausages", "data.end_time": { $gt: new Date("2015-01-22T00:00:00-06:00") } } },
...         { $group: { _id: "$data.type",  total: { $sum: "$data.duration" } } }
...     ], {
...         explain: true
...     }
... )
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"device" : "sausages",
					"data.end_time" : {
						"$gt" : ISODate("2015-01-22T06:00:00Z")
					}
				},
				"fields" : {
					"data.duration" : 1,
					"data.type" : 1,
					"_id" : 0
				},
				"plan" : {
					"cursor" : "BtreeCursor ",
					"isMultiKey" : false,
					"scanAndOrder" : false,
					"indexBounds" : {
						"device" : [
							[
								"sausages",
								"sausages"
							]
						],
						"data.type" : [
							[
								{
									"$minElement" : 1
								},
								{
									"$maxElement" : 1
								}
							]
						],
						"data.start_time" : [
							[
								{
									"$minElement" : 1
								},
								{
									"$maxElement" : 1
								}
							]
						],
						"data.end_time" : [
							[
								ISODate("2015-01-22T06:00:00Z"),
								ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
							]
						]
					},
					"allPlans" : [
						{
							"cursor" : "BtreeCursor ",
							"isMultiKey" : false,
							"scanAndOrder" : false,
							"indexBounds" : {
								"device" : [
									[
										"sausages",
										"sausages"
									]
								],
								"data.type" : [
									[
										{
											"$minElement" : 1
										},
										{
											"$maxElement" : 1
										}
									]
								],
								"data.start_time" : [
									[
										{
											"$minElement" : 1
										},
										{
											"$maxElement" : 1
										}
									]
								],
								"data.end_time" : [
									[
										ISODate("2015-01-22T06:00:00Z"),
										ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
									]
								]
							}
						}
					]
				}
			}
		},
		{
			"$group" : {
				"_id" : "$data.type",
				"total" : {
					"$sum" : "$data.duration"
				}
			}
		}
	],
	"ok" : 1
}