From: | JB <jimbag(at)kw(dot)igs(dot)net> |
---|---|
To: | "Robert W(dot) Berger" <rwb(at)vtiscan(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT speed with LIKE |
Date: | 2000-04-04 06:24:40 |
Message-ID: | 38E98AA8.AFF271D6@kw.igs.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Well, I did as you suggested and it seems to have worked. Thank you for
this. I've spent (wasted) about 3 weeks trying to sort this out,
thinking I'm a moron. I've been told everything from get a bigger
machine to the situation can't be the way I've described. I don't
understand the hackers list as I posted this problem there 3 weeks ago.
I just knew it wasn't right. Thanks again!
cheers
jimbo
"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.
From | Date | Subject | |
---|---|---|---|
Next Message | Balazs Gyetvai | 2000-04-04 06:57:02 | Create user prob. |
Previous Message | Gary Hoffman | 2000-04-04 04:03:44 | Fetching record of the day |