mirror of
https://github.com/windmill-labs/windmill.git
synced 2026-08-20 16:02:19 +00:00
Ruben Fiszel
d4673c2e91
When failures are sparse (<1%), filtering by failure status on the runs
page required scanning millions of success rows. Add a partial index on
v2_job_completed (workspace_id, completed_at DESC) WHERE status IN
('failure', 'canceled') and switch ORDER BY to completed_at when
filtering failures, so Postgres walks the small partial index directly.
Benchmarked at 5.2M rows / 1% failure rate:
- LIMIT 30: 800ms -> 0.4ms (2000x faster)
- LIMIT 1000: 550ms -> 21ms (26x faster)
Co-authored-by: Claude Opus 4.6 <noreply@anthropic.com>
8 lines
483 B
SQL
8 lines
483 B
SQL
-- Partial index for fast failure/canceled filtering on the runs page.
|
|
-- When failures are sparse (<1%) this avoids scanning millions of successful jobs.
|
|
-- The query orders by completed_at DESC (switched from created_at when success=false),
|
|
-- so this index provides both filtering and ordering in a single scan.
|
|
CREATE INDEX IF NOT EXISTS ix_v2_job_completed_failure_workspace
|
|
ON v2_job_completed (workspace_id, completed_at DESC)
|
|
WHERE status IN ('failure', 'canceled');
|