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:
|
1 |
SELECT * FROM test1 WHERE lower(col1) = 'value'; |
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.
|
1 |
CREATE INDEX IX_5 ON indextabled1(kolon1 ,lower(kolon2)) ; |
Indexes that use functions such as lower and upper while creating an index are called Function Indexes.
