SingleStoreDB Visual Explain
The SingleStoreDB Visual Explain page is a feature that allows customers to see a query plan visualized via a graphical interface. This is useful for tuning database queries to reduce run-time or resource usage. See more about this in Query Tuning.
Using Visual Explain
To use Visual Explain, highlight the desired query in the SQL Editor and click the Visual Explain drop-down to the left of the Run button. Choose between EXPLAIN and PROFILE from the drop-down. Alternatively, you can navigate to the Visual Explain tab on the left sidebar and upload a JSON file that contains a query plan. (Learn more)
At the top-left of the Visual Explain page, you may choose the Actual, Estimated, or Difference options for a given query plan.
At the center of this interface is a tree composed of clickable elements, where each element displayed is a single query plan operation. These are ordered so that the operator at the top of the tree is the one that returns the result to the client.
Exporting Query Plans
You can export a query plan projected on the Visual Explain interface by the EXPLAIN or PROFILE command. To perform this action, click the Kebab Menu icon on the top-right corner and choose Export. This action exports a given query plan into a JSON file.
Alternatively, you can export query plans in the command line by using the INTO OUTFILE
clause with PROFILE and EXPLAIN commands. For more information, visit the command reference topics.
Importing Query Plans
On the Visual Explain page, you can import JSON files that contain query plans, output by the EXPLAIN
and PROFILE
commands. To perform this action, click the Upload File button displayed at the center of the page and choose the JSON file with the query plan from your device. Alternatively, you can use the Paste JSON button to directly paste the query plan in JSON format instead of uploading a file.
If you want to import a query plan to replace the one being visualized, click the Kebab Menu icon on the top-right corner, choose Import Explain/Profile, and select the required JSON file from your device. Alternatively, you can use the Paste JSON Explain/Profile option in the Kebab Menu to directly paste the query plan in JSON format.
Metrics Displayed by Visual Explain
Each element of the center graphic portion of this page includes the following information:
The query plan operation being 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 command, such as a nested loop join, that requires the engine to read the table multiple times.
The associated table each query plan operation is performed on (where applicable)
The following metrics are also shown as text on the right of the Visual Explain page. If no element of the graphic has been clicked, a summary of all query operations is shown. If a specific query operation has been selected, this section will show more detailed information.
Summary
Total execution time
Total number of operations
Total memory usage
Details
Each query plan operation
Time spent on each query plan operation
Example
This example shows a simple use of Visual Explain to make a query run faster. First, an example table is created and filled with mock order data.
CREATE 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()); /* Run this command until 524288 rows of data have been inserted into the table. */ INSERT INTO expedited_orders SELECT order_id+(SELECT max(order_id) FROM expedited_orders), now() FROM expedited_orders;
Running the below queries using Visual Profile reveals that SingleStoreDB performs a full TableScan
in this query plan. This means that each individual row is scanned in order to count those where the order_date
is 2020-09-21 17:51:47
.
/* Run this query to get an example order_date. */ SELECT order_date, count(*) FROM expedited_orders GROUP BY 1 HAVING count(*) = 1 /* Run this query using the order_date copied from from the previous query result, in place of the date 2020-09-21 17:51:47. */ SELECT * FROM expedited_orders WHERE order_date = "2020-09-21 17:51:47";

Adding an index on the order_date
column, then running and profiling the same query again reveals an increase in performance. The runtime for the query decreases from 95 milliseconds to 1 millisecond. This is because the new index on order_date
allows SingleStoreDB to execute this query with IndexRangeScan
instead of TableScan
, meaning that the index is utilized in order to only scan rows which might contain the specified order_date
.
CREATE INDEX date_index ON expedited_orders (order_date); SELECT * FROM expedited_orders WHERE order_date = "2020-09-21 17:51:47"; DROP TABLE expedited_orders;

For more examples, see the final lab in the SingleStoreDB Training course, Query Tuning.
Related Topics