CODEWITHSUNDEEP
postgresql
user10776719
user10776719

Reputation: 341

Partial index with function is not used with simple query

This sample table has 500 million sample rows and includes a id, first name, last name, email, phone number.

My index create:

CREATE INDEX ix_test_first_name_lower ON my_table(LOWER(first_name))
    WHERE first_name <> ''

My simple query: 

SELECT id
FROM my_table
WHERE lower(first_name) = 'joe'

The query plan indicates that this simple query is doing a sequential scan of the table and is not utilizing the index I created.

Why would that be the case? Can I not use a function in a partial index?

Edit:

Most important part I noticed. I created my index without the condition and noticed the same query then utilized the index. So it does appear to be related to the where clause in the index.

Upvotes: 3

Views: 44

Answers (1)

GMB
GMB

Reputation: 222582

The documentation says:

To be precise, a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index. PostgreSQL does not have a sophisticated theorem prover that can recognize mathematically equivalent expressions that are written in different forms. (Not only is such a general theorem prover extremely difficult to create, it would probably be too slow to be of any real use.) The system can recognize simple inequality implications, for example “x < 1” implies “x < 2”; otherwise the predicate condition must exactly match part of the query's WHERE condition or the index will not be recognized as usable.

In other words, Postgres is not that smart about identifying when the partial index can be used. You would need to let it know it can use the index in your query, by explicitly adding the index predicate to the query:

SELECT id
FROM my_table
WHERE lower(first_name) = 'joe' AND first_name <> ''

Upvotes: 4

Related Questions