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
