How to get totalDocsExamined with explain in pymongo

Working with Data Drivers
,
1

Hello guys.I am trying to see how many totalDocsExamined with explain on my query,but i dont get this kind of information.I need to run execution stats but i dont know how
This is my query

agg_pipeline=[
{"$match": {
"timestamp1": {"$gte": datetime.strptime("2020-01-01 00:00:00",
 "%Y-%m-%d %H:%M:%S"),
"$lte" :datetime.strptime("2020-12-31 01:55:00", "%Y-%m-%d %H:%M:%S")},
"id13": {"$gt": 5}}},
{"$group": {
"_id": {"$dateToString": {"format": "%Y-%m-%d %H",
 "date": "$timestamp1"}}}},
{"$sort": {"_id": -1}},
{ "$limit": 5},
{"$project": {
"_id": 0,
"hour":"$_id"}}

]

explain_output = mydb1.command('aggregate', 'mongodb2indextimestamp1', pipeline=agg_pipeline, explain=True)
pprint(explain_output)

And this is the the explain output:

{'ok': 1.0,
 'serverInfo': {'gitVersion': '72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7',
                'host': 'xaris-MS-7817',
                'port': 27017,
                'version': '4.4.6'},
 'stages': [{'$cursor': {'queryPlanner': {'indexFilterSet': False,
                                          'namespace': 'mongodbtime.mongodb2indextimestamp1',
                                          'parsedQuery': {'$and': [{'timestamp1': {'$lte': datetime.datetime(2020, 12, 31, 1, 55)}},
                                                                   {'id13': {'$gt': 5}},
                                                                   {'timestamp1': {'$gte': datetime.datetime(2020, 1, 1, 0, 0)}}]},
                                          'planCacheKey': '3A0C9E84',
                                          'plannerVersion': 1,
                                          'queryHash': 'DC05E87A',
                                          'rejectedPlans': [],
                                          'winningPlan': {'inputStage': {'direction': 'forward',
                                                                         'indexBounds': {'id13': ['(5, '
                                                                                                  'inf.0]'],
                                                                                         'timestamp1': ['[new '
                                                                                                        'Date(1609379700000), '
                                                                                                        'new '
                                                                                                        'Date(1577836800000)]']},
                                                                         'indexName': 'timestamp1_-1_id13_1',
                                                                         'indexVersion': 2,
                                                                         'isMultiKey': False,
                                                                         'isPartial': False,
                                                                         'isSparse': False,
                                                                         'isUnique': False,
                                                                         'keyPattern': {'id13': 1,
                                                                                        'timestamp1': -1},
                                                                         'multiKeyPaths': {'id13': [],
                                                                                           'timestamp1': []},
                                                                         'stage': 'IXSCAN'},
                                                          'stage': 'PROJECTION_COVERED',
                                                          'transformBy': {'_id': 0,
                                                                          'timestamp1': 1}}}}},
            {'$group': {'_id': {'$dateToString': {'date': '$timestamp1',
                                                  'format': {'$const': '%Y-%m-%d '
                                                                       '%H'}}}}},
            {'$sort': {'limit': 5, 'sortKey': {'_id': -1}}},
            {'$project': {'_id': False, 'hour': '$_id'}}]}

Is it possible to get information about "executionTimeMillis" "totalKeysExamined" "totalDocsExamined"?
Thanks in advance!

2

@Pavel_Duchovny Hello .Can you help me with that?

3

Hi @harris,

Perhaps try using the shell:

 agg_pipeline=[
{"$match": {
"timestamp1": {"$gte": ISODate("2020-01-01 00:00:00",
 "%Y-%m-%d %H:%M:%S"),
"$lte" :ISODate("2020-12-31 01:55:00", "%Y-%m-%d %H:%M:%S")},
"id13": {"$gt": 5}}},
{"$group": {
"_id": {"$dateToString": {"format": "%Y-%m-%d %H",
 "date": "$timestamp1"}}}},
{"$sort": {"_id": -1}},
{ "$limit": 5},
{"$project": {
"_id": 0,
"hour":"$_id"}}
];

db.mongodb2indextimestamp1.explain(true).aggregate(agg_pipeline);

Thanks
Pavel

4

Hello
Yes i i did that

agg_pipeline=[
{"$match": {
"timestamp1": {"$gte": datetime.strptime("2020-01-01 00:00:00",
 "%Y-%m-%d %H:%M:%S"),
"$lte" :datetime.strptime("2020-12-31 01:55:00", "%Y-%m-%d %H:%M:%S")},
"id13": {"$gt": 5}}},
{"$group": {
"_id": {"$dateToString": {"format": "%Y-%m-%d %H",
 "date": "$timestamp1"}}}},
{"$sort": {"_id": -1}},
{ "$limit": 5},
{"$project": {
"_id": 0,
"hour":"$_id"}}
]

