Expression Indexes
Indexes usually are created based on the columns, but the Expression Indexes(also called Function-based Indexes) let you create an index based on a generic expression(function or modification of data entered) involving the table columns.
Syntax
CREATE INDEX index_name ON table_name( (expression) );
The parenthesis around the expression can be omitted in cases where the expression involves a function.
When the index is defined, it is used when the expression that defines the index is included in the WHERE or ORDER BY clause in the YSQL statement.
Example
A common use case of an expression index is to support case-insensitive text to enable efficient searchability.
For example, let's say there's a users table with an email column to store their email addresses for signing in, but the user want's to maintain authentication in a case-insensitive manner. This can be done using the WHERE clause as WHERE LOWER(email) = '<lower_case_email>', but storing the email address as originally entered by the user.
The following example uses the employees table from Create indexes 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)
(2 rows)
- Create an expression index using the following command:
CREATE INDEX index_employees_department_lc
ON employees(LOWER(department));
- Run the
EXPLAINstatement again to verify that theindex_employees_department_lcindex is used to find the department regardless of it's 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)