Scribd
Upload
9 views12 pages

MongoDB Query Execution Analysis

The document provides detailed query performance summaries for MongoDB operations including find and aggregate commands. It highlights the number of documents returned, execution time, and stages of query execution such as COLLSCAN and SORT, with specific metrics for ascending and descending sorts. The performance metrics indicate that the queries examined 10,000 documents with varying execution times, primarily relying on collection scans.

Uploaded by

lili 879
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views12 pages

MongoDB Query Execution Analysis

The document provides detailed query performance summaries for MongoDB operations including find and aggregate commands. It highlights the number of documents returned, execution time, and stages of query execution such as COLLSCAN and SORT, with specific metrics for ascending and descending sorts. The performance metrics indicate that the queries examined 10,000 documents with varying execution times, primarily relying on collection scans.

Uploaded by

lili 879
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

find() => Query Performance Summary (MongoDB Compass Explain)

1. Documents Returned:10000
2. index Keys Examined:0
3. Documents Examined:10000
4. Actual Query Execution Time (ms):7
5. Sorted in Memory:no
6. COLLSCAN Details {
"stage": "COLLSCAN",
"nReturned": 10000,
"executionTimeMillisEstimate": 5,
"works": 10002,
"advanced": 10000,
"needTime": 1,
"needYield": 0,
"saveState": 78,
"restoreState": 78,
"isEOF": 1,
"direction": "forward",
"docsExamined": 10000
}
-----------------------------------------------------------------------------------
----------------------------------------
find() => Query Performance Summary (MongoDB Shell Command)
-> Query : [Link]().sort({"Name":
1}).pretty().explain("executionStats")

results :

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "[Link]",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10000,
"executionTimeMillis" : 3,
"totalKeysExamined" : 0,
"totalDocsExamined" : 10000,
"executionStages" : {
"stage" : "COLLSCAN",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 0,
"works" : 10002,
"advanced" : 10000,
"needTime" : 1,
"needYield" : 0,
"saveState" : 78,
"restoreState" : 78,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 10000
}
},
"serverInfo" : {
"host" : "[Link]",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593490710, 12),
"signature" : {
"hash" : BinData(0,"UL+oqkSOp7dGR42y/Fdi6DTvuQI="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593490710, 12)
}

===================================================================================
========================================

sort() ASCENDING => Query Performance Summary (MongoDB Compass Explain) ->
{ "Name": 1 }
1. Documents Returned:10000
2. Index Keys Examined:0
3. Documents Examined:10000
4. Actual Query Execution Time (ms):32
5. Sorted in Memory:yes
6. SORT -> {
"stage": "SORT",
"nReturned": 10000,
"executionTimeMillisEstimate": 11,
"works": 20004,
"advanced": 10000,
"needTime": 10003,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"sortPattern": {
"Name": 1
},
"memUsage": 4144208,
"memLimit": 33554432
}
SORT_KEY GENERATOR -> {
"stage": "SORT_KEY_GENERATOR",
"nReturned": 10000,
"executionTimeMillisEstimate": 3,
"works": 10003,
"advanced": 10000,
"needTime": 2,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"parentName": "SORT"
}

COLLSCAN -> {
"stage": "COLLSCAN",
"nReturned": 10000,
"executionTimeMillisEstimate": 0,
"works": 10002,
"advanced": 10000,
"needTime": 1,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"direction": "forward",
"docsExamined": 10000,
"parentName": "SORT_KEY_GENERATOR"
}

sort() DESCENDING => Query Performance Summary (MongoDB Compass Explain) ->
{ "Name": -1 }
1. Documents Returned:10000
2. Index Keys Examined:0
3. Documents Examined:10000
4. Actual Query Execution Time (ms):32
5. Sorted in Memory:yes
6. SORT {
"stage": "SORT",
"nReturned": 10000,
"executionTimeMillisEstimate": 10,
"works": 20004,
"advanced": 10000,
"needTime": 10003,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"sortPattern": {
"Name": -1
},
"memUsage": 4144208,
"memLimit": 33554432
}
SORT_KEY_GENERATOR {
"stage": "SORT_KEY_GENERATOR",
"nReturned": 10000,
"executionTimeMillisEstimate": 2,
"works": 10003,
"advanced": 10000,
"needTime": 2,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"parentName": "SORT"
}
COLLSCAN {
"stage": "COLLSCAN",
"nReturned": 10000,
"executionTimeMillisEstimate": 1,
"works": 10002,
"advanced": 10000,
"needTime": 1,
"needYield": 0,
"saveState": 156,
"restoreState": 156,
"isEOF": 1,
"direction": "forward",
"docsExamined": 10000,
"parentName": "SORT_KEY_GENERATOR"
}
-----------------------------------------------------------------------------------
----------------------------------------

sort() => Query Performance Summary (MongoDB Shell Command)

