Hi there, is there a way to use indexes with (i)like clauses? Should I
use the full text indexing feature?

http://www.postgresql.org/docs/8.3/s...rch-intro.html

Anselmo Canfora <nobody (AT) nowhere (DOT) net> wrote:
Sure, but (maybe) you have to create the index with text_pattern_ops:

test=*# \d words
Table "public.words"
Column | Type | Modifiers
--------+------+-----------
t | text |
Indexes:
"idx_words" btree (t text_pattern_ops)

test=*# explain select * from words where t like 'foo*';
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using idx_words on words (cost=0.00..8.28 rows=1 width=9)
Index Cond: (t = 'foo*'::text)
Filter: (t ~~ 'foo*'::text)
(3 rows)



Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Ok, thanks. For the specific case I am dealing now I decided to try an
alternate way since I do not need a full fledged FTS feature. Since the
like clause I use is always the same ('pattern%') I just created a
functional index with substring to shrink the resultset on which the
planner do the seqscan, the result is pretty satisfying, the execution
time dropped from 1 - 2 seconds to less than 100 milliseconds.

Il 01/09/2010 10.33, Andreas Kretschmer ha scritto: