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.
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:
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.
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.
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() 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:
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:
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.
{"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":[...