results:
Ascending :
-> Query : [Link]().sort({"Name": 1}).pretty()
-> Query : [Link]().sort({"Name": 1}).pretty().explain("executionStats") ->
query show execution status
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "[Link]",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"Name" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10000,
"executionTimeMillis" : 31,
"totalKeysExamined" : 0,
"totalDocsExamined" : 10000,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 8,
"works" : 20004,
"advanced" : 10000,
"needTime" : 10003,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"sortPattern" : {
"Name" : 1
},
"memUsage" : 4144208,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 0,
"works" : 10003,
"advanced" : 10000,
"needTime" : 2,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 0,
"works" : 10002,
"advanced" : 10000,
"needTime" : 1,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 10000
}
}
}
},
"serverInfo" : {
"host" : "[Link]",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593491898, 1),
"signature" : {
"hash" : BinData(0,"Y9SmxwMebM5bo1eUGgmjQkUBUQg="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593491898, 1)
}

Descending :
-> Query : [Link]().sort({"Name": -1}).pretty()
-> Query 1 : [Link]().sort({"Name": -1}).pretty().explain("executionStats")
-> query show execution status
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "[Link]",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"Name" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10000,
"executionTimeMillis" : 31,
"totalKeysExamined" : 0,
"totalDocsExamined" : 10000,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 11,
"works" : 20004,
"advanced" : 10000,
"needTime" : 10003,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"sortPattern" : {
"Name" : -1
},
"memUsage" : 4144208,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 0,
"works" : 10003,
"advanced" : 10000,
"needTime" : 2,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 0,
"works" : 10002,
"advanced" : 10000,
"needTime" : 1,
"needYield" : 0,
"saveState" : 156,
"restoreState" : 156,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 10000
}
}
}
},
"serverInfo" : {
"host" : "[Link]",
"port" : 27017,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593492305, 1),
"signature" : {
"hash" : BinData(0,"vCYuHvsC/UmaqrK3P87vQhY0494="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593492305, 1)
}

===================================================================================
=======================================

Aggregate

1. $sum -> Query : [Link]([ { $group: {_id: "$Club", Total:


{$sum:1}}}]).pretty() -> query show data
[Link]([ { $group: {_id: "$Club", Total:{$sum:1}}}],
{explain:true}) -> query show