mydb1.mongodb2indextimestamp1.explain('true').aggregate(agg_pipeline)

But the output says:'Collection' object is not callable. If you meant to call the 'explain' method on a 'Collection' object it is failing because no such method exist

1 Like
5

I do not think that explain('true') is the same as explain(true).

6

Yes but i code the queries with python.if dont use ‘true’ it gives me error name 'true' is not defined
and if i use mydb1.mongodb2indextimestamp1.explain(True).aggregate(agg_pipeline) the output says
'Collection' object is not callable. If you meant to call the 'explain' method on a 'Collection' object it is failing because no such method exists.

7

The method I provided is via a mongo shell its not for python.

I am not certain if its even possible in a python code to get it this way…

1 Like
8

Yes you are right.Can i ask you one last thing?My table contains 1.157.000 rows but i have used bucket pattern so i have 1 document that contains 12 subdocuments inside.I use the explain stat and i see something weird.It says that the planner used indexscan but it scanned the whole table.isnt this sequence scan?I post the query and the execution plan below.

agg_pipeline=[
{"$match": {
"samples.timestamp1": {"$gte": ISODate("2010-01-01 00:00:00",
 "%Y-%m-%d %H:%M:%S"),
"$lte" :ISODate("2020-12-31 01:55:00", "%Y-%m-%d %H:%M:%S")},
"id13": {"$gt": 5}}},
{"$unwind": "$samples"},
{"$match": {
"samples.id13": {"$gt": 5}}},
{"$group": {
"_id": {"$dateToString": {"format": "%Y-%m-%d %H",
 "date": "$samples.timestamp1"}},}},
{"$sort": {"_id": -1}},
{ "$limit": 5},
{"$project": {
"_id": 0,
"hour":"$_id"}}
];

