Screens: SQL editor

Execution plan

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.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.NameDescription
1Plan buttonGets 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).
2Explain tabA new tab added to the result area each time you get a plan. You can close or pin it like a result tab.
3Operation columnThe 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.
4Object columnThe table or index the operation reads. The format differs by DBMS (PostgreSQL adds the alias, as in orders o, and MySQL writes table: orders;).
5Rows columnThe number of rows the database estimated. It is not the number of rows actually read.
6Cost columnThe 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).
7Raw Desc columnThe 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.
8Execution timeThe time it took to get the plan. It is not the execution time of the query.

Viewing an execution plan

  1. 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.
  2. Click the Plan button in the toolbar. You can also right-click the editor and choose Explain Plan.
  3. 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.

DBMSCommand sentNotes
MySQL, MariaDBEXPLAIN FORMAT=JSON statementThe JSON plan is turned into a tree
PostgreSQLEXPLAIN (FORMAT JSON) statementAn estimated plan. The actual execution time and row counts are not shown
OracleEXPLAIN PLAN SET STATEMENT_ID=… FOR statement, then a query on PLAN_TABLEThe plan is written to PLAN_TABLE and then read back
SQL ServerSET SHOWPLAN_ALL ON → statement → SET SHOWPLAN_ALL OFFWhile SHOWPLAN_ALL is on, the statement is not run. OFF is sent to restore the session even when the statement fails
H2EXPLAIN statementThe plan comes back as one block of text, so it is shown as one row
DatabricksEXPLAIN statementSpark's plan text is split by indentation into a tree
SnowflakeEXPLAIN USING TABULAR statementCompiles only, without running
BigQueryNot availableIt 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
SQLiteEXPLAIN QUERY PLAN statementSteps 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.

ColorExample operationsMeaning
OrangeFull Scan, Seq Scan, Table Scan, Clustered Index Scan, TABLE ACCESS, SQLite's SCANReads the whole table. This can be slow on a large table
TealIndex Scan, Index Only Scan, Bitmap Heap Scan, Index Seek, Range Scan, Index Merge, SQLite's SEARCHReads only the needed range through an index
GreenUnique Access, Access, Const, Clustered Index SeekGoes straight to the row by key
PurpleHash Join, Hash Match, Merge Join, Nested Loop, Nested Loops, HashA join connecting two results
Blue-greySort, Aggregate, Full Index Scan, Materialized, UnionSorting, grouping, building intermediate results
GreyNames not listed aboveNot 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 ANALYZE yourself to get the actual execution time and row counts, the statement really runs. Added to an UPDATE or DELETE, 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.