> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Syntax for CREATE INDEX ... USING, covering data skipping, inverted, full-text search, and vector indexes.

# CREATE INDEX

Creates a secondary index on an existing table. The index kind is selected by the `USING` clause.

This page is the syntax reference. For how each index works, when to use it, and how to tune it, follow the link next to each form into [Storage and indexing](/performance-and-observability/storage-and-indexing).

<Note>
  Aggregating indexes use a different grammar; see [`CREATE AGGREGATING INDEX`](/reference-sql/commands/data-definition/create-aggregating-index). To remove an index, see [`DROP INDEX`](/reference-sql/commands/data-definition/drop-index).
</Note>

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING <method>(<arguments>)
  [WITH (<parameters>)];
```

| Parameter                     | Description                                                                      |
| :---------------------------- | :------------------------------------------------------------------------------- |
| `<index_name>`                | Unique name for the index across all tables, indexes, and views in the database. |
| `<table_name>`                | The `FACT` or `DIMENSION` table to index. External tables are not supported.     |
| `<method>`                    | The index kind: `SKIP_INDEX`, `INVERTED_INDEX`, `FULL_TEXT`, or `HNSW`.          |
| `<arguments>`, `<parameters>` | Method-specific. See the forms below.                                            |

Index names are unique per database. You can create multiple indexes on one table, but at most one index per indexed expression.

## `SKIP_INDEX`: data skipping (minmax)

Skips [granules](/performance-and-observability/storage-and-indexing#granule) whose stored min/max range cannot match a comparison predicate. For mechanism and tuning, see [Data skipping index](/performance-and-observability/storage-and-indexing/data-skipping-index).

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX <index_name>
  ON <table_name>
  USING SKIP_INDEX(<expression>)
  WITH (TYPE = minmax, GRANULARITY = <granularity>);
```

| Parameter      | Description                                                                                                                                                                                                          |
| :------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<expression>` | A column or scalar expression of a comparable type (`INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, `DECIMAL`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`, `TEXT`, `BOOLEAN`, `BYTEA`). `ARRAY` and `STRUCT` are not supported. |
| `TYPE`         | The index type. Only `minmax` is supported. Required.                                                                                                                                                                |
| `GRANULARITY`  | Positive integer: how many consecutive data granules share one min/max summary. Required.                                                                                                                            |

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX dsi_amount ON sales
  USING SKIP_INDEX(amount) WITH (TYPE = minmax, GRANULARITY = 1);
```

## `INVERTED_INDEX`: exact value membership

Maps each value to the rows that contain it. Once the index exists, `=`, `IN`, and bounded range predicates over the column are matched automatically, with `has_all_tokens` available for `ARRAY(TEXT)` membership. For mechanism and tuning, see [Inverted index](/performance-and-observability/storage-and-indexing/inverted-index).

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING INVERTED_INDEX(<expression>);
```

| Parameter      | Description                                                                                                                                                                                                                      |
| :------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<expression>` | A column or scalar expression returning `TEXT`, `ARRAY(TEXT)`, or a scalar type (`BOOLEAN`, `INTEGER`, `BIGINT`, `NUMERIC`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`). For `ARRAY(TEXT)`, each element is indexed as a separate token. |

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX idx_tags ON events USING INVERTED_INDEX(tags);
```

## `FULL_TEXT`: substring search

Indexes overlapping n-grams to accelerate [`search`](/performance-and-observability/storage-and-indexing/full-text-search-index), which is equivalent to `LIKE '%pattern%'`. For mechanism and tuning, see [Full-text search index](/performance-and-observability/storage-and-indexing/full-text-search-index).

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING FULL_TEXT(<column>)
  WITH (ngram_size = <N>);
```

| Parameter    | Description                                                                                                       |
| :----------- | :---------------------------------------------------------------------------------------------------------------- |
| `<column>`   | A `TEXT` column. Arbitrary expressions are not supported.                                                         |
| `ngram_size` | Positive integer n-gram length. The shortest pattern that can use the index. Typical values are 2 to 4. Required. |

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX idx_title ON articles USING FULL_TEXT(title) WITH (ngram_size = 3);
```

## `HNSW`: vector search

Builds an HNSW graph for approximate nearest-neighbor search, queried with the [`vector_search()`](/reference-sql/functions-reference/vector/vector-search) TVF. The `HNSW` form has its own parameter set, documented on [`CREATE VECTOR SEARCH INDEX`](/reference-sql/commands/data-definition/create-vector-search-index). For mechanism and tuning, see [Vector search index](/performance-and-observability/storage-and-indexing/vector-search-index).

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE INDEX <index_name> ON <table_name> USING HNSW (
  <column_name> <distance_metric>
) WITH (
  dimension = <dimension> [, m = 16] [, ef_construction = 128] [, quantization = 'bf16']
);
```

## Index creation on populated tables

When you create an index on a table that already contains data, existing tablets carry no index data until they are rewritten. Run [`VACUUM`](/reference-sql/commands/data-management/vacuum) with `REINDEX=TRUE` to backfill them. All subsequent inserts populate the index automatically.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
VACUUM (REINDEX=TRUE) <table_name>;
```

## See also

* [Storage and indexing](/performance-and-observability/storage-and-indexing) describes how indexes fit into the scan pipeline.
* [`CREATE AGGREGATING INDEX`](/reference-sql/commands/data-definition/create-aggregating-index), [`CREATE VECTOR SEARCH INDEX`](/reference-sql/commands/data-definition/create-vector-search-index), and [`DROP INDEX`](/reference-sql/commands/data-definition/drop-index).
* [`information_schema.indexes`](/reference-sql/information-schema/indexes) lists indexes and their on-disk sizes.