CREATE INDEX (ScopeQL statement)

Creates an index on a column or expression to speed up matching queries.

Syntax

CREATE <index_type> INDEX [ IF NOT EXISTS ] [ <name> ] ON <table_name> (<index_expression>)
[ COMMENT = '<string_literal>' ]
[ WITH ( '<property_name>' = <literal> [, ...] ) ]

Parameters

`<index_type>`

Choose the type that matches the query operation:

Type	Use for
`POINT`	Equality predicates such as `=` and `IS`, and array membership with `contains`. On `object` and `any`, it also supports filtering on nested paths.
`RANGE`	Range conditions, such as a time interval or latency threshold.
`PATTERN`	String predicates such as `contains`, `starts_with`, `ends_with`, and supported `regexp_like` patterns.
`SEARCH`	Token matching with `search`.
`MATERIALIZED`	Reusing a stored expression result, such as an extracted payload field.

`<name>`

An optional index name, unique within the table. If omitted, ScopeDB generates a name from the table and expression. Use an explicit name when you want to refer to the index later.

`<table_name>` and `<index_expression>`

The table and the column or expression to index. Expressions can include typed field access, such as payload['order_id']::string.

Examples

Create a table for request events:

CREATE TABLE indexed_requests (
    event_id string,
    event_time timestamp,
    message string,
    payload object
)

Index event IDs for equality lookups:

CREATE POINT INDEX idx_event_id ON indexed_requests (event_id)

Index event time for range filters:

CREATE RANGE INDEX idx_event_time ON indexed_requests (event_time)

Index messages for token searches:

CREATE SEARCH INDEX idx_message ON indexed_requests (message)
WITH ('analyzer' = 'log')

Index a frequently filtered field inside the payload:

CREATE POINT INDEX idx_order_id ON indexed_requests (payload['order_id']::string)

Usage notes

ScopeDB chooses which available indexes to use. Indexes require additional storage and work during writes, so add them for queries you run frequently. See the indexing guide for choosing and inspecting indexes.
Index definitions must reference columns by name rather than positional references such as $0.

CREATE INDEX