How to force Postgres to use index on ILIKE

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: How to force Postgres to use index on ILIKE
Date: 2006-06-05 19:29:11
Message-ID: e620mg$15jr$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Sullivan 2006-06-05 19:42:52 Re: Some queries starting to hang
Previous Message Chris Beecroft 2006-06-05 19:05:08 Some queries starting to hang