Metrics

DuckDB provides a set of metrics that can be used to monitor the performance and health of the database.

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 OPERATOR_TYPE, all metrics can be turned on or off.

All Metrics

Name	Group	Description
`CPU_TIME`	core	CPU time spent on the query
`CUMULATIVE_CARDINALITY`	core	Cumulative cardinality of the query
`CUMULATIVE_ROWS_SCANNED`	core	Cumulative number of rows scanned by the query
`EXTRA_INFO`	core	Unique operator metrics
`LATENCY`	core	Time spent executing the entire query
`QUERY_NAME`	core	The SQL string of the query
`RESULT_SET_SIZE`	core	The size of the result
`ROWS_RETURNED`	core	The number of rows returned by the query
`BLOCKED_THREAD_TIME`	execution	Time spent waiting for a thread to become available
`SYSTEM_PEAK_BUFFER_MEMORY`	execution	Peak memory usage of the system
`SYSTEM_PEAK_TEMP_DIR_SIZE`	execution	Peak size of the temporary directory
`TOTAL_MEMORY_ALLOCATED`	execution	The total memory allocated by the buffer manager.
`ATTACH_LOAD_STORAGE_LATENCY`	file	Time spent loading from storage.
`ATTACH_REPLAY_WAL_LATENCY`	file	Time spent replaying the WAL file.
`CHECKPOINT_LATENCY`	file	Time spent running checkpoints
`COMMIT_LOCAL_STORAGE_LATENCY`	file	Time spent committing the transaction-local storage.
`TOTAL_BYTES_READ`	file	The total bytes read by the file system.
`TOTAL_BYTES_WRITTEN`	file	The total bytes written by the file system.
`WAITING_TO_ATTACH_LATENCY`	file	Time spent waiting to ATTACH a file.
`WAL_REPLAY_ENTRY_COUNT`	file	The total number of entries to replay in the WAL.
`WRITE_TO_WAL_LATENCY`	file	Time spent writing to the WAL.
`ALL_OPTIMIZERS`	phase_timing	Enables all optimizers
`CUMULATIVE_OPTIMIZER_TIMING`	phase_timing	Time spent in all optimizers
`PHYSICAL_PLANNER`	phase_timing	The time spent generating the physical plan
`PHYSICAL_PLANNER_COLUMN_BINDING`	phase_timing	The time spent binding the columns in the logical plan to physical columns
`PHYSICAL_PLANNER_CREATE_PLAN`	phase_timing	The time spent creating the physical plan
`PHYSICAL_PLANNER_RESOLVE_TYPES`	phase_timing	The time spent resolving the types in the logical plan to physical types
`PLANNER`	phase_timing	The time to generate the logical plan from the parsed SQL nodes.
`PLANNER_BINDING`	phase_timing	The time taken to bind the logical plan.
`OPERATOR_CARDINALITY`	operator	Cardinality of the operator
`OPERATOR_NAME`	operator	Name of the operator
`OPERATOR_ROWS_SCANNED`	operator	Number of rows scanned by the operator
`OPERATOR_TIMING`	operator	Time spent in the operator
`OPERATOR_TYPE`	operator	Type of the operator

Metric Groups

The metrics are organized into groups, which can be used to enable or disable related metrics together. The following is a list of the available metric groups:

ALL: All metrics
DEFAULT: The default set of metrics
CORE
EXECUTION
FILE
OPERATOR
OPTIMIZER
PHASE_TIMING

Core Metrics

core metrics

`CPU_TIME`

Note:

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.

`CUMULATIVE_CARDINALITY`

`CUMULATIVE_ROWS_SCANNED`

`EXTRA_INFO`

`LATENCY`

`QUERY_NAME`

`RESULT_SET_SIZE`

`ROWS_RETURNED`

Execution Metrics

Metrics that are collected during query execution

`BLOCKED_THREAD_TIME`

`SYSTEM_PEAK_BUFFER_MEMORY`

`SYSTEM_PEAK_TEMP_DIR_SIZE`

`TOTAL_MEMORY_ALLOCATED`

File Metrics

metrics that are collected during file operations

`ATTACH_LOAD_STORAGE_LATENCY`

`ATTACH_REPLAY_WAL_LATENCY`

`CHECKPOINT_LATENCY`

`COMMIT_LOCAL_STORAGE_LATENCY`

`TOTAL_BYTES_READ`

`TOTAL_BYTES_WRITTEN`

`WAITING_TO_ATTACH_LATENCY`

`WAL_REPLAY_ENTRY_COUNT`

`WRITE_TO_WAL_LATENCY`

Operator Metrics

metrics that are collected for each operator

`OPERATOR_CARDINALITY`

`OPERATOR_NAME`

`OPERATOR_ROWS_SCANNED`

`OPERATOR_TIMING`

`OPERATOR_TYPE`

Phase_timing Metrics

This group contains metrics related to the planner and the physical planner. The planner is responsible for generating the logical plan, whereas the physical planner is responsible for generating the physical plan from the logical plan.

`ALL_OPTIMIZERS`

`CUMULATIVE_OPTIMIZER_TIMING`

`PHYSICAL_PLANNER`

`PHYSICAL_PLANNER_COLUMN_BINDING`

`PHYSICAL_PLANNER_CREATE_PLAN`

`PHYSICAL_PLANNER_RESOLVE_TYPES`

`PLANNER`

`PLANNER_BINDING`

Optimizer Metrics

Optimizer metrics sit at the QUERY_ROOT level, and 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(){:.language-sql .highlight} table function.

Each optimizer has a corresponding metric that follows the template: OPTIMIZER_⟨OPTIMIZER_NAME⟩{:.language-sql .highlight}. 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
CUMULATIVE_OPTIMIZER_TIMING

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 following is a list of the available cumulative metrics:

CPU_TIME
CUMULATIVE_CARDINALITY
CUMULATIVE_ROWS_SCANNED
CUMULATIVE_OPTIMIZER_TIMING

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 configure_profiling = '{"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": [
...