explainStats
Performance : {
"stages" : [
{
"$cursor" : {
"query" : {

},
"fields" : {
"Club" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" :
"5ef98b244c7d431c28d3ced3_fifa20.player",
"indexFilterSet" : false,
"parsedQuery" : {

},
"queryHash" : "8B3D4AB8",
"planCacheKey" : "8B3D4AB8",
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : "$Club",
"Total" : {
"$sum" : {
"$const" : 1
}
}
}
}
],
"serverInfo" : {
"host" : "[Link]",
"port" : 27000,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593497107, 1),
"signature" : {
"hash" : BinData(0,"mxFfyWkgPDYFNk4TR+V4OY/H5Nw="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593497107, 1)
}
-----------------------------------------------------------------------------------
----------------------------------------
2. $avg -> Query : [Link]([ { $group: {_id:"$Name", TotalAverage :
{ $avg: {$toInt: "$Physical"}}}},
{ "$sort": { "TotalAverage": -
1 }}]).pretty()

show explain stats : [Link]([ { $group: {_id:"$Name", TotalAverage :


{ $avg: {$toInt: "$Physical"}}}}],
{explain:true})

Performance : {
"stages" : [
{
"$cursor" : {
"query" : {

},
"fields" : {
"Name" : 1,
"Physical" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" :
"5ef98b244c7d431c28d3ced3_fifa20.player",
"indexFilterSet" : false,
"parsedQuery" : {

},
"queryHash" : "8B3D4AB8",
"planCacheKey" : "8B3D4AB8",
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : "$Name",
"TotalAverage" : {
"$avg" : {
"$convert" : {
"input" : "$Physical",
"to" : {
"$const" : "int"
}
}
}
}
}
}
],
"serverInfo" : {
"host" : "[Link]",
"port" : 27000,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593501989, 2),
"signature" : {
"hash" : BinData(0,"uYU9JXw5DZWrBdWzeAKOIDzhu3M="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593501989, 2)
}

-----------------------------------------------------------------------------------
----------------------------------------
3. $min -> -> Query : [Link]([ { $group: {_id:"$Name",
MaxPhysicalStats:{$min:"$Physical"}}},
{ "$sort":
{ "MaxPhysicalStats": 1 }}]).pretty() -> show data
show explain stats : [Link]([ { $group: {_id:"$Name",
MaxPhysicalStats:{$min:"$Physical"}}}],{explain:true})

Performance : {
"stages" : [
{
"$cursor" : {
"query" : {

},
"fields" : {
"Name" : 1,
"Physical" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" :
"5ef98b244c7d431c28d3ced3_fifa20.player",
"indexFilterSet" : false,
"parsedQuery" : {

},
"queryHash" : "8B3D4AB8",
"planCacheKey" : "8B3D4AB8",
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : "$Name",
"MaxPhysicalStats" : {
"$min" : "$Physical"
}
}
}
],
"serverInfo" : {
"host" : "[Link]",
"port" : 27000,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593499808, 1),
"signature" : {
"hash" : BinData(0,"S8zqvYNrjMuNzBn71sbNH9XQtaw="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593499808, 1)
}
-----------------------------------------------------------------------------------
----------------------------------------
4. $max (Desc 9-0) -> Query : [Link]([ { $group: {_id:"$Name",
MaxPhysicalStats:{$max:"$Physical"}}},
{ "$sort":
{ "MaxPhysicalStats": -1 }}]).pretty() -> show data
show explain stats : [Link]([ { $group: {_id:"$Name",
MaxPhysicalStats:{$max:"$Physical"}}}],{explain:true})

performance : {
"stages" : [
{
"$cursor" : {
"query" : {

},
"fields" : {
"Name" : 1,
"Physical" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" :
"5ef98b244c7d431c28d3ced3_fifa20.player",
"indexFilterSet" : false,
"parsedQuery" : {

},
"queryHash" : "8B3D4AB8",
"planCacheKey" : "8B3D4AB8",
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : "$Name",
"MaxPhysicalStats" : {
"$max" : "$Physical"
}
}
}
],
"serverInfo" : {
"host" : "[Link]",
"port" : 27000,
"version" : "4.2.8",
"gitVersion" : "43d25964249164d76d5e04dd6cf38f6111e21f5f"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1593499778, 1),
"signature" : {
"hash" : BinData(0,"HoYMj05D2OU+GG+sQz+By80IfCw="),
"keyId" : NumberLong("6843396449003110402")
}
},
"operationTime" : Timestamp(1593499778, 1)
}

Common questions

Powered by AI

Both ascending and descending sorts involve the same stages, including 'COLLSCAN', 'SORT_KEY_GENERATOR', and 'SORT', and exhibit similar performance in terms of documents examined and execution time, around 31-32 milliseconds. The slight variance in execution times (31ms for both sorts but varying memory usage estimate differences) might arise from differences in CPU cache utilization or disk I/O variations during execution .

Aggregation operations like $sum and $avg involve processing potentially large sets of documents ('COLLSCAN'). The absence of tailored indices necessitates examining each document, leading to increased execution time and resource use. Well-designed indices would streamline these operations, allowing MongoDB to quickly access relevant data subsets, reducing overhead and enhancing performance .

To improve the performance of aggregation pipelines relying on 'COLLSCAN', strategies include creating tailored indices to minimize scanning needs and transforming pipelines to reduce unnecessary complexity. Using hints to guide the query planner toward efficient plans, partitioning collections to shrink working sets, and re-thinking queries to align with available indices are further approaches. These can drastically reduce execution time and system resource usage .

MongoDB performs in-memory sorting when no index is used, as indicated by the 'Sorted in Memory: yes' attribute. This approach has performance implications, leading to longer execution times due to the need to sort all documents in memory. For example, sorting in ascending or descending order on the 'Name' field resulted in a total execution time of 31-32 milliseconds, with significant memory usage of 4144208 bytes .

Using distinct grouping keys in a $group operation impacts query complexity by increasing the computational burden, as each distinct key requires MongoDB to track and compute a separate group in memory. This complexity can affect performance, especially if no index exists for the grouping field, resulting in a collection scan (stage 'COLLSCAN'). In scenarios like grouping by 'Name' for statistics, this can lead to substantial memory usage for handling intermediate results .

Execution stats provide insights into areas like execution time, memory usage, and number of documents processed, informing performance optimization by identifying bottlenecks such as in-memory sort operations or high document examination rates when no index is present. These insights can guide decisions on introducing indexes to reduce memory usage and improve execution time for sorting queries, evident from the stats showing high memory usage and execution time for sorts without indexes .

The 'COLLSCAN' stage, which involves scanning all documents in a collection, is pivotal for queries lacking suitable indices, directly impacting query performance and application scalability. Frequent use of 'COLLSCAN' under high load can saturate CPU and I/O resources, reducing query throughput and increasing latency, thereby hindering scalability in large-scale applications. Developing effective indices can mitigate these effects, leading to more sustainable application growth .

The absence of index keys examined implies that MongoDB defaults to using collection scans ('COLLSCAN'), increasing the number of documents examined and execution time, thus leading to higher CPU and memory consumption to process the query. This lack of index utilization results in inefficient query handling, especially for operations that otherwise benefit significantly from index use, such as sorting and filtering .

The selection of a 'winning plan' directly influences query execution by determining the most efficient way to retrieve and process data, even without an index. In the absence of an index, MongoDB defaults to a collection scan ('COLLSCAN'), which impacts performance through increased document examination and execution time. This is evident where both find and sort operations default to 'COLLSCAN', leading to increased resource consumption and less efficient execution .

The MongoDB aggregation pipeline for calculating the average involves stages like '$cursor', which retrieves the documents, and '$group', where the actual average calculation occurs using $avg. This approach is effective since it allows calculation over potentially large datasets using the 'COLLSCAN' stage to examine documents. However, lacking an index on the field being averaged can affect performance by requiring a full collection scan .

You might also like