SingleStoreDB 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.

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.

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.

Each element of the center graphic portion of this page includes the following information:

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.

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