Re: SELECT speed with LIKE

Although I'm using a version for solaris that I built myelf, I found that
my search on a table with 120,000 rows with indexes didn't use
the indexes ... I'm pretty sure I didn't compile with locale support
(how does one check?)

I'm using 6.5.2, haven't bothered to upgrade since it's only a minor
version and 7 is almost out ... (sorry for the html ...)

engine=> \d word
Table    = word
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | varchar() not null               |   255 |
| lower_id                         | varchar() not null               |   255 |
| soundex                          | char() not null                  |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  idx_word_lower_id
          idx_word_soundex
          pkey_word

engine=> \d idx_word_lower_id
Table    = idx_word_lower_id
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| lower_id                         | varchar()                        |   255 |
+----------------------------------+----------------------------------+-------+
engine=> explain select * from word where lower_id like 'cow%';
NOTICE:  QUERY PLAN:

Seq Scan on word  (cost=5675.21 rows=1 width=36)




At 09:50 PM 3/04/00 -0500, Robert W. Berger wrote:
>I had the same problem with 6.5.3. It turns out that there is a "known"
>(at least to the developers; I haven't seen it documented anywhere) problem
>in 6.5:
>if your postgresql was compiled with Locale support on, index searches of
>the form
>LIKE 'foo%' go very, very slow (much slower than deleting the index and
>forcing a sequential search).
>
>The solution is to recompile postgresql with Locale off. Note that I tried
>to use the RPM that claims to be compiled this way, but it didn't help;
>I had to recompile myself from the source RPM. Once I did the search
> on 340,000 rows went from 20 seconds to 0.1 seconds.
>
>7.0 supposedly fixes this, but I haven't tried it.
>