DataGrip 2020.2 Help

Create diagrams

Database diagrams graphically show the structure of the database and relations between database objects. You can generate a diagram for a data source, a schema, or a table. To create relations between database objects, consider using primary and foreign keys.

Also, you can build execution plans. Execution plan is a set of steps that were used to access data in a database. DataGrip supports two types of execution plans:

  • Explain Plan: the result is shown in a mixed tree and table format on a dedicated Plan tab. You can click the Show Visualization icon (the Show Visualization icon) to create a diagram that visualizes the query execution.

  • Explain Plan (Raw): the result is shown in a table format.

Generate a diagram for a database object

  • In the Database tool window (View | Tool Windows | Database), right-click a database object and select Diagrams | Show Visualization.

Generate a diagram for a database object

Build a query plan

  1. Right-click an SQL statement, and select Explain Plan.

  2. In the Output pane, click Plan.

  3. By default, you see the tree representation of the query. To see the query plan, click Show Visualization the Show Visualization button, or press Ctrl+Alt+Shift+U.

Viewing SQL Query map

Show execution plans

  • To create an execution plan, right-click a query in the editor and select Explain Plan. If you want to create a diagram for the query, click the Show Visualization icon (the Show Visualization icon).

Showing an execution plan as a diagram

Enable column comments

  1. Generate a diagram. For more information about generating a diagram, see Generate a diagram for a database object.

  2. Click the Comments button (the Comments button).

    Enable column comments

Creating EXPLAIN query plan

The EXPLAIN command shows the execution plan of a statement. It means you can see details on the approach that the planner took to execute the statement. For example, how the tables are scanned, what join algorithms are used to bring together the required rows, statement execution costs, and other information.

Execution cost is the planner's guess at how long it takes to run the statement. The measurement is made in relative cost units. The execution cost has two options: start-up and total. The start-up cost shows how long it takes before the first row can be processed, while the total cost shows how long it takes to process all the rows.

If you use the ANALYZE option with EXPLAIN, the statement is actually executed, not only planned. In this case, you can see the run time statistics in milliseconds.

Generate a flame graph for EXPLAIN

  1. Right-click an SQL statement, and select Explain Plan.

  2. In the Output pane, click Plan.

  3. Click the Flame Graph icon (the Flame Graph icon) and select between the following options:

    • Total Cost: how long it takes to return all the rows

    • Startup Cost: how long it takes before the first row can be processed.

    Generate a flame graph for EXPLAIN

Generate a flame graph for EXPLAIN ANALYSE

  1. Right-click an SQL statement, and select Explain Analyse Plan.

  2. In the Output pane, click Plan.

  3. Click the Flame Graph icon (the Flame Graph icon) and select between the following options:

    • Total Cost: how long it takes to return all the rows (in relative cost units).

    • Actual Total Time: how long it takes to return all the rows (in milliseconds).

    • Startup Cost: how long it takes before the first row can be processed (in relative cost units).

    • Actual Startup Time: how long it takes before the first row can be processed (in milliseconds).

    Generate a flame graph for EXPLAIN ANALYSE

Diagram configuration settings

To configure the default visibility settings and layout for diagrams, open settings by pressing Ctrl+Alt+S and navigate to Tools | Diagrams.

the Diagrams menu

Content pane

Select the checkboxes next to the elements to be shown on diagrams.

ItemDescription
Show Difference
DetailsIf this checkbox is selected, all the specified details of the elements will be shown in the UML class diagram for a revision. If this checkbox is not selected, only node elements will be included in the diagram.
Database Schema Diagram
Key columns

For the primary key columns to be shown when a diagram opens, select this checkbox.

When viewing a diagram in the editor, use the Key Columns button on the toolbar to show or hide the corresponding columns.

Columns

For the columns other than the primary key columns to be shown when a diagram opens, select this checkbox.

When viewing a diagram in the editor, use the Columns button on the toolbar to show or hide the corresponding columns.

Graphical Explain Plan
Attributes

Toggles the display of query attributes on a query map. These attributes can be a number of rows, index names, or any other additional information about the query.

Enabled and disabled attributes on a query plan

Controls

ItemDescription
Default layoutSelect the desired layout from the Default layout list. Node elements in newly created diagrams will be arranged according to the selected layout.
Default scopeSelect scope from the Default scope list. Specifying a scope helps you avoid showing in diagram the unnecessary hierarchies. You can define scopes for your project in the Scopes page of the Settings Preferences dialog.
Fit content after layoutIf this checkbox is selected, then after applying a layout selected on the diagram context menu, all diagram elements will be resized to fit into the current diagram area. In diagram, use the the Fit Content icon toolbar button.
Do relayout when new elements were addedIf this checkbox is selected, diagram layout will be performed automatically after adding new elements.
Enable colorsIf this checkbox is selected, relationship links will be shown colored.
Last modified: 19 August 2020