This page describes Calíope 1.8, the version I am building right now. 1.7 is on the App Store for the Mac, iPad, iPhone, Apple Watch, Apple TV and Apple Vision Pro. The changelog says which version each feature landed in.

Adds an “Open in Calíope” button to every topic. It only works with the app installed.

Visual EXPLAIN

Visualize Execution Plan

Displays the query optimizer's operation tree.

Where it is: SQL Editor › Visual EXPLAIN

With a SELECT query in the SQL Editor, press Visual EXPLAIN in the toolbar. Calíope asks the server for the plan without running the query —EXPLAIN FORMAT=JSON in MySQL and MariaDB, EXPLAIN (FORMAT JSON) in PostgreSQL, SET SHOWPLAN_XML in SQL Server, EXPLAIN PLAN json = 1 in ClickHouse, EXPLAIN PLAN FOR in Oracle, EXPLAIN FORMAT='tidb_json' in TiDB— and opens the Visual EXPLAIN tab showing the plan's node tree.

Also in the SQLite Editor and the DuckDB Editor, with no server and offline: in the Execute SQL section, the Visual EXPLAIN button asks the file’s engine for the plan —EXPLAIN QUERY PLAN in SQLite, EXPLAIN (FORMAT JSON) in DuckDB— and opens it in this same tab. SQLite does not estimate rows, so its nodes carry none.

Each node displays the access type, the index used (if any), the estimated row count, and the detail the engine adds: the condition, the chosen index or the cost. Time only appears if the engine measured it, and without running the query none of them does.

SQL Server

In SQL Server each node shows, in place of the time, the cost the optimizer estimates for the node with everything below it (EstimatedTotalSubtreeCost). It is not milliseconds: it is optimizer units, useful to see which branch of the plan weighs most.

TiDB

TiDB has no EXPLAIN FORMAT=JSON, so Calíope asks for EXPLAIN FORMAT='tidb_json'. Each node is a TiDB operator (TableReader, Selection, TableFullScan, IndexLookUp), and its detail starts with the operator and says where it runs: cop[tikv] is TiKV, the storage layer; with no annotation, the TiDB node itself. A stats:pseudo in the detail warns that the table has no statistics and the rows are a default estimate: gather them with ANALYZE in Table Maintenance. A table with an alias in the query shows up under the alias, not its name.

Oracle

In Oracle the plan is written to PLAN_TABLE, and Calíope deletes its rows as soon as it reads it: asking for the plan is a write on the server, even if the query is a SELECT. Each node's detail carries the operation (TABLE ACCESS FULL, INDEX RANGE SCAN), the access and filter predicates, and the cost with the estimated bytes.

ClickHouse

In ClickHouse each table read is yellow if the key or a data-skipping index skipped granules, and red if it read them all. Its detail starts with the granules read out of those there were (“granules 2/123”), goes on with what each index skipped, in granules and parts, and ends with the estimate of rows, parts and marks.

Navigating the tree:
- macOS: the mouse wheel or a two-finger pinch on the trackpad to zoom; drag to pan the canvas.
- iPad: pinch with two fingers to zoom, and drag with one finger to pan.
- On all of them: the − and + buttons in the toolbar, with the percentage between them, and Fit to window to frame the whole tree. The shortcuts are ⌘−, ⌘+ and ⌘0.

The scale runs from 18 % to 350 %, which is plenty of room for plans with many levels of nesting.

Apple Vision Pro: as on iPad, with its own gestures: pinch and move to pan the canvas, and pinch with both hands to zoom. The buttons and shortcuts are the same.

Keywords: explain, plan, optimizer, tree, JSON explain, zoom, pan, navigate, pinch

Interpreting Access Colors

Node colors indicate the efficiency of data access.

Where it is: Visual EXPLAIN › Color Legend

Visual EXPLAIN nodes are colored by access type, and each card also writes it in its Access row: green = key lookup or constant (the engine goes straight to the row, or reads no data at all), yellow = scan of a range of an index, orange = scan of a whole index, red = full table scan (no index, the worst case), blue = sort, violet = a join of several tables, gray = the rest (an aggregate, a subquery or an operation Calíope does not classify). The legend in the bar names the four that say the most: green, yellow, red and blue.

A red node indicates an optimization opportunity, typically by adding an index.

Keywords: full scan, range, index, colors, optimization, explain

Visual EXPLAIN with VoiceOver

The canvas is a drawing that VoiceOver cannot read: the plan is given as a list in tree order, indented by depth.

Where it is: Workspace › Tools › EXPLAIN

The plan canvas is a drawing that VoiceOver cannot read, so Calíope offers it as a list. The Execution Plan Hierarchy section has one row per node, in the order of the tree and indented by depth, with the node name and its access type, the estimated rows, the time and the detail. The time always reads “—”, because the plan is requested without running the query.

SQL Server

In SQL Server, the time is replaced by the cost the optimizer estimates for the node with everything below it.

It works the same on every platform.

Keywords: VoiceOver, accessibility, EXPLAIN, plan, nodes