Control / log · All operations

Explain or profile a query plan

A variant of Log and annotate a run

Show the plan a query will run, or run it and report where the time went.

Status

In arcform

Not built yet

arcform does not do this yet; it would live in how a Protocol runs. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (3)
  • EXPLAINread 2026-09-27

    Show the logical and physical plan of a statement: tree, indent, pgjson or graphviz

  • EXPLAIN ANALYZEread 2026-09-27

    Run the statement and show its plan with execution metrics

  • explainread 2026-09-27

    DataFrame method: Return the plan of the DataFrame so far

DuckDB (1)
  • Profilingread 2026-09-27

    Report where a query spent its time

Polars (2)
  • Expr.meta (group)read 2026-09-27

    16 methods: output_name, root_names, is_column, tree_format, serialize and others; inspect the expression itself

  • LazyFrame query plan inspection (family)read 2026-09-27

    4 methods: explain, show_graph, profile, inspect; print, draw or time the query plan and its nodes

Background

A query plan is the sequence of steps a relational database management system uses to access data, and a system may offer several ways to return it.[1] In DuckDB, EXPLAIN shows the plan of a query without executing it, and EXPLAIN ANALYZE runs the query and shows the time spent in each operator of the plan.[2]

When to use it

There are typically many ways to execute a given query, with widely varying performance.[1] A query optimizer evaluates some of the correct plans for a query and returns what it considers the best option.[1] Because query optimizers are imperfect, database users and administrators sometimes need to examine and tune the plans an optimizer produces.[1] Reviewing a query plan can show opportunities for new indexes, or that the database is not taking advantage of existing ones.[1]

In DuckDB, EXPLAIN ANALYZE runs the query it profiles.[2] On a query that takes minutes, read the plain EXPLAIN first, and run EXPLAIN ANALYZE once you know what you are looking for. (Meridian's view)

In DuckDB 2.0, EXPLAIN (SQL) renders the optimized plan back as executable DuckDB SQL, which can differ substantially from the input query, for example through join reordering.[2] In DuckDB, the exact formatting and any generated aliases in the output of EXPLAIN (SQL) are not a stable interface.[2] Read that rewritten SQL to understand what the optimizer did, and never parse it in a pipeline. (Meridian's view)

The method

A query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.[3] Cost-based query optimizers assign an estimated cost to each possible query plan and choose the plan with the smallest cost.[3] Most query optimizers represent a plan as a tree of plan nodes, each a single operation, with intermediate results flowing from the leaves, which scan the data, up to the top.[3] There are two types of query optimization: logical optimization, which generates a sequence of relational algebra to solve the query, and physical optimization, which decides how to carry out each operation.[3]

Profiling is a form of dynamic program analysis that measures, for example, a program's memory or time use, or the frequency and duration of function calls.[4] In DuckDB, a query is profiled with the EXPLAIN ANALYZE statement.[2]

Some database tools draw a graphical representation of a query plan, while others return a textual description of it.[1] In DataFusion, EXPLAIN can print a plan in a tree format modelled after DuckDB plans, an indent format showing both the logical and the physical plan, a pgjson format modelled after the Postgres JSON format, or a graphviz format in the DOT language.[5] In DataFusion, the pgjson format can be loaded into existing plan visualisation tools, such as dalibo.[5] In Polars, LazyFrame.explain creates a string representation of the query plan, and LazyFrame.show_graph shows a plot of it.[6]

Compared with the alternatives

In DuckDB, the Metrics log type logs profiling metrics collected during query execution, and the QueryLog type logs which queries are executed.[7]

Sources

  1. [1]Query plan - Wikipediasecondary source
  2. [2]Profiling Queries – DuckDBdocumentation
  3. [3]Query optimization - Wikipediasecondary source
  4. [4]Profiling (computer programming) - Wikipediasecondary source
  5. [5]EXPLAIN — Apache DataFusion main documentationdocumentation
  6. [6]Descriptive — Polars documentationdocumentation
  7. [7]Logging – DuckDBdocumentation