Re: Lexicographic index ? - Mailing list pgsql-general

From Peter Gibbs
Subject Re: Lexicographic index ?
Date
Msg-id 070701c1fcd9$4312e8a0$0b01010a@emkel.co.za
Whole thread Raw
In response to Lexicographic index ?  (arnaud.mlist1@free.fr)
Responses Re: GiST, R-TREE, Lexicographic index ?
List pgsql-general
----- Original Message -----
From: <arnaud.mlist1@free.fr>
To: <pgsql-general@postgresql.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



pgsql-general by date:

Previous
From: jtv
Date:
Subject: Re: [INTERFACES] C & C ++Program Problem
Next
From: Serkan Bektaş (SoftHome)
Date:
Subject: Is there eny e-mail server that uses postgreSQL as backend server for mailboxes&users