Postgres search using lower() index

Question

I have a Postgres table containing column with stings in upper and lower case characters.

Table: company
id| name    | department
------------------------
1 | LM Corp | Repair
2 | BMG Inc | Maintenance
3 | DFR LLC | shipping

Using pgAdmin I created an index on the column as follows:

CREATE INDEX companydepartment_index
ON public.company (lower(department) ASC NULLS LAST);

When I do a query where input string is lowercase 'repair' then I don't get a match.

select company.id from company where company.department = lower($1));

Why isn't Postgres using the lowercase index to match the lowercase string?

I don't want to use: ...where lower(company.department) = lower($1)), because it defeats the purpose of having a lowercase index.

klin · Accepted Answer · 2018-04-16 20:26:05Z

4

I don't want to use: ...where lower(company.department) = lower($1)), because it defeats the purpose of having a lowercase index.

On the contrary, the index will only be used if you use exactly the same expression in the query as in the index. See Indexes on Expressions in the documentation.

answered Apr 16, 2018 at 20:26

klin

123k15 gold badges241 silver badges263 bronze badges

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

3 Comments

klin Over a year ago

No, in this case the planner knows that it can use the index. No convertions are done, it just search in the index. In case of department = $1 it has no option other than seq scan over the table.

Jimski Over a year ago

@ Klim But when if I use ...where lower(company.department) = lower($1)), this is not exactly the same expression because index also contains ASC NULLS LAST. Does expression have to be exactly the same to use the index?

klin Over a year ago

ASC NULLS LAST is not a part of the index expression, see the syntax: Create Index.

Gordon Linoff · Accepted Answer · 2018-04-16 20:27:09Z

2

The expression:

where lower(company.department) = lower($1)),

does not defeat the purpose of having a lower-case index. It is exactly the reason why you want to have one. What is important for using the index is the expression lower(company.department), because this is the expression used to build the index.

answered Apr 16, 2018 at 20:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres search using lower() index

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related