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 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 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 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:

Click the Upload File button and select the desired JSON output file to upload.
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:

Click the vertical dots menu in the upper right-hand corner of Visual Explain.
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 to learn how.

Example

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

Create the Dataset

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());

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

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;

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

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 training course.

On this page

Overview

Visual Explain Metrics

Export a Query Plan

Import a Query Plan

Example

Create the Dataset

Obtain an Order Date

Visualize and Optimize the Query

Summary

Was this article helpful?

On this page

Was this article helpful?

SingleStore Visual Explain

On this page

Overview

Visual Explain Metrics

Export a Query Plan

Import a Query Plan

Example

Create the Dataset

Obtain an Order Date

Visualize and Optimize the Query

Paste JSON Output

Upload/Import a JSON Output File

Summary

Related Topics

Was this article helpful?

On this page

Was this article helpful?