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.

Setup

The examples run on any YugabyteDB universe.

Set up a local cluster

If a local universe is currently running, first destroy it.

Start a local one-node universe with an RF of 1 by first creating a single node, as follows:

./bin/yugabyted start \
                --advertise_address=127.0.0.1 \
                --base_dir=${HOME}/var/node1 \
                --cloud_location=aws.us-east-2.us-east-2a

After starting the yugabyted processes on all the nodes, configure the data placement constraint of the universe, as follows:

./bin/yugabyted configure data_placement --base_dir=${HOME}/var/node1 --fault_tolerance=zone

This command can be executed on any node where you already started YugabyteDB.

To check the status of a running multi-node universe, run the following command:

./bin/yugabyted status --base_dir=${HOME}/var/node1

Setup

To set up a universe, refer to Set up a YugabyteDB Anywhere universe.

Setup

To set up a cluster, refer to Set up a YugabyteDB Aeon cluster.

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.

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)

Create an expression index using the following command:

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

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 how covering indexes can optimize query performance by covering all the columns needed by a query.
Benefits of an Index-only scan

Expression indexes

Syntax

Setup

Explore covering indexes

Learn more