--- layout: docu redirect_from: - /dev/metrics - /docs/dev/metrics - /docs/stable/dev/metrics - /docs/current/dev/metrics - /docs/lts/dev/metrics - /docs/preview/dev/metrics title: Metrics --- 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]({% link docs/2.0/dev/profiling.md %}#pragmas), 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: ```sql SELECT metric_name, metric_type, unit FROM duckdb_available_metrics(); ``` ## All Metrics The metrics are organized into the following groups: * [`query`](#query-metrics) * [`system`](#system-metrics) * [`io`](#io-metrics) * [`storage`](#storage-metrics) * [`operator`](#operator-metrics) * [`optimizer`](#optimizer-metrics) * [`parser`](#parser-metrics) * [`planner`](#planner-metrics) * [`physical_planner`](#physical_planner-metrics) ### `query` Metrics Metrics measured over the entire query. | Name | Type | Unit | Description | |---------------------------------------|----------|------------|----------------------------------------------------------------------------| | `query.cpu_time` | `double` | seconds | CPU time spent on the query | | `query.sql` | `string` | | The SQL string of the query | | `query.total_intermediate_rows` | `uint64` | rows | Cumulative number of intermediate rows produced by the query | | `query.total_intermediate_size_bytes` | `uint64` | bytes | Cumulative size in bytes of all intermediate results produced by the query | | `query.total_row_groups_scanned` | `uint64` | row_groups | Cumulative number of row groups scanned by the query | | `query.total_row_groups_to_scan` | `uint64` | row_groups | Cumulative total number of row groups available to scan | | `query.total_rows_scanned` | `uint64` | rows | Cumulative number of rows scanned by the query | | `query.total_time` | `double` | seconds | Time spent executing the entire query | ### `system` Metrics Metrics on system resources, such as memory and threads, used while running the query. | Name | Type | Unit | Description | |-------------------------------------|----------|---------|------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `system.blocked_thread_time` | `double` | seconds | Time spent waiting for a thread to become available | | `system.peak_buffer_memory` | `uint64` | bytes | Peak memory usage of the buffer manager | | `system.peak_streaming_buffer_size` | `uint64` | bytes | Peak number of bytes a streaming query result held: queued bytes plus the units blocked workers hold, both exact. Units still under construction are not counted | | `system.peak_temp_dir_size` | `uint64` | bytes | Peak size of the temporary directory | | `system.total_memory_allocated` | `uint64` | bytes | The total memory allocated by the buffer manager | ### `io` Metrics Metrics on the data read from and written to storage. | Name | Type | Unit | Description | |--------------------------|----------|--------|----------------------------------------------| | `io.total_bytes_read` | `uint64` | bytes | The total amount of bytes read from storage | | `io.total_bytes_written` | `uint64` | bytes | The total amount of bytes written to storage | ### `storage` Metrics Metrics on storage operations, such as attaching databases, writing to the WAL and checkpointing. | Name | Type | Unit | Description | |----------------------------------------|----------|---------|------------------------------------------------------------| | `storage.attach_load_storage_latency` | `double` | seconds | Time spent loading from storage | | `storage.attach_replay_wal_latency` | `double` | seconds | Time spent replaying the WAL file | | `storage.checkpoint_latency` | `double` | seconds | Time spent running checkpoints | | `storage.commit_local_storage_latency` | `double` | seconds | Time spent committing the transaction-local storage | | `storage.total_vacuum_time` | `double` | seconds | Total time spent running vacuum tasks during checkpointing | | `storage.waiting_to_attach_latency` | `double` | seconds | Time spent waiting to ATTACH a file | | `storage.wal_replay_entry_count` | `uint64` | rows | The total number of entries to replay in the WAL | | `storage.write_to_wal_latency` | `double` | seconds | Time spent writing to the WAL | ### `operator` Metrics Metrics collected for each individual operator in the query plan. These are reported in the `operator` tree of the profiling output. | Name | Type | Unit | Description | |-------------------------------------|----------|------------|---------------------------------------------------------------| | `operator.cpu_time` | `double` | seconds | CPU time spent in the operator | | `operator.extra_info` | `map` | | Unique operator metrics | | `operator.intermediate_rows` | `uint64` | rows | Number of intermediate rows produced by the operator | | `operator.intermediate_size_bytes` | `uint64` | bytes | Intermediate size in bytes produced by the operator | | `operator.row_groups_scanned` | `uint64` | row_groups | Number of row groups scanned by the operator | | `operator.rows_scanned` | `uint64` | rows | Number of rows scanned by the operator | | `operator.timing` | `double` | seconds | Time spent in the operator | | `operator.total_row_groups_to_scan` | `uint64` | row_groups | Total number of row groups available for the operator to scan | | `operator.type` | `string` | | Type of the operator | ### `optimizer` Metrics Metrics measuring the time spent in the [optimizers]({% link docs/2.0/internals/overview.md %}#optimizer). | Name | Type | Unit | Description | |------------------------|----------|---------|---------------------------------------| | `optimizer.total_time` | `double` | seconds | Time spent in all optimizers combined | 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()`{:.language-sql .highlight} table function]({% link docs/2.0/sql/meta/duckdb_table_functions.md %}#duckdb_optimizers): `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. | Name | Type | Unit | Description | |---------------------|----------|---------|----------------------------------| | `parser.total_time` | `double` | seconds | Time spent parsing the SQL query | ### `planner` Metrics Metrics on generating the logical plan. | Name | Type | Unit | Description | |------------------------|----------|---------|----------------------------------------| | `planner.binding_time` | `double` | seconds | Time spent binding the logical plan | | `planner.total_time` | `double` | seconds | Time spent generating the logical plan | ### `physical_planner` Metrics Metrics on generating the physical plan. | Name | Type | Unit | Description | |-----------------------------------|----------|---------|------------------------------------------------------------------------| | `physical_planner.column_binding` | `double` | seconds | Time spent binding the columns in the logical plan to physical columns | | `physical_planner.create_plan` | `double` | seconds | Time spent creating the physical plan | | `physical_planner.resolve_types` | `double` | seconds | Time spent resolving the types in the logical plan to physical types | | `physical_planner.total_time` | `double` | seconds | Time spent 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. ```sql 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: ```json { "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. ```sql 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: ```json { "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 } } ```