Describe how you would optimize a slow-running query that joins multiple large event tables containing billions of communication logs at Twilio.
Provide an optimized PostgreSQL query and explain the main performance decisions. Use the supplied tables and restrict processing to communication events from 2025-01-01 through 2025-01-03.
account_id, account_name, event_day, messages_sent, delivered_events, failed_events, and total_cost.account_id, then event_day.| Column | Type | Description |
|---|---|---|
| account_idPK | BIGINT | Twilio customer account identifier |
| account_name | VARCHAR(120) | Customer account name |
| Column | Type | Description |
|---|---|---|
| message_idPK | BIGINT | Unique communication message identifier |
| account_id | BIGINT | Owning customer account |
| event_ts | TIMESTAMP | Message creation timestamp |
| Column | Type | Description |
|---|---|---|
| delivery_event_idPK | BIGINT | Unique delivery event identifier |
| message_id | BIGINT | Related communication message |
| event_ts | TIMESTAMP | Delivery event timestamp |
| delivery_status | VARCHAR(30) | Delivery outcome such as delivered or failed |
| Column | Type | Description |
|---|---|---|
| cost_event_idPK | BIGINT | Unique cost event identifier |
| message_id | BIGINT | Related communication message |
| event_ts | TIMESTAMP | Cost event timestamp |
| cost_amount | NUMERIC(12,4) | Cost associated with the event |