DuckDB provides a set of metrics that can be used to monitor the performance and health of the database. Metrics are named group.metric, e.g., query.total_time or operator.timing.

The set of collected metrics is controlled with the tracked_metrics setting, which takes a list of glob patterns, e.g., ['query.*', 'operator.timing']. By default, all metrics are collected (['*']). The available metrics, along with their types and units, can be listed with the duckdb_available_metrics() table function:

SELECT metric_name, metric_type, unit
FROM duckdb_available_metrics();

All Metrics

The metrics are organized into the following groups:

• query
• system
• io
• storage
• operator
• optimizer
• parser
• planner
• physical_planner

query Metrics

Metrics measured over the entire query.

system Metrics

Metrics on system resources, such as memory and threads, used while running the query.

io Metrics

Metrics on the data read from and written to storage.

storage Metrics

Metrics on storage operations, such as attaching databases, writing to the WAL and checkpointing.

operator Metrics

Metrics collected for each individual operator in the query plan. These are reported in the operator tree of the profiling output.

optimizer Metrics

Metrics measuring the time spent in the optimizers.

Additionally, each optimizer has a corresponding metric that follows the template optimizer.optimizer_name and measures the time (in seconds) spent in that optimizer. These metrics are only collected when the specific optimizer is enabled. The available optimizers can be queried using the duckdb_optimizers() table function:

optimizer.expression_rewriter, optimizer.filter_pullup, optimizer.filter_pushdown, optimizer.empty_result_pullup, optimizer.cte_filter_pusher, optimizer.regex_range, optimizer.in_clause, optimizer.join_order, optimizer.deliminator, optimizer.unnest_rewriter, optimizer.unused_columns, optimizer.statistics_propagation, optimizer.common_subexpressions, optimizer.common_aggregate, optimizer.column_lifetime, optimizer.build_side_probe_side, optimizer.limit_pushdown, optimizer.top_n, optimizer.compressed_materialization, optimizer.duplicate_groups, optimizer.reorder_filter, optimizer.sampling_pushdown, optimizer.join_filter_pushdown, optimizer.extension, optimizer.materialized_cte, optimizer.aggregate_function_rewriter, optimizer.late_materialization, optimizer.cte_inlining, optimizer.row_group_pruner, optimizer.top_n_window_elimination, optimizer.common_subplan, optimizer.join_elimination, optimizer.window_self_join, optimizer.projection_pullup, optimizer.outer_join_simplification, optimizer.row_number_rewriter, optimizer.partitioned_execution, optimizer.partial_aggregate_pushdown.

parser Metrics

Metrics on parsing the SQL query.

planner Metrics

Metrics on generating the logical plan.

physical_planner Metrics

Metrics on generating the physical plan.

Examples

The following example enables profiling, sets the output to a file and only collects the total query time along with the operator metrics.

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);

SET enable_profiling = 'json';
SET profiling_output = '/path/to/file.json';
SET tracked_metrics = ['query.total_time', 'operator.*'];

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

The file's content after executing the query:

{
    "operator": [
        {
            "extra_info": {},
            "intermediate_rows": 0,
            "intermediate_size_bytes": 0,
            "timing": 5.458e-06,
            "type": "RESULT_COLLECTOR",
            "children": [
                {
                    "extra_info": {
                        "Projections": "name",
                        "Estimated Cardinality": "1"
                    },
                    "intermediate_rows": 2,
                    "intermediate_size_bytes": 40,
                    "timing": 8.3e-08,
                    "type": "PROJECTION",
                    "children": [
                        {
                            "extra_info": {
                                "Join Type": "INNER",
                                "Conditions": "sid = sid",
                                "Estimated Cardinality": "1"
                            },
                            "intermediate_rows": 2,
                            "intermediate_size_bytes": 48,
                            "timing": 4.4209e-05,
                            "type": "HASH_JOIN",
                            "children": [
...
    "query": {
        "total_time": 0.001491167
    }
}

The second example collects all query and planner metrics, as well as the total time spent in the optimizers.

SET tracked_metrics = ['query.*', 'planner.*', 'optimizer.total_time'];

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

The contents of the output file:

{
    "optimizer": {
        "total_time": 0.000179833
    },
    "planner": {
        "binding_time": 5e-05,
        "total_time": 6.8959e-05
    },
    "query": {
        "cpu_time": 5.8541999999999996e-05,
        "sql": "SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%';\n",
        "total_intermediate_rows": 8,
        "total_intermediate_size_bytes": 136,
        "total_row_groups_scanned": 2,
        "total_row_groups_to_scan": 2,
        "total_rows_scanned": 6,
        "total_time": 0.001529
    }
}