* fix(search): add pg_trgm index fallback + statement_timeout guard (MUL-4059)
Root cause of the "search freezes with no response" symptom reported in
MUL-4059: the search handler runs LOWER(col) LIKE '%pattern%' queries
that expect a pg_bigm GIN index (migrations 032, 033, 036), but every
migration wraps the CREATE EXTENSION + CREATE INDEX in a DO/EXCEPTION
handler that silently skips when pg_bigm is unavailable. The bundled
self-host / dev / CI Postgres image (pgvector/pgvector:pg17) does not
ship pg_bigm, so on every self-hosted deployment the migrations no-op
and no GIN indexes get built. Every /api/issues/search + /api/projects/search
request then falls back to a Seq Scan on `issue` + correlated Seq Scans
on `comment` — verified with EXPLAIN on the local dev DB, which has zero
title/description/comment search indexes before this change.
Two independent guardrails are added, either of which alone would have
prevented the reported hang:
1) Migration 134 installs pg_trgm (ships in all standard Postgres +
pgvector images) and builds GIN indexes with gin_trgm_ops on
`LOWER(title)`, `LOWER(COALESCE(description, ''))`, and
`LOWER(content)`. The expression signatures match the search
handler's WHERE clauses exactly, so the planner picks the index
without further changes. The pg_bigm indexes from 036 are left
intact — deployments on AWS RDS with pg_bigm 1.2 keep the CJK-friendly
bigram path; deployments without it get the trigram fallback. Verified
against a local 25k-row fixture: the description LIKE hits
`Bitmap Index Scan on idx_issue_description_trgm` in 0.5 ms.
2) runSearchQuery wraps both search handlers in a short-lived read-only
transaction with SET LOCAL statement_timeout = 3 s. In the pathological
case where indexes are still missing or the query plan is bad, callers
see a fast 503 with a descriptive error instead of a stalled request.
Verified against a live Postgres: a deliberate pg_sleep(2) with the
test override at 200 ms is cut off in 230 ms with SQLSTATE 57014, as
asserted by TestRunSearchQuery_StatementTimeoutFires.
Non-goals: this change does not remove the pg_bigm code path, does not
change the SQL the handler builds, and does not change the API response
shape. It is the minimum diff to unblock production while preserving
the CJK-search advantage that pg_bigm provides where it is available.
Co-authored-by: multica-agent <github@multica.ai>
* fix(search): scope comment subqueries to workspace to unblock prd hang (MUL-4059)
Follow-up correction after PRD investigation: pg_bigm IS installed on
prd `multica-prod` and all five bigm indexes exist in the correct
`LOWER(...) gin_bigm_ops` form. The initial "missing index" hypothesis
was wrong; migration 134 (pg_trgm fallback) still helps self-host but
does not touch the production hang path.
Actual prd EXPLAIN (workspace with 60k issues, keyword "search"):
Index Scan using idx_issue_workspace on issue i
Rows Removed by Filter: 59123
SubPlan 2
Bitmap Heap Scan on comment c
Rows Removed by Index Recheck: 1928275
Heap Blocks: exact=48297 lossy=164696
Bitmap Index Scan on idx_comment_content_bigm
rows=536761
Execution Time: 32345.002 ms
Root cause: the correlated `EXISTS` over `comment` gets rewritten by
the planner into a *hashed* subplan. Without a workspace_id filter in
the subquery, that hashed set covers every comment in every workspace
matching the LIKE — 536k rows for "search" — which spills work_mem
into a lossy bitmap and rechecks 1.9M rows.
Two-part fix:
1. Query rewrite. buildSearchQuery now emits
`c.workspace_id = $wsParam` inside every comment subquery (WHERE
phrase match, WHERE multi-term match, tier 7 rank, tier 8 rank, and
the matched_comment_content COALESCE). The same $4 parameter is
reused so Postgres treats it as a compile-time constant and pushes
it into the hashed subplan's key, collapsing the set to this
workspace's comments.
2. Supporting index (migration 135). New
`idx_comment_workspace ON comment (workspace_id)`. Without it, the
pushed-down filter still triggers a Seq Scan on `comment` because
comment has no btree on workspace_id (only the FK constraint and
composite (issue_id, ...) indexes).
Locally verified against a repro that mirrors prd (5k issues in the
target workspace, 100k comments in a sibling workspace all containing
"search"): the plan drops from 60 ms (hashed global scan, no support
index) to 3 ms (subplan uses idx_comment_workspace). Prd extrapolation
from the same shape: 32.3 s → tens of milliseconds.
Regression test TestBuildSearchQuery_CommentSubqueryWorkspaceScope
asserts every `FROM comment c` in the generated SQL is followed by a
`c.workspace_id = $4` filter, so a future refactor can't silently
regress the plan back to the global-hash pathology.
The statement_timeout guard from the earlier commit in this branch is
kept — it still bounds the worst case if any future query shape
regresses.
Co-authored-by: multica-agent <github@multica.ai>
* fix(search): address PR review — unwrap 135 + add project trigram indexes (MUL-4059)
Both must-fix items from GPT-Boy's review:
1. Migration 135 unwrapped. The previous version buried
`CREATE INDEX idx_comment_workspace` inside `DO $$ ... EXCEPTION
WHEN OTHERS $$` — exactly the anti-pattern that caused MUL-4059 in
the first place. `idx_comment_workspace` is not a CJK-bonus fallback;
it is the critical support that makes the query rewrite land on an
Index Scan instead of a Seq Scan. A silent failure (lock timeout,
disk full, permission denied, schema drift) MUST abort the migration
and fail deployment, not slip through as green. The unwrapped
`CREATE INDEX IF NOT EXISTS` now propagates real errors to the
migration runner, which aborts and does NOT record the version as
applied. IF NOT EXISTS keeps idempotency for the operator-precreated
case (`CREATE INDEX CONCURRENTLY ...` before running migrations on
large prd tables).
2. Migration 134 now covers project search too. SearchProjects reads
`LOWER(project.title)` and `LOWER(COALESCE(project.description, ''))`,
and the pg_bigm equivalents in migration 039 silently no-op on
pg_bigm-less images just like 032/033/036. Without the trigram
fallback, project searches on self-host would still Seq Scan and hit
the 3 s statement_timeout guard as a 503 — technically bounded but
not actually fixed. Added `idx_project_title_trgm` and
`idx_project_description_trgm`; the down migration drops them too.
Also: fixed the search.go comment that said callers get a "standard
500" — they get a 503 with SQLSTATE-57014 mapping; the comment now
matches reality.
Verified: build clean, vet clean, existing search / timeout tests
still green. Migration 135 dry-run (dropping the index, re-applying
the unwrapped SQL under `ON_ERROR_STOP=1`) creates the index cleanly;
a deliberate `CREATE INDEX` on a non-existent column now aborts psql
with exit 3, confirming the migration runner would fail loudly on any
real error.
Co-authored-by: multica-agent <github@multica.ai>
---------
Co-authored-by: Eve <eve@multica-ai.local>
Co-authored-by: multica-agent <github@multica.ai>
Projects are now searchable alongside issues in the Cmd+K search dialog.
Results are grouped by type (Projects / Issues) with project icon, status,
and description snippet highlighting.
Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
* fix(search): use LOWER/LIKE instead of ILIKE for pg_bigm 1.2 compatibility
pg_bigm 1.2 on RDS does not support ILIKE index scans. Replace all
ILIKE expressions with LOWER(column) LIKE LOWER(pattern) so the GIN
indexes are utilized. Rebuild gin_bigm_ops indexes on LOWER() expressions.
Closes MUL-482
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
* fix(search): lowercase pattern in Go, add buildSearchQuery unit tests
- Lowercase phrase/terms in Go (strings.ToLower) so SQL only needs
LOWER() on the column side, avoiding redundant per-query LOWER() on
the pattern
- Add 5 unit tests for buildSearchQuery asserting SQL shape: no ILIKE,
LOWER on columns only, lowercased args, multi-term AND, number match,
include-closed flag, special char escaping
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
