An execution plan is the plan the database makes in advance for processing a SQL statement: in which order, and by which method (a full scan, an index, a join method). The Plan button of the SQL editor gets the plan of the statement at the cursor from the database and shows it as a tree table in the result area. The statement itself is not run, so you can look at the plan of a slow query or of a statement that changes data.
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Plan button | Gets the execution plan of the statement at the cursor (or of the selected text, if there is a selection). The shortcut is Ctrl+Shift+E (Cmd+Shift+E on macOS). |
| 2 | Explain tab | A new tab added to the result area each time you get a plan. You can close or pin it like a result tab. |
| 3 | Operation column | The name of the operation. Child operations are shown as an indented tree, which you collapse with the arrow in front. The color of the icon before the name is explained in 'Reading the icon colors' below. |
| 4 | Object column | The table or index the operation reads. The format differs by DBMS (PostgreSQL adds the alias, as in orders o, and MySQL writes table: orders;). |
| 5 | Rows column | The number of rows the database estimated. It is not the number of rows actually read. |
| 6 | Cost column | The cost the database estimated (Total Cost for PostgreSQL, TotalSubtreeCost for SQL Server). It is a relative value without a unit, so compare it only between plans of the same DBMS. It is empty for databases that do not give a cost (H2, Databricks, Snowflake, SQLite). |
| 7 | Raw Desc column | The remaining items not shown in the columns above (filter conditions, join conditions, width and so on), joined as name = value. Long text is cut off; hover over it to see all of it. |
| 8 | Execution time | The time it took to get the plan. It is not the execution time of the query. |
Viewing an execution plan
- In a SQL tab, put the cursor in the statement whose plan you want. To look at only part of several statements, select that part.
- Click the Plan button in the toolbar. You can also right-click the editor and choose Explain Plan.
- An Explain tab opens in the result area with the plan expanded. The outermost operation finishes last; the further in an operation is, the earlier it runs.
- If the result grid has edits that are not applied, you are first asked whether to discard them.
- The plan is fetched in the database session of this SQL tab. So the tab's run target (connection, database, schema) and its uncommitted changes are reflected in the plan (SQL execution and transactions).
- If the plan cannot be fetched, the Explain tab shows the database's error message and the SQL that was sent. Getting AI help on the error is explained in AI in the editor and ERD.
- Plans fetched this way are not recorded in the SQL execution history.
How each DBMS produces the plan
NeoSQL sends the statement with the DBMS's plan command in front of it. For no DBMS does it use a method such as ANALYZE that actually runs the statement.
| DBMS | Command sent | Notes |
|---|---|---|
| MySQL, MariaDB | EXPLAIN FORMAT=JSON statement | The JSON plan is turned into a tree |
| PostgreSQL | EXPLAIN (FORMAT JSON) statement | An estimated plan. The actual execution time and row counts are not shown |
| Oracle | EXPLAIN PLAN SET STATEMENT_ID=… FOR statement, then a query on PLAN_TABLE | The plan is written to PLAN_TABLE and then read back |
| SQL Server | SET SHOWPLAN_ALL ON → statement → SET SHOWPLAN_ALL OFF | While SHOWPLAN_ALL is on, the statement is not run. OFF is sent to restore the session even when the statement fails |
| H2 | EXPLAIN statement | The plan comes back as one block of text, so it is shown as one row |
| Databricks | EXPLAIN statement | Spark's plan text is split by indentation into a tree |
| Snowflake | EXPLAIN USING TABULAR statement | Compiles only, without running |
| BigQuery | Not available | It does not give a plan through SQL, so the Plan button is disabled and shows the This DBMS does not provide an execution plan (EXPLAIN) through SQL. notice |
| SQLite | EXPLAIN QUERY PLAN statement | Steps such as SCAN and SEARCH are shown as a tree. Row counts and costs are not given |
Reading the icon colors
The icons in the Operation column get their color from the operation name. If you see a lot of orange, first check whether some part of the query does not use an index.
| Color | Example operations | Meaning |
|---|---|---|
| Orange | Full Scan, Seq Scan, Table Scan, Clustered Index Scan, TABLE ACCESS, SQLite's SCAN | Reads the whole table. This can be slow on a large table |
| Teal | Index Scan, Index Only Scan, Bitmap Heap Scan, Index Seek, Range Scan, Index Merge, SQLite's SEARCH | Reads only the needed range through an index |
| Green | Unique Access, Access, Const, Clustered Index Seek | Goes straight to the row by key |
| Purple | Hash Join, Hash Match, Merge Join, Nested Loop, Nested Loops, Hash | A join connecting two results |
| Blue-grey | Sort, Aggregate, Full Index Scan, Materialized, Union | Sorting, grouping, building intermediate results |
| Grey | Names not listed above | Not classified |
- The color is chosen only from the letters of the operation name. Names differ by DBMS, so an operation whose name is not in the table is grey even when it is of the same kind.
Things to keep in mind
- The Cost column is the cost up to that operation (with its child operations), not of the whole plan. The value of the outermost operation is the estimated cost of the whole statement.
- If you run
EXPLAIN ANALYZEyourself to get the actual execution time and row counts, the statement really runs. Added to anUPDATEorDELETE, it changes data. The read-only protection mode of the connection profile does not treat such a statement as a query and blocks it (Safeguards for production databases). - The Rows column is an estimate based on statistics. If the statistics are old, it can be far from the real number.
- The Plan button can only be clicked when the connection of the run target has connection details.
Related screens
- SQL editor: the toolbar with the Plan button, and the shortcuts.
- Result grid: the result tabs that the Explain tab sits with.
- AI in the editor and ERD: hand query tuning to the AI with AI: Tune query in the editor's right-click menu.
- SQL execution and transactions: the database session and transaction the plan is fetched in.