juspay / juspay/invokr

perf(jobs): endpoint search does a sequential scan (consider pg_trgm GIN index)

Open
#48 0 comments 0 reactions 1 assignee Claimed by @tsdk02 View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.