Using functions in postgres query skips the index. Any solution to use functions with index applied

Question

For eg:

If lower function is removed, the query performance is better than using lower. Using lower skips the index. Is

SELECT X.article_number, X.online_description
FROM articles X
WHERE LOWER(X.online_description) % usp_get_search_terms ('coke');

Unrelated to your problem, but: Postgres 9.3 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Jun 3, 2020 at 7:06

Laurenz Albe · Accepted Answer · 2020-06-03 07:00:50Z

1

An index can only be used to speed up a WHERE condition if the condition is of the following form:

<indexed expression> <operator> <constant>

Here <operator> must be an operator in the operator class of the index, and <constant> need not really be a constant, but fixed for the duration of the index scan.

I assume that % is the similarity operator from the pg_trgm extension. Then you need either a GIN index with the gin_trgm_ops operator class or a GiST index with the gist_trgm_ops operator class.

Since trigram operations are case insensitive anyway, you should remover the lower() from the query. Otherwise, you'd have to create the index on lower(online_description), which would be a waste of CPU.

answered Jun 3, 2020 at 7:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3085717 Over a year ago

lower(online_description) = <constant> , any alternative to use this without indexing the lower(online_description)

Laurenz Albe Over a year ago

No way. It cannot be done. Fix your query or fix the index.

Collectives™ on Stack Overflow

Using functions in postgres query skips the index. Any solution to use functions with index applied

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related