> ## 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.

# Expression Indexes

An *expression index* is an index created by applying an [expression](https://docs.cockroachlabs.com/docs/v26.4/scalar-expressions.md) to a column. For example, to facilitate fast, case insensitive lookups of user names you could create an index by applying the function `lower` to the `name` column: `CREATE INDEX users_name_idx ON users (lower(name))`. The value of the expression is stored only in the expression index, not in the primary family index.

Both [standard indexes](https://docs.cockroachlabs.com/docs/v26.4/create-index.md) and [GIN indexes](https://docs.cockroachlabs.com/docs/v26.4/inverted-indexes.md) support expressions. You can use expressions in [unique indexes](https://docs.cockroachlabs.com/docs/v26.4/create-index.md#unique-indexes) and [partial indexes](https://docs.cockroachlabs.com/docs/v26.4/partial-indexes.md).

You can reference multiple columns in an expression index.

## Create an expression index

To create an expression index, use the syntax:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX index_name ON table_name (expression(column_name));
```

## View index expression

To view the expression used to generate the index, run `SHOW CREATE TABLE`:

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

```
 table_name |                                  create_statement
-------------+--------------------------------------------------------------------------------------
  users      | CREATE TABLE public.users (
...
             |     INDEX users_name_idx (lower(name:::STRING) ASC),
...
             | )
(1 row)
```

## Examples

### Create various expression indexes

Suppose you have a table with the following columns:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE t (i INT, b BOOL, s STRING, j JSON);
```

The following examples illustrate how to create various types of expression indexes.

A partial, multi-column index, where one column is defined with an expression:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX ON t (lower(s), b) WHERE i > 0;
```

A unique, partial, multi-column index, where one column is defined with an expression:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE UNIQUE INDEX ON t (lower(s), b) WHERE i > 0;
```

A GIN, partial, multi-column index, where one column is defined with an expression:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INVERTED INDEX ON t (lower(s), i, j) WHERE b;
```

### Use an expression to index a field in a `JSONB` column

You can use an expression in an index definition to index a field in a JSON column. You can also use an expression to create a [GIN index](https://docs.cockroachlabs.com/docs/v26.4/inverted-indexes.md) on a subset of the JSON column.

Normally an index is used only if the cost of using the index is less than the cost of a full table scan. To disable that optimization, turn off statistics collection:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
```

Create a table of three users with a JSON object in the `user_profile` column:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users (
  profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  last_updated TIMESTAMP DEFAULT now(),
  user_profile JSONB
);

> INSERT INTO users (user_profile) VALUES
  ('{"id": "d78236", "firstName": "Arthur", "lastName": "Read", "birthdate": "2010-01-25", "school": "PVPHS", "credits": 120, "sports": ["none"], "clubs": ["Robotics"]}'),
  ('{"id": "f98112", "firstName": "Buster", "lastName": "Bunny", "birthdate": "2011-11-07",  "school": "THS", "credits": 67, "sports": ["Gymnastics"], "clubs": ["Theater"]}'),
  ('{"id": "t63512", "firstName": "Jane", "lastName": "Narayan", "birthdate": "2012-12-12", "school" : "Brooklyn Tech", "credits": 98, "sports": ["Track and Field"], "clubs": ["Chess"]}');
```

When you perform a query that filters on the `user_profile->'birthdate'` column:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> EXPLAIN SELECT jsonb_pretty(user_profile) FROM users WHERE user_profile->>'birthdate' = '2011-11-07';
```

You can see that a full scan is performed:

```
                            info
-------------------------------------------------------------
  distribution: local
  vectorized: true

  • render
  │
  └── • filter
      │ filter: (user_profile->>'birthdate') = '2011-11-07'
      │
      └── • scan
            missing stats
            table: users@users_pkey
            spans: FULL SCAN
(12 rows)

Time: 2ms total (execution 1ms / network 0ms)
```

To limit the number of rows scanned, create an expression index on the `birthdate` field:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE INDEX timestamp_idx ON users (parse_timestamp(user_profile->>'birthdate'));
```

When you filter on the expression `parse_timestamp(user_profile->'birthdate')`, only the row matching the filter is scanned:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> EXPLAIN SELECT jsonb_pretty(user_profile) FROM users WHERE parse_timestamp(user_profile->>'birthdate') = '2011-11-07';
```

```
                                 info
----------------------------------------------------------------------
  distribution: local
  vectorized: true

  • render
  │
  └── • index join
      │ table: users@users_pkey
      │
      └── • scan
            missing stats
            table: users@timestamp_idx
            spans: [/'2011-11-07 00:00:00' - /'2011-11-07 00:00:00']
(12 rows)

Time: 2ms total (execution 2ms / network 0ms)
```

As shown in this example, for an expression index to be used to service a query, the query must constrain the **same exact expression** in its filter.

## Known limitations

Expression indexes have the following limitations:

* The expression cannot reference columns outside the index's table.
* Functional expression output must be determined by the input arguments. For example, you can't use the [volatile function](https://docs.cockroachlabs.com/docs/v26.4/functions-and-operators.md#function-volatility) `now()` to create an index because its output depends on more than just the function arguments.
* CockroachDB does not allow  expression indexes  [expression indexes](https://docs.cockroachlabs.com/docs/v26.4/expression-indexes.md)  to reference [computed columns](https://docs.cockroachlabs.com/docs/v26.4/computed-columns.md).
* CockroachDB does not support expressions as `ON CONFLICT` targets. This means that unique  expression indexes  [expression indexes](https://docs.cockroachlabs.com/docs/v26.4/expression-indexes.md)  cannot be selected as arbiters for [`INSERT .. ON CONFLICT`](https://docs.cockroachlabs.com/docs/v26.4/insert#on-conflict-clause) statements. For example:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE TABLE t (a INT, b INT, UNIQUE INDEX ((a + b)));
  ```

  ```
  CREATE TABLE
  ```

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO NOTHING;
  ```

  ```
  invalid syntax: statement ignored: at or near "(": syntax error
  SQLSTATE: 42601
  DETAIL: source SQL:
  INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO NOTHING
                                      ^
  HINT: try \h INSERT
  ```

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO UPDATE SET a = 10;
  ```

  ```
  invalid syntax: statement ignored: at or near "(": syntax error
  SQLSTATE: 42601
  DETAIL: source SQL:
  INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO UPDATE SET a = 10
                                      ^
  HINT: try \h INSERT
  ```

## See also

* [Computed Columns](https://docs.cockroachlabs.com/docs/v26.4/computed-columns.md)
* [`CREATE INDEX`](https://docs.cockroachlabs.com/docs/v26.4/create-index)
* [`DROP INDEX`](https://docs.cockroachlabs.com/docs/v26.4/drop-index)
* [`ALTER INDEX ... RENAME TO`](https://docs.cockroachlabs.com/docs/v26.4/alter-index#rename-to)
* [`SHOW INDEX`](https://docs.cockroachlabs.com/docs/v26.4/show-index)
* [Indexes](https://docs.cockroachlabs.com/docs/v26.4/indexes.md)
* [SQL Statements](https://docs.cockroachlabs.com/docs/v26.4/sql-statements.md)
