1. Knowledge Base
  2. Documentation
  3. Visual Explain
Searching...

Visual Explain

Visual Explain displays the stages MongoDB took to execute a given query in a diagram format, with the option to show runtime statistics for each query stage.

MongoDB explain(), visualized

In previous versions of Studio 3T, it was already possible to see query plans and execution statistics through the Explain Tab, which displayed this information in tree view, like this:

The Explain tab gives an in-depth view into query performance.

Visual Explain is the evolution of the Explain Tab.

Instead of clicking through such a tree, the Visual Explain visualizes the same strategy undertaken by MongoDB in a simple-to-follow diagram.

Studio 3T's Visual Explain

Brief vs. Full Mode

By default, Visual Explain will show the explain diagram in brief mode without runtime statistics, like this:

Brief Mode doesn't display runtime statistics

It’s fast and always available for all queries, without delay.

Full mode, on the other hand, shows both the stages and runtime statistics:

Full mode displays stages and runtime statistics, but might cause a delay

It requires running the query, which might cause a delay depending on its size.

To trigger full mode, click the Run full explain button in the toolbar:

Click on the Run full explain button to show full mode

Because of limitations in MongoDB, full mode is not available for aggregation queries built through Aggregation Editor and SQL Query.

Hover over elements

You can hover over all elements in full mode to trigger additional help texts, for example:

Arrows

Hover over arrow elements to reveal more information

Difference between L-shaped, zigzag arrows, and straight arrows

L-shaped arrows indicate that the target collection or index is accessed sequentially.

Zigzag arrows indicate that the target collection or index is accessed in random order.

Straight arrows could indicate a number of events, e.g. processing of documents within the same stage, transfer of documents to the next stage, etc.

Runtime statistics

Hovering over runtime statistics displays information like the estimated number of bytes processed by the stage:

Hover over the statistic to see amount of data processed

Or the full document count (e.g. 1007 documents instead of 1.0K):

Reveal the full document count by hovering over the statistic

Or the time it took to execute an individual stage, or the whole query if you hover over the Result block:

Hover over the statistic to show how long it took to execute a stage, or the whole query

Query stages

Behind every MongoDB query is a query plan, and the Visual Explain displays how this plan was executed stage by stage.

View full description

To view a stage’s full description, double-click it or right-click and choose What’s This?.

Trigger the stage description by double-clicking on any stage

Runtime statistics

Visual Explain’s full mode displays actual runtime statistics, such as:

Stage output (number of documents produced)

Shows the number of documents produced by a stage

Shows the number of documents produced by a stage.

Execution time

Shows the milliseconds it took to execute a stage

Tracks the number of milliseconds it took to execute a stage.

Amount of data processed

Shows the estimated amount of data processed

Displays the estimated amount of data processed to carry out the operation.

Learn how to use the information from Visual Explain to optimize MongoDB query performance.

View the original query plan in JSON

Missing the old Explain tab? You still have the option to view the query plan as a JSON document. Click on the View original JSON document button:

Click on the icon to view the query plan as a JSON document

View a specific stage’s query plan in JSON

The original JSON document still contains a lot of information not visible in the diagram, but they tend to be very nested and difficult to work with.

To spare the back-and-forth scrolling for those who are interested in this extra information, the Visual Explain makes it much easier to view a specific stage’s JSON fragment:

  1. Right-click on a stage block.
  2. Click on view original JSON fragment.View a specific stage's JSON fragment
  3. This will display only that specific stage’s JSON fragment, instead of the full query plan.Displays the stage's specific JSON fragment

List of supported query stages

Aggregation operator
Represents a stage in an aggregation pipeline. All such stages read their input documents, apply operator-specific processing, and pass the result to the next stage.
Collection scan
Represents a collection scan query stage. Collection scan reads the documents from the collection one by one and passes them to the next stage. The documents are produced in no particular order. Collection scan is performed whenever the query engine decides that the easiest (or the only possible) way to get all the required documents is to read every document in the entire collection. Collection scan is a very I/O expensive operation, especially if the collection is large.
Fetch document
Represents a fetch document query stage. This stage reads a stream of ids produced by the previous stage. Each id then fetches a document with that id from the collection, then passes the document to the next stage. Although each individual document lookup is fast, this stage generates a lot of random I/O operations, which can become very I/O expensive, especially when multiplied by the number of documents.
Filter
Represents a document filter. Not a stage by itself, filtering is performed as a part of various stages. All the input documents are checked against some condition, and only those that match are passed to the next stage.
Index scan
Represents an index scan query stage. Index scan quickly locates and reads index records that match the query filter. Each matching record is passed to the next stage. The records are typically produced in order of the index (e.g. alphabetical). Since indexes contain only a handful of fields, this stage is typically followed by a fetch, which reads an entire document from the collection, using ids extracted from the index record. Index scan is only used when the query engine decides that the current query will benefit from going through the index, rather than running a full collection scan.
Union + deduplicate
Represents a union + deduplicate query stage. The purpose of this stage is to combine the results of its input stages, discarding identical documents that may be encountered more that once.Content
Limit
Represents a limit query stage. This stage reads the first N documents, passes them to the next stage, then terminates the process without examining the rest.
Projection
Represents a projection query stage. Projection transforms the input documents by removing some fields and possibly adding others. The transformed documents - which typically have just a small number of fields left - are passed to the next stage.
Shard merge sort
Represents a shard merge sort query stage. This stage combines the results obtained from several shards into one. All the shards
Sharding filter
Shard merge
Represents a shard merge query stage. This stage combines the results obtained from several shards into one.
Single shard
Skip
Represents a skip query stage. This stage reads the first N documents and discards them without producing anything, then passes the rest of the documents to the next stage.
Sort buffer
Represents a sort buffer. Not a stage by itself, sort buffer limits the number of documents to be produced by the sort stage. This allows to reduce the amount of memory required for sorting.
Sort key generator
Represents a sort key generator query stage. This is a preliminary stage to the sort stage that follows. From the input documents it extracts the fields required for sorting and passes the result to the sort stage.
Sort
Subplan
Represents a subplan stage. This stage plans each clause of the $or query individually, and then
creates an overall query plan based on the winning plan from each clause.
Text match
Represents a text match query stage. This stage returns only the documents that match the full-text search
expression.
Text
Represents a text query stage. The purpose of this stage is to collect statistics on the full-text
search process itself.
Unknown
This block indicates that a query stage of this type is not known
to the application, and is displayed as "just some stage".
Not supported
This block indicates that the format of the query plan is not supported and it is not parsed at all.

Release the full power of MongoDB with these time-saving features:

  • Import and Export Wizard – Easily import to and from MongoDB in JSON, CSV, BSON/mongodump, SQL, and another collection
  • IntelliShell – Studio 3T’s built-in mongo shell with robust autocompletion
  • Aggregation Editor – Build MongoDB aggregation queries stage by stage

Updated on January 7, 2019
Tagged:

Was this article helpful?

Yes No

About The Author

Kathryn Vargas

Kathryn Vargas

Kathryn simply wants to let the world know that Studio 3T is the best MongoDB IDE out there. When she's not gushing over Studio 3T's useful features, she spends her free time exploring Berlin's food scene, playing the drums, learning languages (current mission: German), and writing.

Related Articles