PostgreSQL Function Index

In today’s article, we will learn how to use functional indexes in PostgreSQL to obtain fast access to data based on the results of function calls.

For example, a common way to do case-insensitive comparisons is to use the lower function:

Let’s continue with the indextabled1 table we created in my article Single-Column and Multi-Column Indexes.

I want to bring the ones with the capital F letter in column2 in the indextabled1 table and the record numbered 11774 in column2.

When we query our table without creating an index as above, it performs seqscan.

Let’s create a function index as below and see the method used by the query plan.

Indexes that use functions such as lower and upper while creating an index are called Function Indexes.