Walk me through how you would optimize a highly complex, slow-running SQL query used by the analytics team at Anduril Industries.
Using the provided PostgreSQL schema, produce an optimized query that preserves the required results. Consider filtering, join cardinality, aggregation strategy, latest-status selection, indexing, and how you would validate the improvement.
mission_id, mission_name, asset_count, active_asset_count, critical_event_count, avg_signal_strength, and latest_status_atmission_id ascending| Column | Type | Description |
|---|---|---|
| mission_idPK | INT | Mission identifier |
| mission_name | VARCHAR(100) | Mission name |
| mission_status | VARCHAR(30) | Mission lifecycle status |
| start_at | TIMESTAMP | Mission start timestamp |
| end_at | TIMESTAMP | Mission end timestamp |
| Column | Type | Description |
|---|---|---|
| mission_idPK | INT | Referenced mission |
| asset_idPK | INT | Referenced asset |
| assigned_at | TIMESTAMP | Asset assignment timestamp |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Telemetry event identifier |
| asset_id | INT | Reporting asset |
| occurred_at | TIMESTAMP | Event timestamp |
| severity | VARCHAR(20) | Event severity |
| signal_strength | DECIMAL(6,2) | Observed signal strength |
| Column | Type | Description |
|---|---|---|
| status_idPK | INT | Status history identifier |
| asset_id | INT | Status asset |
| status | VARCHAR(30) | Recorded asset status |
| recorded_at | TIMESTAMP | Status timestamp |