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.
>
--
Mr Grumpy is now a virtual personality ...