Definition

The Query Utilization data model surfaces runtime and resource metrics for queries executed by authenticated users against the OpenSafely data platform. It is automatically scoped to the currently authenticated user, providing self-service visibility into query performance, resource consumption, and execution history.

Information

Each record in the Query Utilization model represents a single query execution event. Records are automatically filtered to the current authenticated user, ensuring users only observe their own query history.

Results are ordered by create_time descending so the most recently submitted queries appear first.

Key Data Fields

Identity & Classification

• usr — The username of the user who submitted the query.
• query — The full SQL text of the executed query.
• query_state — Lifecycle state of the query (e.g. FINISHED, FAILED, RUNNING).
• query_type — Category of the query (e.g. SELECT, INSERT).
• query_plan — The internal execution plan produced by the query engine.

Row Metrics

• total_rows — Total number of rows read or processed during execution.
• output_rows — Number of rows returned to the caller.

Timing Metrics

• cpu_time_ms — Total CPU time consumed across all workers, in milliseconds.
• wall_time_ms — End-to-end elapsed (wall-clock) time, in milliseconds.
• queued_time_ms — Time the query spent waiting in the scheduler queue before execution started, in milliseconds.
• analysis_time — Duration of the query analysis (planning) phase.
• execution_time — Duration of the actual execution phase.
• create_time — Timestamp when the query was submitted to the engine.
• end_time — Timestamp when the query completed (successfully or otherwise).

Resource Metrics

• cumulative_memory — Peak cumulative memory allocated across all query stages, in bytes.

Error Information

• failure_info — Structured details about the failure if query_state is FAILED; NULL for successful queries.

Overview

flowchart TB
    subgraph container["Query Utilization"]
        n1["Identity & Classification"]
        n2["Row Metrics"]
        n3["Timing Metrics"]
        n4["Resource Metrics"]
        n5["Error Information"]
    end

    n6["Query Execution Event"] --> container
    container --> n7["Query Utilization Model"]

Examples

View your most recent queries

SELECT
  usr,
  query,
  query_state,
  query_type,
  total_rows,
  output_rows,
  cpu_time_ms,
  wall_time_ms,
  queued_time_ms,
  cumulative_memory,
  analysis_time,
  execution_time,
  create_time,
  end_time,
  failure_info
FROM user_query_utilization
ORDER BY create_time DESC;

Find failed queries

SELECT
  usr,
  query,
  query_state,
  failure_info,
  create_time,
  end_time
FROM user_query_utilization
WHERE query_state = 'FAILED'
ORDER BY create_time DESC;

Identify long-running or resource-intensive queries

SELECT
  usr,
  query,
  query_state,
  wall_time_ms,
  cpu_time_ms,
  cumulative_memory,
  total_rows,
  output_rows,
  create_time
FROM user_query_utilization
WHERE query_state = 'FINISHED'
ORDER BY wall_time_ms DESC;