Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

Profiling

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. For an in-depth explanation, see the “Profiling” page in the Developer Documentation.

## `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.

## `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.

## 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.

Setting | Description | Default | Options
--- | --- | --- | ---
`enable_profiling`, `enable_profile` | Turn on profiling. | `query_tree` | `query_tree`, `json`, `query_tree_optimizer`, `no_output`
`profiling_output` | Set a profiling output file. | Console | A filepath.
`profiling_mode` | Toggle additional optimizer and planner metrics. | `standard` | `standard`, `detailed`
`custom_profiling_settings` | Enable or disable specific metrics. | All metrics except those activated by detailed profiling. | A JSON object that matches the following: `{"METRIC_NAME": "boolean", ...}`. See the metrics section below.
`disable_profiling`, `disable_profile` | Turn off profiling. |  |

## Metrics

The query tree has two types of nodes: the `QUERY_ROOT` and `OPERATOR` nodes. The `QUERY_ROOT` refers exclusively to the top-level node, and the metrics it contains are measured over the entire query. The `OPERATOR` nodes refer to the individual operators in the query plan. Some metrics are only available for `QUERY_ROOT` nodes, while others are only for `OPERATOR` nodes. The table below describes each metric and which nodes they are available for.

Other than `QUERY_NAME` and `OPERATOR_TYPE`, it is possible to turn all metrics on or off.

Metric | Return type | Unit | Query | Operator | Description
--- | --- | --- | --- | --- | ---
`BLOCKED_THREAD_TIME` | `double` | seconds | ✅ |  | The total time threads are blocked.
`EXTRA_INFO` | `string` |  | ✅ | ✅ | Unique operator metrics.
`LATENCY` | `double` | seconds | ✅ |  | The total elapsed query execution time.
`OPERATOR_CARDINALITY` | `uint64` | absolute |  | ✅ | The cardinality of each operator, i.e., the number of rows it returns to its parent. Operator equivalent of `ROWS_RETURNED`.
`OPERATOR_ROWS_SCANNED` | `uint64` | absolute |  | ✅ | The total rows scanned by each operator.
`OPERATOR_TIMING` | `double` | seconds |  | ✅ | The time taken by each operator. Operator equivalent of `LATENCY`.
`OPERATOR_TYPE` | `string` |  |  | ✅ | The name of each operator.
`QUERY_NAME` | `string` |  | ✅ |  | The query string.
`RESULT_SET_SIZE` | `uint64` | bytes | ✅ | ✅ | The size of the result.
`ROWS_RETURNED` | `uint64` | absolute | ✅ |  | The number of rows returned by the query.

### Cumulative Metrics

DuckDB also supports several cumulative metrics that are available in all nodes. In the `QUERY_ROOT` node, these metrics represent the sum of the corresponding metrics across all operators in the query. The `OPERATOR` nodes represent the sum of the operator's specific metric and those of all its children recursively.

These cumulative metrics can be enabled independently, even if the underlying specific metrics are disabled. The table below shows the cumulative metrics. It also depicts the metric based on which DuckDB calculates the cumulative metric.

Metric | Unit | Metric calculated cumulatively
--- | --- | ---
`CPU_TIME` | seconds | `OPERATOR_TIMING`
`CUMULATIVE_CARDINALITY` | absolute | `OPERATOR_CARDINALITY`
`CUMULATIVE_ROWS_SCANNED` | absolute | `OPERATOR_ROWS_SCANNED`

`CPU_TIME` measures the cumulative operator timings. It does not include time spent in other stages, like parsing, query planning, etc. Thus, for some queries, the `LATENCY` in the `QUERY_ROOT` can be greater than the `CPU_TIME`.

## 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 `OPTIMIZER`, `PLANNER`, and `PHYSICAL_PLANNER` metrics. They are measured in seconds and returned as a `double`. It is possible to toggle each of these additional metrics individually.

### Optimizer Metrics

At the `QUERY_ROOT` node, there are metrics that measure the time taken by each optimizer. These metrics are only available when the specific optimizer is enabled. The available optimizations can be queried using the `duckdb_optimizers() table function`.

Each optimizer has a corresponding metric that follows the template: `OPTIMIZER_⟨OPTIMIZER_NAME⟩`. For example, the `OPTIMIZER_JOIN_ORDER` metric corresponds to the `JOIN_ORDER` optimizer.

Additionally, the following metrics are available to support the optimizer metrics:

- `ALL_OPTIMIZERS`: Enables all optimizer metrics and measures the time the optimizer parent node takes.
- `CUMMULATIVE_OPTIMIZER_TIMING`: The cumulative sum of all optimizer metrics. It is usable without turning on all optimizer metrics.

