Profiling is essential to help understand why certain queries exhibit specific performance characteristics. DuckDB contains several built-in features to enable query profiling, which this page covers. For a high-level example of using EXPLAIN, see the “Inspect Query Plans” page.

Statements

The EXPLAIN Statement

The first step to profiling a query can include examining the query plan. The EXPLAIN statement shows the query plan and describes what is going on under the hood.

The EXPLAIN ANALYZE Statement

The query plan helps developers understand the performance characteristics of the query. However, it is often also necessary to examine the performance numbers of individual operators and the cardinalities that pass through them. The EXPLAIN ANALYZE statement enables obtaining these, as it pretty-prints the query plan and also executes the query. Thus, it provides the actual run-time performance numbers.

The FORMAT Option

The EXPLAIN [ANALYZE] statement allows exporting to several formats:

• text – default ASCII-art style output
• graphviz – produces a DOT output, which can be rendered with Graphviz
• html – produces an HTML output, which can be rendered with treeflex
• json – produces a JSON output
• mermaid – produces a Mermaid flowchart

To specify a format, use the FORMAT tag:

EXPLAIN (FORMAT html) SELECT 42 AS x;

Pragmas

DuckDB supports several pragmas for turning profiling on and off and controlling the level of detail in the profiling output.

The following pragmas are available and can be set using either PRAGMA or SET. They can also be reset using RESET, followed by the setting name. For more information, see the “Profiling” section of the pragmas page.

Table Functions

These table functions were introduced in DuckDB v1.5.0.

DuckDB provides table functions to enable and disable profiling, consolidating multiple settings into a single call.

enable_profiling()

The enable_profiling() function configures profiling with the specified options.

CALL enable_profiling(
    format := 'json',
    save_location := '/path/to/output.json',
    coverage := 'select',
    mode := 'standard',
    metrics := ['QUERY_NAME', 'LATENCY', 'OPERATOR_TIMING']
);

All parameters are optional and named. You can also pass metrics as an unnamed parameter:

CALL enable_profiling(['LATENCY', 'RESULT_SET_SIZE']);

disable_profiling()

The disable_profiling() function turns off profiling.

CALL disable_profiling();

Metrics

DuckDB supports a wide range of metrics that can be enabled or disabled independently. To learn more and to see the full list of available metrics, refer to the metrics documentation.

Detailed Profiling

When the profiling_mode is set to detailed, an extra set of metrics are enabled, which are only available in the QUERY_ROOT node. These include all the metrics in the Phase timing metric group. It is possible to toggle each of these additional metrics individually.

Query Graphs

It is also possible to render the profiling output as a query graph. The query graph visually represents the query plan, showing the operators and their relationships. The query plan must be output in the json format and stored in a file. After writing a profiling output to its designated file, the Python script can render it as a query graph. The script requires the duckdb Python module to be installed. It generates an HTML file and opens it in your web browser.

python -m duckdb.query_graph /path/to/file.json

Notation in Query Plans

In query plans, the hash join operators adhere to the following convention: the probe side of the join is the left operand, while the build side is the right operand.

Join operators in the query plan show the join type used:

• Inner joins are denoted as INNER.
• Left outer joins and right outer joins are denoted as LEFT and RIGHT, respectively.
• Full outer joins are denoted as FULL.

Tip To visualize query plans, consider using the DuckDB execution plan visualizer developed by the Database Systems Research Group at the University of Tübingen.