# SingleStore Visual Explain

## Overview

SingleStore Visual Explain (or simply “Visual Explain”) is a feature that allows query plans to be visualized graphically. This is useful for tuning database queries to reduce execution times and/or resource usage.

Visual Explain is offered as a free [stand-alone service](https://explain.labs.singlestore.com) for self-managed customers.

To visualize a query plan, you may:

* **Use JSON output**: The `SHOW PROFILE JSON` and `EXPLAIN JSON` SQL statements can display JSON output that can be pasted or uploaded into Visual Explain to show the query plan.
* **Use a URL**: The `SHOW PROFILE UI` and `EXPLAIN UI` SQL statements can display a URL that will open Visual Explain and show the query plan.

Use the [provided example](https://docs.singlestore.com/#section-idm4635610284734433781854947158.md) to learn how to visualize a query plan with Visual Explain.

## Visual Explain Metrics

From the upper left-hand corner of Visual Explain, you may select the **Actual** or **Estimated** options for a given query plan.

At the center of Visual Explain is a tree composed of clickable elements, where each element is a single query plan operation. The tree is read from the bottom to the top, so the element at the top of the tree returns the result to the SQL client. Each element includes the following information:

* The query plan operation performed
* The percent of total query execution time for each query plan operation
* The number of rows processed by each query plan operation

  This number could be greater than the number of rows in a given table if the query includes a statement, such as a nested loop join, that requires the engine to read the table multiple times.
* The associated table on which each query plan operation is performed (where applicable)

The following metrics are also shown on the right of Visual Explain.

* If an element in the graphic has not been clicked, Visual Explain displays a summary of all query plan operations.

  * **Cluster Info**

    * Server version
    * The total number of aggregators and leaf nodes
  * **Summary**

    * Total execution time
    * Total number of operations
    * Total memory usage
  * **Details**

    * Each query plan operation
    * Time spent on each query plan operation
* If an element in the graphic has been clicked, Visual Explain displays a summary of its query operations.

## Export a Query Plan

To export a query plan for use *with* Visual Explain, either:

* Run a `PROFILE` SQL statement followed by `SHOW PROFILE JSON` or run an  `EXPLAIN JSON` SQL statement. Both of these options display JSON output that will show the query plan when pasted or uploaded into Visual Explain. Include the `INTO OUTFILE` clause in either of these SQL statements to write the JSON output to a local file.
* Run a `PROFILE` SQL statement followed by `SHOW PROFILE UI` or run an  `EXPLAIN UI` SQL statement. Both of these options display a URL that, when opened in a browser, will show the query plan in Visual Explain.

Use the [provided example](https://docs.singlestore.com/#section-idm4635610284734433781854947158.md) to learn how to visualize a query plan with Visual Explain.

To export a query plan *from* Visual Explain, click the vertical dots menu in the upper right-hand corner of Visual Explain and select **Export**.

## Import a Query Plan

To import an initial query plan from a JSON output file that was created from either the `PROFILE` or `EXPLAIN` SQL statements, you may either:

1. Click the **Upload File** button and select the desired JSON output file to upload.

2. Click the **Paste JSON** button and paste the JSON output of a query plan into the provided field.

To replace a query plan with another:

1. Click the vertical dots menu in the upper right-hand corner of Visual Explain.

2. Select either:

   1. The **Import Explain/Profile** option and select the desired JSON output file to upload.

   2. The **Paste JSON Explain/Profile** option and paste the JSON output into the provided field.

Use the [provided example](https://docs.singlestore.com/#section-idm4635610284734433781854947158.md) to learn how.

## Example

The following example demonstrates how to use Visual Explain to profile a query and improve its performance.

## Create the Dataset

1. In a SQL client, create the following rowstore table in the database of your choice (such as a test database) and fill it with mock order data.
   ```sql
   CREATE ROWSTORE TABLE if not exists expedited_orders(
   order_id int,
   order_date datetime,
   key(order_id),
   shard(order_id));
   INSERT INTO expedited_orders VALUES(1, now());
   ```

2. Re-run the following SQL statement until 524,288 rows of data have been inserted into the table.
   ```sql
   INSERT INTO expedited_orders
   SELECT order_id+(SELECT max(order_id) FROM expedited_orders), now()
   FROM expedited_orders;
   ```

## Obtain an Order Date

1. In a SQL client, run the following query to obtain a sample `order_date`.
   ```sql
   SELECT order_date, count(*)
   FROM expedited_orders
   GROUP BY 1
   HAVING count(*) = 1;
   ```

2. Run the following query using an `order_date` from the previous query result.

   **Note**: The remainder of these steps will use `2023-07-11 15:56:08` for the `order_date`. Use an `order_date` from your own database to ensure that this example will work.
   ```sql
   SELECT *
   FROM expedited_orders
   WHERE order_date = "2023-07-11 15:56:08";
   ```

## Visualize and Optimize the Query

* *Paste JSON Output*

  1. In a SQL client, run the following `PROFILE`-related queries to obtain the JSON output.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```sql
     SHOW PROFILE JSON;
     ```

  2. In [Visual Explain](https://explain.labs.singlestore.com), click the **Paste JSON** button, paste the JSON output into the provided field, and click the **Show Explain** button.

     Visualizing this query reveals that SingleStore performed a full table scan (indicated by TableScan) in this query. As a consequence, each row was scanned to determine which order has an `order_date` of `2023-07-11 15:56:08`.

     Note that the execution time for this query is 410 milliseconds, which includes the query compilation time.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8219355af6a74c62/6a2c436208fb7817343194e8/visual_explain_example_1-oW7Du0.png)To determine if adding an index will improve performance:1. Add an index on the `order_date` column.
     ```sql
     CREATE INDEX date_index ON expedited_orders (order_date);
     ```

  2. Re-run the `SELECT * FROM expedited_orders` query.
     ```sql
     SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```

  3. Re-run the `PROFILE`-related queries to obtain the JSON output.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```sql
     SHOW PROFILE JSON;
     ```

  4. Copy the JSON output to the clipboard.

  5. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the **Paste JSON Explain/Profile** option, paste the JSON output into the provided field, and click the **Show Explain** button.

     Visualizing this query reveals that SingleStore used an index range scan (indicated by IndexRangeScan) instead of a full table scan, thereby limiting the scan to only those rows that should contain the specified `order_date`.

     Note that the execution time for this query is down to 221 milliseconds, which also includes the query compilation time.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8111329d0baca10f/6a2c4230a36d0a8db2b8211b/visual_explain_example_2-3SMoAT.png)To see how quickly this query will run without the query compilation time:1. Re-run the `SELECT * FROM expedited_orders` query.
     ```sql
     SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```

  2. Re-run the `PROFILE`-related queries to obtain the JSON output.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```sql
     SHOW PROFILE JSON;
     ```

  3. Copy the JSON output to the clipboard.

  4. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the **Paste JSON Explain/Profile** option, paste the JSON output into the provided field, and click the **Show Explain** button.

     Visualizing this query reveals that the execution time has been reduced to 26 milliseconds.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltff2afdb7308cc173/6a2c4231f7564608a0060d73/visual_explain_example_3-3Qlm5c.png)

* *Upload/Import a JSON Output File*

  1. Display the location where you can write files on the host. This is typically the `/var/lib/memsql/db_files` directory.
     ```sql
     SELECT @@secure_files_priv;
     ```
     Refer to [Setting SECURE\_FILE\_PRIV](https://docs.singlestore.com/db/v9.1/security/setting-secure-file-priv.md) to change this location.

  2. In a SQL client, run the following `PROFILE`-related queries to obtain a JSON output file.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```sql
     SHOW PROFILE JSON INTO OUTFILE “/var/lib/memsql/db_files/query_plan_1.json”;
     ```

  3. In [Visual Explain](https://explain.labs.singlestore.com), click the **Upload File** button, and select this JSON output file to upload.

     Visualizing this query reveals that SingleStore performed a full table scan (indicated by TableScan) in this query. As a consequence, each row was scanned to determine which order has an `order_date` of `2023-07-11 15:56:08`.

     Note that the execution time for this query is 410 milliseconds, which includes the query compilation time.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8219355af6a74c62/6a2c436208fb7817343194e8/visual_explain_example_1-oW7Du0.png)To determine if adding an index will improve performance:1. Add an index on the `order_date` column.
     ```sql
     CREATE INDEX date_index ON expedited_orders (order_date);
     ```

  2. Re-run the `SELECT * FROM expedited_orders` query.
     ```
     SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```

  3. Re-run the `PROFILE`-related queries to obtain a JSON output file.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```sql
     SHOW PROFILE JSON INTO OUTFILE “/var/lib/memsql/db_files/query_plan_2.json”;
     ```

  4. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the **Import Explain/Profile** button, and select this JSON output file to upload.

     Visualizing this query reveals that SingleStore used an index range scan (indicated by IndexRangeScan) instead of a full table scan, thereby limiting the scan to only those rows that should contain the specified `order_date`.

     Note that the execution time for this query is down to 221 milliseconds, which also includes the query compilation time.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8111329d0baca10f/6a2c4230a36d0a8db2b8211b/visual_explain_example_2-3SMoAT.png)To see how quickly this query will run without the query compilation time:1. Re-run the `SELECT * FROM expedited_orders` query.
     ```sql
     SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```

  2. Re-run the `PROFILE`-related queries to obtain a JSON output file.
     ```sql
     PROFILE SELECT *
     FROM expedited_orders
     WHERE order_date = "2023-07-11 15:56:08";
     ```
     ```
     SHOW PROFILE JSON INTO OUTFILE “/var/lib/memsql/db_files/query_plan_3.json”;
     ```

  3. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the **Import Explain/Profile** button, and select this JSON output file to upload.

     Visualizing this query reveals that the execution time has been reduced to 26 milliseconds.

     ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltff2afdb7308cc173/6a2c4231f7564608a0060d73/visual_explain_example_3-3Qlm5c.png)

## Summary

By employing Visual Explain to help optimize this example, the execution time for the `SELECT * FROM expedited_orders query` has been reduced:

* From an initial 410 milliseconds for a full table scan
* To 221 milliseconds after introducing an index, which includes the query compilation time
* To only 26 milliseconds after the query had been compiled

For additional examples, refer to the final lab in the SingleStore [Query Tuning](https://training.singlestore.com/learn/course/internal/view/elearning/634/query-tuning) training course.

## Related Topics

* [PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md) and [EXPLAIN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/explain.md)
* [Query Tuning](https://docs.singlestore.com/db/v9.1/query-data/query-tuning.md)
* [Query Plan Operations](https://docs.singlestore.com/db/v9.1/query-data/query-plan-operations.md)

***

Modified at: May 22, 2026

Source: [/db/v9.1/query-data/query-tuning/singlestore-visual-explain/](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/singlestore-visual-explain/)

(An index of the documentation is available at /llms.txt)