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

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