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

# CREATE INDEX

The `CREATE INDEX` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) creates an index for a table. [Indexes](https://docs.cockroachlabs.com/docs/v24.2/indexes) improve your database's performance by helping SQL locate data without having to look through every row of a table.

Indexes are automatically created for a table's [`PRIMARY KEY`](https://docs.cockroachlabs.com/docs/v24.2/primary-key) and [`UNIQUE`](https://docs.cockroachlabs.com/docs/v24.2/unique) columns. When querying a table, CockroachDB uses the fastest index. For more information about that process, see [Index Selection in CockroachDB](https://www.cockroachlabs.com/blog/index-selection-cockroachdb-2/).

The following types cannot be included in an index key, but can be stored (and used in a covered query) using the [`STORING` or `COVERING`](https://docs.cockroachlabs.com/docs/v24.2/create-index#store-columns) clause:

* [`JSONB`](https://docs.cockroachlabs.com/docs/v24.2/jsonb)
* [`ARRAY`](https://docs.cockroachlabs.com/docs/v24.2/array)
* The computed [`TUPLE`](https://docs.cockroachlabs.com/docs/v24.2/scalar-expressions#tuple-constructors) type, even if it is constructed from indexed fields

To create an index on the schemaless data in a [`JSONB`](https://docs.cockroachlabs.com/docs/v24.2/jsonb) column or on the data in an [`ARRAY`](https://docs.cockroachlabs.com/docs/v24.2/array), use a [GIN index](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes).

> [!NOTE]
>
> The `CREATE INDEX` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes).

## Required privileges

The user must have the `CREATE` [privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) on the table.

## Synopsis

### Standard index

![create\_index syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/create_index.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=2ae5a5fa6d757a564e55c5cca6ec6d3d)

### GIN index

![create\_inverted\_index syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/create_inverted_index.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=658f1ebeb12091b67ac06de314994600)

## Parameters

| Parameter | Description |
| - | - |
| `UNIQUE` | Apply the [`UNIQUE` constraint](https://docs.cockroachlabs.com/docs/v24.2/unique) to the indexed columns. This causes the system to check for existing duplicate values on index creation. It also applies the `UNIQUE` constraint at the table level, so the system checks for duplicate values when inserting or updating data. |
| `INVERTED` | Create a [GIN index](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes) on the schemaless data in the specified [`JSONB`](https://docs.cockroachlabs.com/docs/v24.2/jsonb) column.  You can also use the PostgreSQL-compatible syntax `USING GIN`. For more details, see [GIN Indexes](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes#creation). |
| `IF NOT EXISTS` | Create a new index only if an index of the same name does not already exist; if one does exist, do not return an error. |
| `opt_index_name` `index_name` | The name of the index to create, which must be unique to its table and follow these [identifier rules](https://docs.cockroachlabs.com/docs/v24.2/keywords-and-identifiers#identifiers). If you do not specify a name, CockroachDB uses the format `_<columns_key/idx`. `key` indicates the index applies the `UNIQUE` constraint; `idx` indicates it does not. Example: `accounts_balance_idx` |
| `table_name` | The name of the table you want to create the index on. |
| `USING name` | An optional clause for compatibility with third-party tools. Accepted values for `name` are `btree`, `gin`, and `gist`, with `btree` for a standard secondary index, `gin` as the PostgreSQL-compatible syntax for a [GIN index](#create-gin-indexes), and `gist` for a [spatial index](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes). |
| `name` | The name of the column you want to index. For [multi-region tables](https://docs.cockroachlabs.com/docs/v24.2/multiregion-overview#table-localities), you can use the `crdb_region` column within the index in the event the original index may contain non-unique entries across multiple, unique regions. |
| `ASC` or `DESC` | Sort the column in ascending (`ASC`) or descending (`DESC`) order in the index. How columns are sorted affects query results, particularly when using `LIMIT`. **Default:** `ASC` |
| `STORING ...` | Store (but do not sort) each column whose name you include. For information on when to use `STORING`, see  [Store Columns](#store-columns).  Note that columns that are part of a table's [`PRIMARY KEY`](https://docs.cockroachlabs.com/docs/v24.2/primary-key) cannot be specified as `STORING` columns in secondary indexes on the table. `COVERING` and `INCLUDE` are aliases for `STORING` and work identically. |
| `opt_partition_by` | An option that lets you [define index partitions at the row level](https://docs.cockroachlabs.com/docs/v24.2/partitioning). As of CockroachDB v21.1 and later, most users should use [`REGIONAL BY ROW` tables](https://docs.cockroachlabs.com/docs/v24.2/table-localities#regional-by-row-tables). Indexes against regional by row tables are automatically partitioned, so explicit index partitioning is not required. |
| `opt_where_clause` | An optional `WHERE` clause that defines the predicate boolean expression of a [partial index](https://docs.cockroachlabs.com/docs/v24.2/partial-indexes). |
| `opt_index_visible` | An optional `VISIBLE`, `NOT VISIBLE`, or `VISIBILITY` clause that indicates that an [index is visible, not visible, or partially visible to the cost-based optimizer](https://docs.cockroachlabs.com/docs/v24.2/cost-based-optimizer#control-whether-the-optimizer-uses-an-index). If not visible, the index will not be used in queries unless it is specifically selected with an [index hint](https://docs.cockroachlabs.com/docs/v24.2/indexes#selection) or the property is overridden with the [`optimizer_use_not_visible_indexes` session variable](https://docs.cockroachlabs.com/docs/v24.2/set-vars#optimizer-use-not-visible-indexes). For examples, see [Set index visibility](https://docs.cockroachlabs.com/docs/v24.2/alter-index#set-index-visibility). Indexes that are not visible are still used to enforce `UNIQUE` and `FOREIGN KEY` [constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints). For more considerations, see [Index visibility considerations](https://docs.cockroachlabs.com/docs/v24.2/alter-index#not-visible). |
| `USING HASH` | Creates a [hash-sharded index](https://docs.cockroachlabs.com/docs/v24.2/hash-sharded-indexes). |
| `WITH storage_parameter` | A comma-separated list of [spatial index tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#index-tuning-parameters). Supported parameters include `fillfactor`, `s2_max_level`, `s2_level_mod`, `s2_max_cells`, `geometry_min_x`, `geometry_max_x`, `geometry_min_y`, and `geometry_max_y`. The `fillfactor` parameter is a no-op, allowed for PostgreSQL-compatibility. For details, see [Spatial index tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#index-tuning-parameters). For an example, see [Create a spatial index that uses all of the tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#create-a-spatial-index-that-uses-all-of-the-tuning-parameters). |
| `CONCURRENTLY` | Optional, no-op syntax for PostgreSQL compatibility. All indexes are created concurrently in CockroachDB. |

## Viewing schema changes

This schema change statement is registered as a job.  You can view long-running jobs with [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs).

## Examples

#### Setup

To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/v24.2/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/v24.2/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

### Create standard indexes

To create the most efficient indexes, we recommend reviewing:

* [Indexes: Best Practices](https://docs.cockroachlabs.com/docs/v24.2/indexes#best-practices)
* [Index Selection in CockroachDB](https://www.cockroachlabs.com/blog/index-selection-cockroachdb-2/)

#### Single-column indexes

Single-column indexes sort the values of a single column.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON users (name);
```

Because each query can only use one index, single-column indexes are not typically as useful as multiple-column indexes.

#### Multiple-column indexes

Multiple-column indexes sort columns in the order you list them.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON users (name, city);
```

To create the most useful multiple-column indexes, we recommend reviewing our [best practices](https://docs.cockroachlabs.com/docs/v24.2/schema-design-indexes#best-practices).

#### Unique indexes

Unique indexes do not allow duplicate values among their columns.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE UNIQUE INDEX ON users (name, id);
```

This also applies the [`UNIQUE` constraint](https://docs.cockroachlabs.com/docs/v24.2/unique) at the table level, similar to [`ALTER TABLE`](https://docs.cockroachlabs.com/docs/v24.2/alter-table). The preceding example is equivalent to:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE users ADD CONSTRAINT users_name_id_key UNIQUE (name, id);
```

Primary key columns that are not specified within a unique index are automatically marked as [`STORING`](https://docs.cockroachlabs.com/docs/v24.2/indexes#storing-columns) in the [`information_schema.statistics`](https://docs.cockroachlabs.com/docs/v24.2/information-schema#statistics) table and in [`SHOW INDEX`](https://docs.cockroachlabs.com/docs/v24.2/show-index).

### Create GIN indexes

You can create [GIN indexes](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes) on schemaless data in a [`JSONB`](https://docs.cockroachlabs.com/docs/v24.2/jsonb) column.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON promo_codes USING GIN (rules);
```

The following syntax is equivalent:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INVERTED INDEX ON promo_codes (rules);
```

### Create trigram indexes

You can create [trigram indexes](https://docs.cockroachlabs.com/docs/v24.2/trigram-indexes) on `STRING` columns by specifying the `gin_trgm_ops` or `gist_trgm_ops` opclass:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX ON rides USING GIN (vehicle_city gin_trgm_ops);
```

The following syntax is equivalent:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INVERTED INDEX ON rides(vehicle_city gin_trgm_ops);
```

> [!NOTE]
>
> GIN and GiST indexes are implemented identically on CockroachDB. `GIN` and `GIST` are therefore synonymous when defining a trigram index.

### Create spatial indexes

You can create [spatial indexes](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes) on `GEOMETRY` and `GEOGRAPHY` columns.  Spatial indexes are a special type of [GIN index](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes).

To create a spatial index on a `GEOMETRY` column:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX geom_idx_1 ON some_spatial_table USING GIST(geom);
```

Unlike GIN indexes, spatial indexes do not support an alternate `CREATE INVERTED INDEX ...` syntax.  Only the syntax shown here is supported.

For advanced users, there are a number of [spatial index tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#create-a-spatial-index-that-uses-all-of-the-tuning-parameters) that can be passed in using the syntax `WITH (var1=val1, var2=val2)` as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX geom_idx_2
  ON some_spatial_table USING GIST(geom)
  WITH (s2_max_cells = 20, s2_max_level = 12, s2_level_mod = 3);
```

> [!CAUTION]
>
> Most users should not change the default spatial index settings. There is a risk that you will get worse performance by changing the default settings. For more information , see [Spatial indexes](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes).

### Store columns

Storing a column improves the performance of queries that retrieve (but do not filter) its values.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON users (city) STORING (name);
```

However, to use stored columns, queries must filter another column in the same index. For example, SQL can retrieve `name` values from the above index only when a query's `WHERE` clause filters `city`.

An index that stores all the columns needed by a query is also known as a *covering index* for that query.  When a query has a covering index, CockroachDB can use that index directly instead of doing an "index join" with the primary index, which is likely to be slower.

### Change column sort order

To sort columns in descending order, you must explicitly set the option when creating the index. (Ascending order is the default.)

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON users (city DESC, name);
```

How a column is ordered in the index will affect the ordering of the index keys, and may affect the efficiency of queries that include an `ORDER BY` clause.

### Query specific indexes

Normally, CockroachDB selects the index that it calculates will scan the fewest rows. However, you can override that selection and specify the name of the index you want to use. To find the name, use [`SHOW INDEX`](https://docs.cockroachlabs.com/docs/v24.2/show-index).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW INDEX FROM users;
```

```
  table_name |   index_name        | non_unique | seq_in_index | column_name | direction | storing | implicit
+------------+---------------------+------------+--------------+-------------+-----------+---------+----------+
  users      | users_pkey          |   false    |            1 | city        | ASC       |  false  |  false
  users      | users_pkey          |   false    |            2 | id          | ASC       |  false  |  false
  users      | users_pkey          |   false    |            3 | name        | N/A       |  true   |  false
  users      | users_pkey          |   false    |            4 | address     | N/A       |  true   |  false
  users      | users_pkey          |   false    |            5 | credit_card | N/A       |  true   |  false
  users      | users_city_name_idx |    true    |            1 | city        | DESC      |  false  |  false
  users      | users_city_name_idx |    true    |            2 | name        | ASC       |  false  |  false
  users      | users_city_name_idx |    true    |            3 | id          | ASC       |  false  |   true
(8 rows)

```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT name FROM users@users_name_idx WHERE city='new york';
```

```
        name
+------------------+
  Catherine Nelson
  Devin Jordan
  James Hamilton
  Judy White
  Robert Murphy
(5 rows)
```

You can use the `@primary` alias to use the table's primary key in your query if no secondary index explicitly named `primary` exists on that table.

### Create a hash-sharded secondary index

We [discourage indexing on sequential keys](https://docs.cockroachlabs.com/docs/v24.2/schema-design-indexes#best-practices). If a table **must** be indexed on sequential keys, use [hash-sharded indexes](https://docs.cockroachlabs.com/docs/v24.2/hash-sharded-indexes). Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range [hot spots](https://docs.cockroachlabs.com/docs/v24.2/performance-best-practices-overview#hot-spots) and improving write performance on sequentially-keyed indexes at a small cost to read performance.

Let's assume the `events` table already exists:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE events (
    product_id INT8,
    owner UUID,
    serial_number VARCHAR,
    event_id UUID,
    ts TIMESTAMP,
    data JSONB,
    PRIMARY KEY (product_id, owner, serial_number, ts, event_id)
);
```

You can create a hash-sharded index on an existing table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX ON events(ts) USING HASH;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW INDEX FROM events;
```

```
  table_name |  index_name   | non_unique | seq_in_index |        column_name        | direction | storing | implicit
-------------+---------------+------------+--------------+---------------------------+-----------+---------+-----------
  events     | events_pkey   |   false    |            1 | product_id                | ASC       |  false  |  false
  events     | events_pkey   |   false    |            2 | owner                     | ASC       |  false  |  false
  events     | events_pkey   |   false    |            3 | serial_number             | ASC       |  false  |  false
  events     | events_pkey   |   false    |            4 | ts                        | ASC       |  false  |  false
  events     | events_pkey   |   false    |            5 | event_id                  | ASC       |  false  |  false
  events     | events_pkey   |   false    |            6 | data                      | N/A       |  true   |  false
  events     | events_ts_idx |    true    |            1 | crdb_internal_ts_shard_16 | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            2 | ts                        | ASC       |  false  |  false
  events     | events_ts_idx |    true    |            3 | product_id                | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            4 | owner                     | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            5 | serial_number             | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            6 | event_id                  | ASC       |  false  |   true
(12 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM events;
```

```
         column_name        | data_type | is_nullable | column_default |               generation_expression               |           indices           | is_hidden
----------------------------+-----------+-------------+----------------+---------------------------------------------------+-----------------------------+------------
  product_id                | INT8      |    false    | NULL           |                                                   | {events_pkey,events_ts_idx} |   false
  owner                     | UUID      |    false    | NULL           |                                                   | {events_pkey,events_ts_idx} |   false
  serial_number             | VARCHAR   |    false    | NULL           |                                                   | {events_pkey,events_ts_idx} |   false
  event_id                  | UUID      |    false    | NULL           |                                                   | {events_pkey,events_ts_idx} |   false
  ts                        | TIMESTAMP |    false    | NULL           |                                                   | {events_pkey,events_ts_idx} |   false
  data                      | JSONB     |    true     | NULL           |                                                   | {events_pkey}               |   false
  crdb_internal_ts_shard_16 | INT8      |    false    | NULL           | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx}             |   true
(7 rows)
```

## See also

* [Indexes](https://docs.cockroachlabs.com/docs/v24.2/indexes)
* [`SHOW INDEX`](https://docs.cockroachlabs.com/docs/v24.2/show-index)
* [`DROP INDEX`](https://docs.cockroachlabs.com/docs/v24.2/drop-index)
* [`ALTER INDEX ... RENAME TO`](https://docs.cockroachlabs.com/docs/v24.2/alter-index#rename-to)
* [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs)
* [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements)
* [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)