From: Josh Berkus <josh@agliodbs.com>
To: Alex Kretschmer <a_kretschmer@gmx.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: postgres index on ILIKE
Date: Mon, 29 Sep 2003 10:02:14 -0700
Message-ID: <200309291002.14823.josh@agliodbs.com> (raw)
In-Reply-To: <3F7824A8.2010704@gmx.de>
References: <3F7824A8.2010704@gmx.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, a_kretschmer@gmx.de
Subject: Re: postgres index on ILIKE
In-Reply-To: <200309291002.14823.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox