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

Stateway automatically maintains a GIN index over the variables column. This index accelerates:

• Containment checks and key-existence checks used by most structured filters
• Filters on top-level variable fields with moderate result sets

You don't need to do anything to benefit from this index — it's part of every Stateway deployment.

Expression indexes for specific fields. For fields that appear frequently in filters and show Seq Scan in stats.scanMode, Stateway proactively creates expression indexes — B-tree indexes on extracted field paths such as variables.loan_status or variables.amount (with numeric cast for range comparisons). These are created based on telemetry patterns across tenant queries. If you observe persistent Seq Scan on a specific field at scale, that signal is already being captured by Stateway's monitoring.

Text search indexes. For mode: "fulltext" and mode: "trigram" queries on specific fields, specialized GIN indexes can be added for recurring access patterns. These are also created proactively based on observed usage.

---

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