Re: Lexicographic index ?

----- Original Message -----
From: <arnaud(dot)mlist1(at)free(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>

> select * from twords where words||'%' like 'saxophones';
>
> works but uses a sequential scan on the table...

The only method I have been able to find that will use the index is to
provide both upper and lower limits on the key.

For example:

select * from twords
where words <= 'saxophones'
and words >= 's'
and position(words in 'saxophones') = 1;

This uses the index in my test, whereas it doesn't if you leave out the
second condition, even if you add an 'order by' clause.
Using position is slightly faster on my system than using likes (but I am
only using the standard /usr/dict/words for testing, so I only have 45402
rows.

--
Peter Gibbs
EmKel Systems