Обсуждение: SELECT speed with LIKE

I've got a real problem with the speed of a select. Some folk might
recall a prev post about a month ago about this 50MB table taking 20+
seconds to complete a query. This table has about 70,000 records in it.
The table has a field called 'stname char(17)' which is indexed. The
query is "SELECT * FROM data WHERE stname LIKE 'MAIN%'". I'm running on
Redhat 6.1, 128MB ram, 40GB, P350. The actual index file is 4 MB.
EXPLAIN tells that it will use the index. The thing still takes about 20
seconds to complete. Can anyone make some suggestions on how I can speed
this up? It seems like such a simple problem I can't see the solution.
Any help would be most appreciated as this abosolutly has to be fixed
and I don't want to have to convert to another DB.

BTW I've run the same thing on a similar machine with 64 MB ram and it
take over a minute to complete. EXPLAIN says...

NOTICE: QUERY PLAN:
Index Scan using nx_data2 on data (cost=3352.28 rows=1 width=440)
EXPLAIN

jim

--
Get hold of portable property.  -- Charles Dickens, "Great Expectations"

> I've got a real problem with the speed of a select. Some folk might

> "SELECT * FROM data WHERE stname LIKE 'MAIN%'"

I once had a speed problem on mSQL and found if I only selected the
columns I needed, rather than use a wildcard to get them all, it ran much
faster.  Of course, this is a different database program and maybe you
need the data from every column in your application, but it's something to
try.....

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================

At 01:33 PM 4/01/00 -0500, you wrote:
>The table has a field called 'stname char(17)' which is indexed. The
>query is "SELECT * FROM data WHERE stname LIKE 'MAIN%'". I'm running on
>Redhat 6.1, 128MB ram, 40GB, P350. The actual index file is 4 MB.

I haven't tried it, but didn't someone mention a few weeks ago that  "WHERE
stname ~ '^MAIN'"  would produce the same results faster?

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.

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.

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.
>

On Tue, Apr 04, 2000 at 06:30:17PM +1000, Jim Richards wrote:
<nothing, because it was in html>

Boy, _that_ was hard to read. Not only HTML, but lots of   all
over. I _think_ Jim was concerned that selects on his big tables
where not using his indices. The canonical reply question is:

Have you run 'vacuum analyze' recently?

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

The locale problem only affects the speed once it decides to use an index;
it does not affect whether it chooses an index or sequential scan.

Try doing a VACUUM ANALYZE; that often makes it start using index scans.