Re: How to force Postgres to use index on ILIKE - Mailing list pgsql-performance

"Andrus" <eetasoft@online.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