Re: How to force Postgres to use index on ILIKE

"Andrus" <eetasoft(at)online(dot)ee> writes:
>> 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you
>> problems using an index, period.

> 1. I haven't seen any example where VARCHAR is better that CHAR for indexing

The advice you were given is good, even if the explanation is bad.
CHAR(n) is a poor choice for just about every purpose, because of all
the padding blanks it insists on storing and transmitting. That adds
up to a lot of wasted space, I/O effort, and CPU cycles.

> I tried by Postgres does not use index. Why ?
> create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops);

Try to get over this fixation on CHAR. That would work with
text_pattern_ops --- lower() returns TEXT, and TEXT is what the LIKE
operator accepts, so that's the opclass you need to use to optimize
lower() LIKE 'pattern'.

regards, tom lane