Thread: How to force Postgres to use index on ILIKE

I have UTF-8 Postgres 8.1 database on W2K3

Query

SELECT toode, nimetus
FROM toode
WHERE toode      ILIKE  'x10%' ESCAPE '!'
ORDER BY UPPER(toode     ),nimetus  LIMIT 100

runs 1 minute in first time for small table size.

Toode field type is CHAR(20)

How to create index on toode field so that query can use  it ?

Andrus,

> SELECT toode, nimetus
> FROM toode
> WHERE toode      ILIKE  'x10%' ESCAPE '!'
> ORDER BY UPPER(toode     ),nimetus  LIMIT 100
>
> runs 1 minute in first time for small table size.
>
> Toode field type is CHAR(20)

1) why are you using CHAR and not VARCHAR or TEXT?   CHAR will give you
problems using an index, period.

2) You can't use an index on ILIKE.  You can, however, use an index on
lower(field) if your query is properly phrased and if you've created an
expression index on lower(field).

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

>> SELECT toode, nimetus
>> FROM toode
>> WHERE toode      ILIKE  'x10%' ESCAPE '!'
>> ORDER BY UPPER(toode     ),nimetus  LIMIT 100
>>
>> runs 1 minute in first time for small table size.
>>
>> Toode field type is CHAR(20)
>
> 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
2. I have a lot of existing code. Changing CHAR to VARCHAR requires probably
re-writing a lot of code, a huge work.

> 2) You can't use an index on ILIKE.

I'ts very sad. I expected that lower(toode) index can be used.


> You can, however, use an index on
> lower(field) if your query is properly phrased and if you've created an
> expression index on lower(field).

I tried by Postgres does not use index. Why ?

create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops);

explain analyze select nimi from firma1.klient where lower(nimi) like
'mokter%'

"Seq Scan on klient  (cost=0.00..9.79 rows=1 width=74) (actual
time=0.740..0.761 rows=1 loops=1)"
"  Filter: (lower((nimi)::text) ~~ 'mokter%'::text)"
"Total runtime: 0.877 ms"

"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