Search Performance & Indexes

Understanding stats.scanMode

Every search response includes a stats object:

{
  "stats": {
    "returned": 12,
    "limit": 50,
    "scanMode": "Index Scan",
    "textMode": null,
    "estimatedComplexity": "low"
  }
}

scanMode tells you how PostgreSQL executed the query's main scan:

Value	Meaning	Performance
Index Scan	Used a B-tree or GIN index	Fast — scales well
Index Only Scan	Index covered all needed columns	Fastest
Bitmap Heap Scan	Used GIN index with heap fetch	Fast for moderate result sets
Seq Scan	Full table scan — no usable index	Slow at scale

Seq Scan on a filtered variable field means your query is scanning every row. At small data volumes this is unnoticeable, but it degrades linearly as your instance count grows.

---

Built-in Index Coverage

Every Stateway deployment ships a GIN index over the variables column (idx_instances_variables_gin, migration 0013). It uses jsonb_path_ops, so it accelerates containment comparisons such as variables @> '{"loan_status": "in_review"}'.

The structured filters of the search API do not compile to @>. A filter like { "variables.loan_status": "in_review" } becomes variables #>> '{loan_status}' = $1 — a per-row text comparison that this GIN index cannot serve. That is why a variable-field filter reports Seq Scan in stats.scanMode as your instance count grows. scanMode is computed per query (from EXPLAIN) and returned in the response; per-query metrics are also written to the service logs (search.executed, and search.slow_query above the slow-query threshold).

Expression indexes for specific fields — manual, not automatic. Stateway does not create expression indexes on its own: there is no background job, telemetry-driven or otherwise, that inspects your queries and adds indexes. If a field shows a persistent Seq Scan at scale, create the B-tree index yourself, matching the exact expression the query builder emits:

-- Equality / $ne / $in / $regex on a top-level variable field
CREATE INDEX idx_instances_loan_status
  ON process_instances ((variables #>> '{loan_status}'));

-- Nested field path
CREATE INDEX idx_instances_customer_country
  ON process_instances ((variables #>> '{customer,country}'));

-- Range comparisons ($gt/$gte/$lt/$lte): the builder casts the extracted text
-- to numeric, so the index must cover the cast expression
CREATE INDEX idx_instances_amount_numeric
  ON process_instances (((variables #>> '{amount}')::numeric));

The planner only uses an expression index when the expression matches literally: variables ->> 'loan_status' and variables #>> '{loan_status}' are different expressions, so the arrow form will not serve the queries the API generates.

Text search indexes — also manual. For recurring mode: "fulltext" or mode: "trigram" queries on specific fields, add the corresponding GIN index yourself, again matching the expression built by src/modules/search/textSearch.ts (for example to_tsvector('portuguese', COALESCE(variables #>> '{notes}', '')), wrapped in unaccent(...) when the unaccent extension is enabled, or COALESCE(variables #>> '{applicant,name}', '') with gin_trgm_ops for trigram).

---

Monitoring Query Complexity

The stats.estimatedComplexity field (low / medium / high) reflects how many boolean operators are in your filter. Complex filters that combine many $and/$or branches may still use indexes for each sub-condition but can be slower to evaluate. If a complex filter is slow, consider:

1. Checking scanMode — a Seq Scan on any sub-condition is the first signal
2. Narrowing the statusScope (avoid all when active is sufficient)
3. Adding a definitionKey to pre-filter by process type before applying variable filters