| 1 | -- Apply: wrangler d1 execute reasonix-crash --remote --file=migrate-window-index-fix.sql |
| 2 | -- |
| 3 | -- Every dashboard aggregate is `WHERE date >= ... GROUP BY <other columns>`, and |
| 4 | -- each table's primary key already starts with `date`. Offering SQLite a second |
| 5 | -- index on the GROUP BY columns makes it prefer an ordered full-index scan — |
| 6 | -- skipping the window prune and reading every historical row back through the |
| 7 | -- table. Measured on seeded copies (65 days retained, 30-day window): |
| 8 | -- metrics 180k rows: 807ms -> 88ms |
| 9 | -- metric_users 5.3M rows: 31.6s -> 3.3s |
| 10 | -- pings 568k rows: 672ms -> 459ms |
| 11 | -- Dropping them also drops their write cost on the ingest path. |
| 12 | -- |
| 13 | -- Run this only against a worker that no longer creates the cli_* three: |
| 14 | -- ensureCLITelemetrySchema used to rebuild them on the next CLI request. |
| 15 | DROP INDEX IF EXISTS metrics_signal_bucket; |
| 16 | DROP INDEX IF EXISTS metric_users_signal_bucket; |
| 17 | DROP INDEX IF EXISTS pings_version; |
| 18 | DROP INDEX IF EXISTS cli_metrics_signal_bucket; |
| 19 | DROP INDEX IF EXISTS cli_metric_users_signal_bucket; |
| 20 | DROP INDEX IF EXISTS cli_pings_version; |
| 21 |