EXPLAIN

The EXPLAIN statement shows the physical query plan that will be executed. It displays a tree of operators that will run in sequence to produce the query results. The query optimizer transforms this plan to improve performance.

On MotherDuck queries, (L) indicates queries that are executed locally and (R) indicates the queries are executed remotely.

For more detailed query analysis, review the documentation on EXPLAIN ANALYZE.

note

The query profiling guide on DuckDB is a great place to start with this topic.

Syntax

This SQL query shows the physical query plan for an example query.

EXPLAIN <SQL query>

Example Usage

EXPLAIN
SELECT 1 AS col
UNION ALL
SELECT 2

This will return the physical plan, which executes entirely locally.

Physical Plan
┌───────────────────────────┐
│         UNION (L)         ├──────────────┐
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       PROJECTION (L)      ││       PROJECTION (L)      │
│    ────────────────────   ││    ────────────────────   │
│            col            ││             2             │
│                           ││                           │
│          ~1 Rows          ││          ~1 Rows          │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       DUMMY_SCAN (L)      ││       DUMMY_SCAN (L)      │
└───────────────────────────┘└───────────────────────────┘

EXPLAIN in MotherDuck compared to DuckDB

The MotherDuck EXPLAIN plan is similar to the DuckDB EXPLAIN plan, with two main differences:

• Operations that run locally are marked as (L), and operations running remotely on the MotherDuck service are marked as (R).
• The MotherDuck DuckDB extension adds four new type of custom operators, to exchange data between your local DuckDB and the MotherDuck service:
  • The UploadSink operator runs locally and sends data from your local DuckDB to the remote MotherDuck service.
  • The UploadSource operator runs remotely in the DuckDB on the MotherDuck side and consumes the uploaded data.
  • The DownloadSink operator runs remotely on the MotherDuck side and prepares the data to be downloaded by the local DuckDB.
  • The DownloadSource operator runs in your local DuckDB, fetching the data from the MotherDuck service made available via the remote DownloadSink.