How would you optimize a query that is running slowly due to a massive join on high-frequency vehicle sensor logs for Waymo?
Write a PostgreSQL query that returns completed sessions in the specified time range, including sessions with no matching logs or alerts. Pre-aggregate the high-frequency data before joining it to session and vehicle records.
vehicle_id, vehicle_model, session_id, started_at, valid_log_count, avg_speed_mps, peak_lidar_m, and open_alert_count.started_at, then session_id.| Column | Type | Description |
|---|---|---|
| log_idPK | INT | Unique sensor log identifier |
| session_id | INT | Driving session identifier |
| recorded_at | TIMESTAMP | Sensor reading timestamp |
| sensor_type | VARCHAR(30) | Sensor measurement category |
| reading_value | DECIMAL(10,2) | Numeric sensor reading |
| quality_valid | BOOLEAN | Whether the reading passed quality checks |
| Column | Type | Description |
|---|---|---|
| alert_idPK | INT | Unique alert identifier |
| session_id | INT | Driving session identifier |
| alert_timestamp | TIMESTAMP | Alert creation timestamp |
| severity | INT | Alert severity from 1 through 3 |
| is_open | BOOLEAN | Whether the alert remains open |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Unique driving session identifier |
| vehicle_id | INT | Vehicle assigned to the session |
| started_at | TIMESTAMP | Session start timestamp |
| is_completed | BOOLEAN | Whether the session completed successfully |
| Column | Type | Description |
|---|---|---|
| vehicle_idPK | INT | Unique Waymo vehicle identifier |
| vehicle_model | VARCHAR(50) | Vehicle model name |