Help Center/ GaussDB(DWS)/ Tool Guide/ Data Studio/ SQL Terminal Management/ Viewing the Execution Plan and Costs
Updated on 2024-04-28 GMT+08:00

Viewing the Execution Plan and Costs

The execution plan shows how the table referenced by the SQL statement will be scanned (sequential scan or index scan).

The SQL statement execution cost indicates the duration for executing a statement (measured in cost units that are arbitrary, but conventionally mean disk page fetches).

Follow the steps below to view the plan and cost for a required SQL query:

  1. Enter the query or use an existing query in the SQL Terminal and click on the SQL Terminal toolbar to view explain plan.

    To view explain plan with analyze, click the drop-down from , select Include Analyze, and click .

    The Execution Plan opens in tree view format as a new tab at the bottom by default. The display mode has a tree shape and text style.

    The data shown in tree explain plan and visual explain may vary, since the execution parameters considered by both are not the same.

    Following are the parameters selected for explain plan with/without analyze and the columns displayed:

    Table 1 Explain plan options

    Explain Plan Type

    Parameter

    Column

    Include Analyze deselected (default setting)

    Verbose, Costs

    Node type, startup cost, total cost, rows, width, additional Info

    Include Analyze selected

    Analyze, Verbose, Costs, Buffers, Timing

    Node type, startup cost, total cost, rows, width, Actual startup time, Actual total time, Actual Rows, Actual loops, Additional Info

    The Additional Info column contains predicate information (filter predicate and hash condition), distribution key, output information, and node type.

    In Tree Format, nodes are categorized into 16 types. Each node is indicated with an icon of the corresponding type. The following table lists the node types and icons.

    Table 2 Node types and icons

    Node Category

    Icon

    Aggregate

    Group Aggregate

    Function

    Hash

    Hash Join

    Nested Loop

    Nested Loop Join

    Modify Table

    Partition Iterator

    Row Adapter

    Seq Scan on

    Set Operator

    Sort

    Stream

    Union

    Unknown

    You can hover over highlighted cells to identify the heaviest, costliest, and slowest nodes. Cells can only be highlighted in Tree Format.

    If multiple queries are selected, the explain plan with/without Include Analyze selected is displayed only for the last query.

    Each execution plan generated from executing a query is opened on a new tab page.

    If the connection is lost during execution but the database remains connected in Object Browser, the Connection Error dialog box is displayed with the following options:

    • OK: Connects to the database again to obtain the execution plan and cost.
    • Cancel: Disconnects the database from Object Browser.

    Toolbar menu in the Execution Plan window:

    Toolbar Name

    Icon

    Description

    Tree Format

    This icon is used view explain plan in tree format.

    Text Format

    This icon is used to display explain plan in text format.

    Copy

    This icon is used to copy selected data from the Result tab to clipboard. The shortcut key is Ctrl+C.

    Commit

    This icon is used to save the explain plan in text format.

    For information about refresh, SQL preview, and search bar, see Execute SQL Queries.

    After you click Refresh, the EXPLAIN ANALYZE query is executed again, and the execution plan is updated.

    The result is displayed on the Messages tab.