Improve query performance with indexes

Indexes help ScopeDB skip segments and rows that cannot match a query. ScopeDB automatically maintains range, inverted, path, and materialized indexes for your columns, and lets you configure search indexes for token matching.

What ScopeDB indexes automatically

When you create a table or update its columns, ScopeDB automatically generates these index requirements without manual CREATE INDEX statements:

Index type	Applied to	What it accelerates
Range	Every ordinary and computed column	Range predicates (such as `time >= NOW() - '1h'::interval`) and segment min/max pruning.
Inverted	`int` and `string` columns (ordinary and computed)	Equality (`=`, `IN`) and distinct lookups.
Path	`object` and `any` columns	Filtering on nested paths inside semi-structured payloads, such as `var['gateway']::string = 'credit_card'`.
Materialized	Computed columns	Reading precomputed expression results without re-evaluating the expression on every scan.

New writes and background compaction build these indexes automatically. Queries against historical segments that do not yet have an index fall back to scanning the column data and still return complete results.

Start with the query

For example, this query filters by time, service, a nested gateway field inside var, and a token search on message:

FROM events
SELECT *, var['gateway']::string AS gateway
WHERE time >= NOW() - 'PT1h'::interval
  AND service = 'checkout'
  AND gateway = 'credit_card'
  AND search(message, 'timeout')
ORDER BY time DESC
LIMIT 100

In this query:

time uses its automatic Range index;
service uses its automatic Inverted and Range indexes;
var['gateway'] uses the automatic Path index on var (or can be promoted to a computed column);
search(message, 'timeout') benefits from an explicit Search index on message.

Add a search index in Catalog

To accelerate search(...) on a string, array, object, or any column, add {"type": "search"} to the column's indexes array in the table's Catalog definition (in Console Data > Edit table > JSON, with scope catalog, or over HTTP):

{
  "name": "events",
  "columns": [
    { "name": "time", "data_type": "timestamp" },
    { "name": "service", "data_type": "string" },
    { "name": "name", "data_type": "string" },
    {
      "name": "message",
      "data_type": "string",
      "indexes": [{ "type": "search", "analyzer": "log" }]
    },
    { "name": "var", "data_type": "object" }
  ]
}

analyzer defaults to "log" when omitted. If a query calls search(...) on a column without a Search index, ScopeDB still evaluates the search by scanning the column.

Promote a hot payload path with a computed column

An object or any column already has an automatic Path index. When one specific nested expression—such as var['gateway']::string or var['http']['url']::string—appears repeatedly across filters, groupings, or joins, define a computed column in the table's Catalog definition:

{ "name": "gateway", "data_type": "string", "expression": "var['gateway']" }

ScopeDB automatically attaches Materialized, Range, and Inverted indexes to the computed column and evaluates the expression for both historical and incoming rows.

SQL index statements

You can also create or drop indexes with CREATE INDEX and DROP INDEX:

CREATE SEARCH INDEX ON events (message)

CREATE POINT INDEX ON events (var['gateway']::string)

Prefer Catalog definitions and computed columns so your index configuration stays part of the declarative table schema.

Check whether an index helps

Compare the same representative query before and after adding a search index or computed column. Use the same time window and data, verify that results agree, and compare elapsed time and scanned bytes in the query progress details.

Add indexes