pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: postgres index on ILIKE
2+ messages / 1 participants
[nested] [flat]
* Re: postgres index on ILIKE
@ 2003-09-29 17:02 Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2003-09-29 17:02 UTC (permalink / raw)
To: Alex Kretschmer <a_kretschmer@gmx.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: postgres index on ILIKE
@ 2003-09-29 17:09 Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2003-09-29 17:09 UTC (permalink / raw)
To: Alex Kretschmer <a_kretschmer@gmx.de>; +Cc: pgsql-sql
Alex,
> mydb=# explain select filename from neighbors where filename ilike
> '%test%'; QUERY PLAN
> -------------------------------------------------------------
> Seq Scan on neighbors (cost=0.00..17449.64 rows=774 width=37)
> Filter: ((filename)::text ~~* '%test%'::text)
I just looked at your query. What you have above is an "unanchored text
search", where you are looking for a snippet of text anywhere in the field.
Such a search *cannot* be indexed using a regular index.
Instead, you need to employ the TSearch full text searching module for
PostgreSQL.
hmmm ... the Tsearch home page appears to be down. Check out the readme in
your postgreSQL source code: PG_SOURCE/contrib/tsearch/readme
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-09-29 17:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 17:02 ` Josh Berkus <josh@agliodbs.com>
2003-09-29 17:09 ` Josh Berkus <josh@agliodbs.com>
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