Query Optimization for OpenSAFELY

This page contains practical SQL-level guidance for teams building or reviewing OpenSAFELY queries on Trino.

Use native column types in predicates and joins

Columns such as snomed_concept_id, code_id, and hashed identifiers are stored as native numeric/binary types. Casting them to VARCHAR in predicates or join conditions can prevent file pruning.

-- Prefer native type comparison
WHERE obs.snomed_concept_id IN (BIGINT '123456789', BIGINT '987654321')

Prefer EXISTS for membership checks

When you only need to confirm that related rows exist, EXISTS usually performs better than LEFT JOIN ... IS NOT NULL.

SELECT DISTINCT pop.patient_id
FROM hive."your_username"."tmp_population" AS pop
WHERE EXISTS (
  SELECT 1
  FROM explorer_open_safely.observation AS obs
  WHERE obs.patient_id = pop.patient_id
    AND obs.snomed_concept_id = BIGINT '37687008'
)

Use explicit date ranges

Prefer bounded date predicates over expressions like YEAR(column) = ....

WHERE effective_datetime >= DATE '2022-01-01'
  AND effective_datetime < DATE '2023-01-01'

Use aggregates for latest-record logic where possible

For “latest per patient” patterns, aggregate functions can avoid heavy window materialization.

SELECT
  patient_id,
  MAX_BY(observation_id, effective_datetime) AS latest_observation_id,
  MAX(effective_datetime) AS latest_date
FROM explorer_open_safely.observation
GROUP BY patient_id

Investigating slow queries

Use execution plans during query tuning:

1. Run EXPLAIN for plan shape and pushdown checks.
2. Use EXPLAIN ANALYZE in controlled environments for runtime statistics.
3. Review data scanned, row counts, join strategy, and memory-heavy operators.

Related resources

• Trino Query Optimization
• OpenSAFELY Documentation