and the explain is here

 db.mongodbbucketnocpu2index.explain(true).aggregate(agg_pipeline);
{
	"stages" : [
		{
			"$cursor" : {
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "mongodbtime.mongodbbucketnocpu2index",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"samples.timestamp1" : {
									"$lte" : ISODate("2020-12-31T01:55:00Z")
								}
							},
							{
								"id13" : {
									"$gt" : 5
								}
							},
							{
								"samples.timestamp1" : {
									"$gte" : ISODate("2010-01-01T00:00:00Z")
								}
							}
						]
					},
					"queryHash" : "D8B2A1E8",
					"planCacheKey" : "322C4E92",
					"winningPlan" : {
						"stage" : "PROJECTION_SIMPLE",
						"transformBy" : {
							"samples" : 1,
							"_id" : 0
						},
						"inputStage" : {
							"stage" : "FETCH",
							"filter" : {
								"$and" : [
									{
										"id13" : {
											"$gt" : 5
										}
									},
									{
										"samples.timestamp1" : {
											"$gte" : ISODate("2010-01-01T00:00:00Z")
										}
									}
								]
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"samples.timestamp1" : -1,
									"samples.id13" : 1
								},
								"indexName" : "samples.timestamp1_-1_samples.id13_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"samples.timestamp1" : [
										"samples"
									],
									"samples.id13" : [
										"samples"
									]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"samples.timestamp1" : [
										"[new Date(1609379700000), new Date(-9223372036854775808)]"
									],
									"samples.id13" : [
										"[MinKey, MaxKey]"
									]
								}
							}
						}
					},
					"rejectedPlans" : [
						{
							"stage" : "PROJECTION_SIMPLE",
							"transformBy" : {
								"samples" : 1,
								"_id" : 0
							},
							"inputStage" : {
								"stage" : "FETCH",
								"filter" : {
									"$and" : [
										{
											"id13" : {
												"$gt" : 5
											}
										},
										{
											"samples.timestamp1" : {
												"$lte" : ISODate("2020-12-31T01:55:00Z")
											}
										}
									]
								},
								"inputStage" : {
									"stage" : "IXSCAN",
									"keyPattern" : {
										"samples.timestamp1" : -1,
										"samples.id13" : 1
									},
									"indexName" : "samples.timestamp1_-1_samples.id13_1",
									"isMultiKey" : true,
									"multiKeyPaths" : {
										"samples.timestamp1" : [
											"samples"
										],
										"samples.id13" : [
											"samples"
										]
									},
									"isUnique" : false,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"samples.timestamp1" : [
											"[new Date(9223372036854775807), new Date(1262304000000)]"
										],
										"samples.id13" : [
											"[MinKey, MaxKey]"
										]
									}
								}
							}
						}
					]
				},
				"executionStats" : {
					"executionSuccess" : true,
					"nReturned" : 0,
					"executionTimeMillis" : 582,
					"totalKeysExamined" : 1156920,
					"totalDocsExamined" : 96410,
					"executionStages" : {
						"stage" : "PROJECTION_SIMPLE",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 54,
						"works" : 1156921,
						"advanced" : 0,
						"needTime" : 1156920,
						"needYield" : 0,
						"saveState" : 1186,
						"restoreState" : 1186,
						"isEOF" : 1,
						"transformBy" : {
							"samples" : 1,
							"_id" : 0
						},
						"inputStage" : {
							"stage" : "FETCH",
							"filter" : {
								"$and" : [
									{
										"id13" : {
											"$gt" : 5
										}
									},
									{
										"samples.timestamp1" : {
											"$gte" : ISODate("2010-01-01T00:00:00Z")
										}
									}
								]
							},
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 52,
							"works" : 1156921,
							"advanced" : 0,
							"needTime" : 1156920,
							"needYield" : 0,
							"saveState" : 1186,
							"restoreState" : 1186,
							"isEOF" : 1,
							"docsExamined" : 96410,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 96410,
								"executionTimeMillisEstimate" : 47,
								"works" : 1156921,
								"advanced" : 96410,
								"needTime" : 1060510,
								"needYield" : 0,
								"saveState" : 1186,
								"restoreState" : 1186,
								"isEOF" : 1,
								"keyPattern" : {
									"samples.timestamp1" : -1,
									"samples.id13" : 1
								},
								"indexName" : "samples.timestamp1_-1_samples.id13_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"samples.timestamp1" : [
										"samples"
									],
									"samples.id13" : [
										"samples"
									]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"samples.timestamp1" : [
										"[new Date(1609379700000), new Date(-9223372036854775808)]"
									],
									"samples.id13" : [
										"[MinKey, MaxKey]"
									]
								},
								"keysExamined" : 1156920,
								"seeks" : 1,
								"dupsTested" : 1156920,
								"dupsDropped" : 1060510
							}
						}
					},
					"allPlansExecution" : [
						{
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"totalKeysExamined" : 28929,
							"totalDocsExamined" : 2411,
							"executionStages" : {
								"stage" : "PROJECTION_SIMPLE",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 28929,
								"advanced" : 0,
								"needTime" : 28929,
								"needYield" : 0,
								"saveState" : 58,
								"restoreState" : 57,
								"isEOF" : 0,
								"transformBy" : {
									"samples" : 1,
									"_id" : 0
								},
								"inputStage" : {
									"stage" : "FETCH",
									"filter" : {
										"$and" : [
											{
												"id13" : {
													"$gt" : 5
												}
											},
											{
												"samples.timestamp1" : {
													"$gte" : ISODate("2010-01-01T00:00:00Z")
												}
											}
										]
									},
									"nReturned" : 0,
									"executionTimeMillisEstimate" : 0,
									"works" : 28929,
									"advanced" : 0,
									"needTime" : 28929,
									"needYield" : 0,
									"saveState" : 58,
									"restoreState" : 57,
									"isEOF" : 0,
									"docsExamined" : 2411,
									"alreadyHasObj" : 0,
									"inputStage" : {
										"stage" : "IXSCAN",
										"nReturned" : 2411,
										"executionTimeMillisEstimate" : 0,
										"works" : 28929,
										"advanced" : 2411,
										"needTime" : 26518,
										"needYield" : 0,
										"saveState" : 58,
										"restoreState" : 57,
										"isEOF" : 0,
										"keyPattern" : {
											"samples.timestamp1" : -1,
											"samples.id13" : 1
										},
										"indexName" : "samples.timestamp1_-1_samples.id13_1",
										"isMultiKey" : true,
										"multiKeyPaths" : {
											"samples.timestamp1" : [
												"samples"
											],
											"samples.id13" : [
												"samples"
											]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"samples.timestamp1" : [
												"[new Date(1609379700000), new Date(-9223372036854775808)]"
											],
											"samples.id13" : [
												"[MinKey, MaxKey]"
											]
										},
										"keysExamined" : 28929,
										"seeks" : 1,
										"dupsTested" : 28929,
										"dupsDropped" : 26518
									}
								}
							}
						},
						{
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 3,
							"totalKeysExamined" : 28929,
							"totalDocsExamined" : 2412,
							"executionStages" : {
								"stage" : "PROJECTION_SIMPLE",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 3,
								"works" : 28929,
								"advanced" : 0,
								"needTime" : 28929,
								"needYield" : 0,
								"saveState" : 1186,
								"restoreState" : 1186,
								"isEOF" : 0,
								"transformBy" : {
									"samples" : 1,
									"_id" : 0
								},
								"inputStage" : {
									"stage" : "FETCH",
									"filter" : {
										"$and" : [
											{
												"id13" : {
													"$gt" : 5
												}
											},
											{
												"samples.timestamp1" : {
													"$lte" : ISODate("2020-12-31T01:55:00Z")
												}
											}
										]
									},
									"nReturned" : 0,
									"executionTimeMillisEstimate" : 3,
									"works" : 28929,
									"advanced" : 0,
									"needTime" : 28929,
									"needYield" : 0,
									"saveState" : 1186,
									"restoreState" : 1186,
									"isEOF" : 0,
									"docsExamined" : 2412,
									"alreadyHasObj" : 0,
									"inputStage" : {
										"stage" : "IXSCAN",
										"nReturned" : 2412,
										"executionTimeMillisEstimate" : 3,
										"works" : 28929,
										"advanced" : 2412,
										"needTime" : 26517,
										"needYield" : 0,
										"saveState" : 1186,
										"restoreState" : 1186,
										"isEOF" : 0,
										"keyPattern" : {
											"samples.timestamp1" : -1,
											"samples.id13" : 1
										},
										"indexName" : "samples.timestamp1_-1_samples.id13_1",
										"isMultiKey" : true,
										"multiKeyPaths" : {
											"samples.timestamp1" : [
												"samples"
											],
											"samples.id13" : [
												"samples"
											]
										},
										"isUnique" : false,
										"isSparse" : false,
										"isPartial" : false,
										"indexVersion" : 2,
										"direction" : "forward",
										"indexBounds" : {
											"samples.timestamp1" : [
												"[new Date(9223372036854775807), new Date(1262304000000)]"
											],
											"samples.id13" : [
												"[MinKey, MaxKey]"
											]
										},
										"keysExamined" : 28929,
										"seeks" : 1,
										"dupsTested" : 28929,
										"dupsDropped" : 26517
									}
								}
							}
						}
					]
				}
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		},
		{
			"$unwind" : {
				"path" : "$samples"
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		},
		{
			"$match" : {
				"samples.id13" : {
					"$gt" : 5
				}
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		},
		{
			"$group" : {
				"_id" : {
					"$dateToString" : {
						"date" : "$samples.timestamp1",
						"format" : {
							"$const" : "%Y-%m-%d %H"
						}
					}
				}
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		},
		{
			"$sort" : {
				"sortKey" : {
					"_id" : -1
				},
				"limit" : NumberLong(5)
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		},
		{
			"$project" : {
				"hour" : "$_id",
				"_id" : false
			},
			"nReturned" : NumberLong(0),
			"executionTimeMillisEstimate" : NumberLong(552)
		}
	],
	"serverInfo" : {
		"host" : "xaris-MS-7817",
		"port" : 27017,
		"version" : "4.4.6",
		"gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
	},
	"ok" : 1
}
9

Hello @harris,

In PyMongo, there is only one way to specify the explain on aggregate method - that is via the db.command (that is same as mongo shell’s runCommand). And, there are no option to specify the executionStats and allPlansExecution modes. This is an earlier post discussing the syntax:

10

Thank you @Prasad_Saya.If you have spare time can you take a look on my explain above.The explain says that we do an index scan,but it scanned all the rows of the table(1.157.000).Is there an explanation for that ?

					"totalKeysExamined" : 1156920,
					"totalDocsExamined" : 96410,
11

Hello harri

winningPlan
  "stage" : "IXSCAN",
  "keyPattern" : {
                  "samples.timestamp1" : -1,
		   "samples.id13" : 1
		 },
  "indexName" : "samples.timestamp1_-1_samples.id13_1"
  
"executionStats" : 
  "executionSuccess" : true,
  "nReturned" : 0,
  "executionTimeMillis" : 582,
  "totalKeysExamined" : 1156920,
  "totalDocsExamined" : 96410,

Looks like the index was used,1156920 index keys examined,and from them were FETCHED
96410 documents. (fetch is consindered examined also)
Even if an index is used,to get the other information from the documents,still FETCH is needed.
If it was collection scan it would say COLLSCAN not IXSCAN ,and totalDocsExamined equal
to the collection size ndocs 1million +

This page from Docs is very useful

12

@harris,

This query is not very selective as it does only range query over a multikey index which is not considered selective , therefore lots of keys are scanned.

See the following documentation for more:

Thanks
Pavel

1 Like