### Planner Metrics

The planner is responsible for generating the logical plan. Currently, DuckDB measures two metrics in the planner:

- `PLANNER`: The time to generate the logical plan from the parsed SQL nodes.
- `PLANNER_BINDING`: The time taken to bind the logical plan.

### Physical Planner Metrics

The physical planner is responsible for generating the physical plan from the logical plan. The following are the metrics supported in the physical planner:

- `PHYSICAL_PLANNER`: The time spent generating the physical plan.
- `PHYSICAL_PLANNER_COLUMN_BINDING`: The time spent binding the columns in the logical plan to physical columns.
- `PHYSICAL_PLANNER_RESOLVE_TYPES`: The time spent resolving the types in the logical plan to physical types.
- `PHYSICAL_PLANNER_CREATE_PLAN`: The time spent creating the physical plan.

## Custom Metrics Examples

The following examples demonstrate how to enable custom profiling and set the output format to `json`. In the first example, we enable profiling and set the output to a file. We only enable `EXTRA_INFO`, `OPERATOR_CARDINALITY`, and `OPERATOR_TIMING`.

    CREATE TABLE students (name VARCHAR, sid INTEGER);
    CREATE TABLE exams (eid INTEGER, subject VARCHAR, sid INTEGER);
    INSERT INTO students VALUES ('Mark', 1), ('Joe', 2), ('Matthew', 3);
    INSERT INTO exams VALUES (10, 'Physics', 1), (20, 'Chemistry', 2), (30, 'Literature', 3);

    PRAGMA enable_profiling = 'json';
    PRAGMA profiling_output = '/path/to/file.json';

    PRAGMA custom_profiling_settings = '{"CPU_TIME": "false", "EXTRA_INFO": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_TIMING": "true"}';

    SELECT name
    FROM students
    JOIN exams USING (sid)
    WHERE name LIKE 'Ma%';

The file's content after executing the query:

    {
        "extra_info": {},
        "query_name": "SELECT name\nFROM students\nJOIN exams USING (sid)\nWHERE name LIKE 'Ma%';",
        "children": [
            {
                "operator_timing": 0.000001,
                "operator_cardinality": 2,
                "operator_type": "PROJECTION",
                "extra_info": {
                    "Projections": "name",
                    "Estimated Cardinality": "1"
                },
                "children": [
                    {
                        "extra_info": {
                            "Join Type": "INNER",
                            "Conditions": "sid = sid",
                            "Build Min": "1",
                            "Build Max": "3",
                            "Estimated Cardinality": "1"
                        },
                        "operator_cardinality": 2,
                        "operator_type": "HASH_JOIN",
                        "operator_timing": 0.00023899999999999998,
                        "children": [
    ...

The second example adds detailed metrics to the output.

    PRAGMA profiling_mode = 'detailed';

    SELECT name
    FROM students
    JOIN exams USING (sid)
    WHERE name LIKE 'Ma%';

The contents of the outputted file:

    {
      "all_optimizers": 0.001413,
      "cumulative_optimizer_timing": 0.0014120000000000003,
      "planner": 0.000873,
      "planner_binding": 0.000869,
      "physical_planner": 0.000236,
      "physical_planner_column_binding": 0.000005,
      "physical_planner_resolve_types": 0.000001,
      "physical_planner_create_plan": 0.000226,
      "optimizer_expression_rewriter": 0.000029,
      "optimizer_filter_pullup": 0.000002,
      "optimizer_filter_pushdown": 0.000102,
    ...
      "optimizer_column_lifetime": 0.000009999999999999999,
      "rows_returned": 2,
      "latency": 0.003708,
      "cumulative_rows_scanned": 6,
      "cumulative_cardinality": 11,
      "extra_info": {},
      "cpu_time": 0.000095,
      "optimizer_build_side_probe_side": 0.000017,
      "result_set_size": 32,
      "blocked_thread_time": 0.0,
      "query_name": "SELECT name\nFROM students\nJOIN exams USING (sid)\nWHERE name LIKE 'Ma%';",
      "children": [
        {
          "operator_timing": 0.000001,
          "operator_rows_scanned": 0,
          "cumulative_rows_scanned": 6,
          "operator_cardinality": 2,
          "operator_type": "PROJECTION",
          "cumulative_cardinality": 11,
          "extra_info": {
            "Projections": "name",
            "Estimated Cardinality": "1"
          },
          "result_set_size": 32,
          "cpu_time": 0.000095,
          "children": [
    ...

## 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`.

##### About this page

- Report content issue
- Edit this page on GitHub