perf(jobs): endpoint search does a sequential scan (consider pg_trgm GIN index)
- Dominant language
- Rust
- Stars
- 2
- Forks
- 0
- Avg merge
- 1d 23h
- Merged PRs (30d)
- 10
Description
## Summary
The jobs-list endpoint search (`?endpoint=...`, added in #35) matches with a
leading-wildcard `ILIKE '%' || $n || '%'`. A leading `%` cannot use a B-tree
index, so this is a sequential scan of the workspace's `jobs` table on every
filtered request. Combined filters beyond `status` (which has
`idx_jobs_status`) also have no supporting index.
This is fine at current data volumes and was intentionally deferred from the
pagination/filter hardening PR — filing it so the perf work isn't lost.
## Where
- `crates/common/src/db/jobs.rs` — `build_list_query`, the `endpoint ILIKE ...`
condition.
- Per-workspace schema template: `migrations/workspace_v1.sql`.
## Proposed fix
Add a trigram GIN index so leading-wildcard `ILIKE` can be index-assisted:
```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX IF NOT EXISTS idx_{p}jobs_endpoint_trgm
ON {p}jobs USING gin (endpoint gin_trgm_ops);
```
Considerations:
- Requires the `pg_trgm` extension to be available/enabled in each tenant
schema's database; add to the workspace provisioning template.
- Measure first — for small per-workspace tables the planner may still prefer a
seq scan, and the index has write-amplification cost.
## Acceptance
- [ ] Decide whether endpoint search volume justifies the index.
- [ ] If yes, add `pg_trgm` + GIN index to `workspace_v1.sql` and verify
`EXPLAIN` shows it used for a representative `ILIKE '%x%'` query.
Follow-up to #35.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.