Sign inTry Free

SingleStore Visual Explain

On this page

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:

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

    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.

    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.

    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.

    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.

Related Topics

Last modified: August 4, 2023

Was this article helpful?

On this page

Was this article helpful?