Skip to main content
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.
Aggregating indexes use a different grammar; see CREATE AGGREGATING INDEX. To remove an index, see DROP INDEX.

​
Syntax

CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING <method>(<arguments>)
  [WITH (<parameters>)];
ParameterDescription
<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 whose stored min/max range cannot match a comparison predicate. For mechanism and tuning, see Data skipping index. 
CREATE INDEX <index_name>
  ON <table_name>
  USING SKIP_INDEX(<expression>)
  WITH (TYPE = minmax, GRANULARITY = <granularity>);
ParameterDescription
<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.
TYPEThe index type. Only minmax is supported. Required.
GRANULARITYPositive integer: how many consecutive data granules share one min/max summary. Required.
CREATE INDEX dsi_amount ON sales
  USING SKIP_INDEX(amount) WITH (TYPE = minmax, GRANULARITY = 1);

​
INVERTED_INDEX: exact token membership

Maps each token to the rows that contain it, accelerating has_all_tokens. For mechanism and tuning, see Inverted index. 
CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING INVERTED_INDEX(<expression>);
ParameterDescription
<expression>A column or scalar expression returning TEXT or ARRAY(TEXT). For ARRAY(TEXT), each element is indexed as a separate token.
CREATE INDEX idx_tags ON events USING INVERTED_INDEX(tags);

​
FULL_TEXT: substring search

Indexes overlapping n-grams to accelerate search, which is equivalent to LIKE '%pattern%'. For mechanism and tuning, see Full-text search index. 
CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING FULL_TEXT(<column>)
  WITH (ngram_size = <N>);
ParameterDescription
<column>A TEXT column. Arbitrary expressions are not supported.
ngram_sizePositive integer n-gram length. The shortest pattern that can use the index. Typical values are 2 to 4. Required.
CREATE INDEX idx_title ON articles USING FULL_TEXT(title) WITH (ngram_size = 3);
Builds an HNSW graph for approximate nearest-neighbor search, queried with the vector_search() TVF. The HNSW form has its own parameter set, documented on CREATE VECTOR SEARCH INDEX. For mechanism and tuning, see Vector search index. 
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 with REINDEX=TRUE to backfill them. All subsequent inserts populate the index automatically. 
VACUUM (REINDEX=TRUE) <table_name>;

​
See also