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

# Expression Indexes

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

An *expression index* is an index created by applying an <InternalLink path="scalar-expressions">expression</InternalLink> 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 <InternalLink path="create-index">standard indexes</InternalLink> and <InternalLink path="inverted-indexes">GIN indexes</InternalLink> support expressions. You can use expressions in <InternalLink path="create-index#unique-indexes">unique indexes</InternalLink> and <InternalLink path="partial-indexes">partial indexes</InternalLink>.

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 <InternalLink path="inverted-indexes">GIN index</InternalLink> 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 <InternalLink path="functions-and-operators#function-volatility">volatile function</InternalLink> `now()` to create an index because its output depends on more than just the function arguments.
* CockroachDB does not allow  expression indexes  <InternalLink path="expression-indexes">expression indexes</InternalLink>  to reference <InternalLink path="computed-columns">computed columns</InternalLink>.
* CockroachDB does not support expressions as `ON CONFLICT` targets. This means that unique  expression indexes  <InternalLink path="expression-indexes">expression indexes</InternalLink>  cannot be selected as arbiters for <InternalLink path="insert#on-conflict-clause">`INSERT .. ON CONFLICT`</InternalLink> 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

* <InternalLink path="computed-columns">Computed Columns</InternalLink>
* <InternalLink path="create-index">`CREATE INDEX`</InternalLink>
* <InternalLink path="drop-index">`DROP INDEX`</InternalLink>
* <InternalLink path="alter-index#rename-to">`ALTER INDEX ... RENAME TO`</InternalLink>
* <InternalLink path="show-index">`SHOW INDEX`</InternalLink>
* <InternalLink path="indexes">Indexes</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>