Re: postgres index on ILIKE

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