Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Alex Kretschmer <a_kretschmer@gmx.de>
Subject: Re: postgres index on ILIKE
Date: Mon, 29 Sep 2003 10:02:14 -0700
User-Agent: KMail/1.4.3
References: <3F7824A8.2010704@gmx.de>
In-Reply-To: <3F7824A8.2010704@gmx.de>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200309291002.14823.josh@agliodbs.com>

Alex,

> I read your article about indexing in postgres which unfortunately ends
> after the 2nd part. So I decided to ask you by email.

Yeah, yeah, I know.   The completed version will probably become part of a 
published book.  We'll see.

> Is it somehow possible to create an index for the ILIKE comparision?
> I set up a database which contains the paths to all files in my network
> neighborghood.

Short of creating your own datatype, you can't index for ILIKE.   

Instead, you create an index on the LOWER() of the column ...

CREATE INDEX idx_table_lower_text ON table(lower(text_field));

Then, you make sure when querying to query the lower function:

SELECT * FROM table
WHERE lower(text_field) LIKE 'xxxyy%';

This will use the index wherever it improves execution.

I suggest that you join the PGSQL-SQL mailing list for future questions of 
this type.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco