ALTER MATERIALIZED VIEW ADD INDEX

Adds an index to a SYMBOL column in a materialized view, improving query performance for filtered lookups.

Syntax

ALTER MATERIALIZED VIEW viewName ALTER COLUMN columnName ADD INDEX [ CAPACITY n ]

Parameter	Description
`viewName`	Name of the materialized view
`columnName`	Name of the `SYMBOL` column to index
`CAPACITY`	Optional index capacity (advanced; use default unless you understand implications)

Add an index when:

Queries frequently filter by a SYMBOL column (e.g., WHERE symbol = 'BTC-USD')
The column has high cardinality (many distinct values)
Query performance on the materialized view needs improvement

questdb-sql

ALTER MATERIALIZED VIEW trades_hourly
  ALTER COLUMN symbol ADD INDEX;

Aspect	Description
Operation type	Atomic, non-blocking, non-waiting
Immediate effect	SQL optimizer starts using the index once created
Column requirement	Column must be of type `SYMBOL`

:::note Index capacity and symbol capacity are different settings. Only change index capacity if you understand the implications. :::

Adding an index requires the ALTER MATERIALIZED VIEW permission:

questdb-sql

GRANT ALTER MATERIALIZED VIEW ON trades_hourly TO user1;

Error	Cause
`materialized view does not exist`	View with specified name doesn't exist
`column does not exist`	Column not found in the view
`column is not a symbol`	Index can only be added to `SYMBOL` columns
`index already exists`	Column is already indexed
`permission denied`	Missing `ALTER MATERIALIZED VIEW` permission (Enterprise)