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](/docs/2.0/dev/profiling.html#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();
```
## 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
}
}
```