PostgreSQL: How we can create Index on Expression?

If you are searching about, What is Expression Index, this is the one of the right article for you.

We know about the different types of PostgreSQL Index.
If you don’t about this, please visit below few links.

What is BRIN (Block Range Index )?

What is Partial Index?

What is BTree Index?

Most of the Database Administrators or Developers are doing a common mistake,
When you create any Index on Table Column, dose not mean that you can use the Indexed column in any expression and Index will work as per the expectation.

When you used Indexed column in any kind of expression, query planner simply skips the scanning of Indexes of that column.

For example,
We have created one index on Date column and in WHERE are extracting days from this Date column.
In this situation, the Index will not work on Date column and for such specific requirement, we have to create the Expression Index of PostgreSQL.

Let me demonstrate this.

First create one sample table:

Generate sample data for testing the performance of Indexes:

Total inserted record count is 24969601:

Now create index on TransactionDate column:

Lets see the plan of query with date filter:

Check Index usage by above query
(Result is one index scan):

Lets see the plan of query with date function filter:

Check Index usage by above query:
The Result is one index scan, means above query processed without scaning any index.
We created one index on TransactionDate, but when we use this column with any of default function, planner skips the index fot that column.

Now we should create one expression Index:
Please provide your require timezone otherwise It produces error like:

Lets execute same query with date function filter:

Check created new Expression Index usage by above query
(Result is one index scan):

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of