Query Plan

The Query plan button of the SQL Editor shows why a query is slow. It runs the statement and shows the plan of the database as a tree, with the time, the rows and the usual causes of slowness of every step. It works on PostgreSQL, SQL Server, MySQL and MariaDB. Each database gives the plan in its own way, and myDBR turns it into the same view.

Using it

1. Write a statement in the SQL Editor. Select the part you want to explain, or leave the selection empty to explain the first statement.
2. Click Query plan next to Execute.
3. The plan opens below the editor.

The statement is really run, so the plan has the real times and row counts, not only estimates. It runs inside a transaction that is rolled back, so the changes of INSERT, UPDATE and DELETE are not kept. Effects outside the transaction, such as sequences, notifications and calls to other servers, are not undone. A statement is stopped after 60 seconds (on PostgreSQL after the report timeout when one is set). On MySQL the limit applies to SELECT statements only.

Only the first statement is explained. A second statement after a semicolon is refused on PostgreSQL.

Reading the plan

Query plan  Planning: 0.19 ms  Execution: 11.5 ms  Buffers: 707 hit, 0 read
▼ Sort            0.03 ms (0%)   rows 100 (estimated 200)
  ▼ Aggregate     1.00 ms (9%)   rows 100 (estimated 200)
    ▼ Hash Join   3.52 ms (31%)  rows 17,652 (estimated 276,452)
        Estimated 276,452 rows, got 17,652
        Seq Scan ex_a   4.37 ms (38%)  rows 52,525 (estimated 69,113)   ◀ the slowest step
        Sequential scan over 52,525 rows
        ▼ Hash          1.54 ms (13%)
            Seq Scan ex_b   0.97 ms (8%)  rows 20,000

• Each step of the plan (a node) has its own time, shown as a bar and as a percent of the whole execution. The time of the steps below it is not counted.
• The slowest step is highlighted.
• Rows are the real rows, with the estimate of the database in brackets. A step that runs in a loop shows rows 1 x 12,404 (the rows of one loop times the loops).
• Buffers or Logical reads show how much data the step read, and on PostgreSQL, MariaDB and MySQL where the database tells it, how much of it came from disk.
• Click a triangle to close a part of the tree.
• Plan as JSON shows the raw plan of the database (JSON on PostgreSQL and MariaDB, XML on SQL Server, the text tree on MySQL). Copy copies it, for example to a plan visualizer.

Flags

A line in color under a step points at a likely cause of slowness. Which flags are shown depends on what the database tells:

Flag	Meaning	Databases
Scan over N rows	The whole table was read. An index may help.	All
Estimated N rows, got M	The estimate is more than ten times off. Update the statistics of the table.	All
N rows removed by the filter	The step reads many rows and the filter throws most away. An index on the filter column may help.	PostgreSQL, MySQL, MariaDB
Sort spilled to disk, Hash used N batches	The sort or the hash did not fit in memory (work_mem).	PostgreSQL
Spilled to tempdb	The sort or the hash did not fit in memory.	SQL Server
No join predicate, No statistics on the columns	The join has no condition, or the optimizer has no statistics for a column.	SQL Server
Missing index suggested	The plan contains a suggestion for a missing index, with the estimated impact.	SQL Server
Sorted with a filesort, Uses a temporary table	The sort or the grouping is done outside the index.	MySQL and MariaDB estimated plans. In an analyzed plan they are steps of the tree.

PostgreSQL

The statement is run as EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). It works for SELECT, WITH, VALUES, INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS and CREATE MATERIALIZED VIEW.

A report is a function, so explaining its call (select * from sp_DBR_sales(2024)) shows only one Function Scan. PostgreSQL does not show the queries inside the function, and the view says so. Copy the queries of the function into the SQL Editor and explain them one by one, with the values of the parameters written in.

SQL Server

The statement is run with SET STATISTICS XML ON and the actual plan is read from the result. It works for SELECT, WITH, INSERT, UPDATE, DELETE, MERGE and EXEC.

• For EXEC the plans of the statements inside the stored procedure are shown, one after another (up to five), so a report can be explained as a whole.
• The time of a step is available in the plans of SQL Server 2016 SP1 and later. In older plans the bars show the share of the estimated cost.
• The execution time, the logical reads of the steps and the missing index suggestions of the plan are shown.

MySQL

MySQL 8.0.18 or later is needed. The statement is run as EXPLAIN ANALYZE and the text tree of the plan is shown as the view. The time and the rows of a node in the text are averages of one loop, and the view multiplies them with the loops.

• EXPLAIN ANALYZE works for SELECT and for UPDATE and DELETE of several tables.
• MySQL cannot analyze INSERT, or UPDATE and DELETE of one table. For them the estimated plan (EXPLAIN FORMAT=JSON) is shown with a notice, and the statement is not run.
• CALL cannot be explained. Explain the statements of the procedure one by one.

MariaDB

MariaDB 10.1 or later is needed. The statement is run as ANALYZE FORMAT=JSON, which gives the real rows, loops and time of every table, the sorts (filesorts), temporary tables and page reads. It works for SELECT, UPDATE, DELETE and INSERT. CALL cannot be explained.

MySQL and MariaDB: tables that cannot be rolled back

A statement that changes data is run only when the tables it names are transactional (InnoDB, NDB, RocksDB or TokuDB). The changes of a MyISAM, MEMORY or Aria table cannot be rolled back. For those the statement is not run, and the estimated plan (EXPLAIN FORMAT=JSON) is shown with a notice that names the tables. The tables are found by the names in the statement, so a statement that only mentions such a table also gets the estimated plan.