Expression indexes

Explore expression indexes in YugabyteDB using YSQL

Indexes are typically created based solely on the columns, but using an expression index (also called a function-based index) you can create an index based on a generic expression (function or modification of data entered) computed from table columns.

Syntax

CREATE INDEX index_name ON table_name( (expression) );

You can omit the parentheses around the expression where the expression is a simple function call.

Once defined, the index is used when the expression that defines the index is included in the WHERE or ORDER BY clause in the YSQL statement.

Example

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

A common use case of an expression index is to support case-insensitive text to enable efficient searchability.

For example, suppose you have a users table with an email column to store login email addresses, and you want to maintain case-insensitive authentication. Using the WHERE clause as WHERE LOWER(email) = '<lower_case_email>' allows you to store the email address as originally entered by the user.

The following example uses the employees table from the Secondary indexes example scenario to show how to create an index on an expression that converts the department to lowercase to improve searchability.

  1. Verify the query plan without creating an expression index for the department Operations.

    EXPLAIN SELECT * FROM employees
  WHERE LOWER(department) = 'operations';

                            QUERY PLAN
---------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..105.00 rows=1000 width=68)
  Filter: (lower(department) = 'operations'::text)
(2 rows)

  2. Create an expression index using the following command:

    CREATE INDEX index_employees_department_lc
  ON employees(LOWER(department));

  3. Run the EXPLAIN statement again to verify that the index_employees_department_lc index is used to find the department regardless of case:

    EXPLAIN SELECT * FROM employees
  WHERE LOWER(department) = 'operations';

                                            QUERY PLAN
------------------------------------------------------------------------------------------------
 Index Scan using index_employees_department_lc on employees  (cost=0.00..5.25 rows=10 width=68)
  Index Cond: (lower(department) = 'operations'::text)

Explore covering indexes

Learn more