PostgreSQL Expression Index

Summary: in this tutorial, you’ll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression, rather than on column data.

PostgreSQL expression index overview #

When creating an index, you specify one or more columns of a table to include in the index. PostgreSQL will extract values from these columns for creating the index. When querying data based on the indexed columns, PostgreSQL uses the index for fast lookup.

You can create an expression index based on the results of an expression that involves table columns rather than the columns’ data. When you create an expression index, PostgreSQL evaluates the expression and uses the results for indexing.

When you query data from the table using expression, PostgreSQL will utilize the expression index to improve the performance.

To create an expression index, you use the following form of the CREATE INDEX statement:

CREATE INDEX [index_name]
ON table_name(expression);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the index after the CREATE INDEX keywords. The index name is optional.
• Second, provide the name of the table you want to create an index on.
• Third, define an expression for creating the index.

Creating an expression index based on the result of a function #

We’ll use the products table from the inventory database:

First, create an index for the product_name column of the products table:

CREATE INDEX ON products (product_name);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, find the products with the name 'apple iphone 15':

EXPLAIN
SELECT
  product_name,
  price
FROM
  products
WHERE
  LOWER(product_name) = 'apple iphone 15';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This query does not utilize the index products_product_name_idx.

Third, create an expression index for the products table:

CREATE INDEX ON products (LOWER(product_name));Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This statement creates a new index with the name products_lower_idx.

Finally, run the query that finds products using the LOWER() function:

EXPLAIN
SELECT
  product_name,
  price
FROM
  products
WHERE
  LOWER(product_name) = 'apple iphone 15';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=4.32..15.09 rows=5 width=17)
   Recheck Cond: (lower((product_name)::text) = 'apple iphone 15'::text)
   ->  Bitmap Index Scan on products_lower_idx  (cost=0.00..4.31 rows=5 width=0)
         Index Cond: (lower((product_name)::text) = 'apple iphone 15'::text)
(4 rows)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the query uses the expression index to search for the product.

Creating an expression index based on the result of an expression #

First, create an expression index based on the values of safety stocks:

CREATE INDEX ON products ((price * safety_stock));Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This statement creates an expression index with the name products_expr_idx.

Note that you have to place the expression within parentheses. So, there are two parentheses after the table name ((expression)).

Second, query the products by the safety stock values:

EXPLAIN
SELECT
  product_name,
  price,
  safety_stock
FROM
  products
WHERE
  price * safety_stock > 1000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=10.93..29.91 rows=342 width=21)
   Recheck Cond: ((price * (safety_stock)::numeric) > '1000'::numeric)
   ->  Bitmap Index Scan on products_expr_idx  (cost=0.00..10.84 rows=342 width=0)
         Index Cond: ((price * (safety_stock)::numeric) > '1000'::numeric)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the query uses the expression index.

When to use expression indexes #

In practice, you find the expression indexes useful in the following cases:

• Case-sensitive searches.
• Filtering and sorting rows based on an expression.
• Filtering rows based on parts of a date or time.
• Filtering JSON data using the JSONB_EXTRACT_PATH function.
• Filtering array data using the ARRAY_LENGTH function.

Summary #

• Use expression indexes to optimize queries that involve expressions.

Quiz #