Thread: 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.
<br /> Although I'm using a version for solaris that I built myelf, I found that<br /> my search on a table with 120,000rows with indexes didn't use<br /> the indexes ... I'm pretty sure I didn't compile with locale support<br /> (howdoes one check?)<br /><br /> I'm using 6.5.2, haven't bothered to upgrade since it's only a minor<br /> version and 7is almost out ... (sorry for the html ...)<br /><br /><font face="Courier New, Courier">engine=> \d word<br /> Table = word<br /> +----------------------------------+----------------------------------+-------+<br /> | Field | Type | Length|<br /> +----------------------------------+----------------------------------+-------+<br/> | id |varchar() not null | 255 |<br /> | lower_id | varchar() not null | 255 |<br /> | soundex | char() not null | 4 |<br /> +----------------------------------+----------------------------------+-------+<br/> Indices: idx_word_lower_id<br /> idx_word_soundex<br /> pkey_word<br /><br /> engine=> \d idx_word_lower_id<br /> Table = idx_word_lower_id<br/> +----------------------------------+----------------------------------+-------+<br /> | Field | Type | Length|<br /> +----------------------------------+----------------------------------+-------+<br/> | lower_id |varchar() | 255 |<br /> +----------------------------------+----------------------------------+-------+<br/> engine=> explain select * from wordwhere lower_id like 'cow%';<br /> NOTICE: QUERY PLAN:<br /><br /> Seq Scan on word (cost=5675.21 rows=1 width=36)<br/><br /><br /><br /><br /></font>At 09:50 PM 3/04/00 -0500, Robert W. Berger wrote:<br /> >I had the sameproblem with 6.5.3. It turns out that there is a "known"<br /> >(at least to the developers; I haven't seen it documentedanywhere) problem<br /> >in 6.5:<br /> >if your postgresql was compiled with Locale support on, index searchesof<br /> >the form<br /> >LIKE 'foo%' go very, very slow (much slower than deleting the index and<br /> >forcinga sequential search).<br /> ><br /> >The solution is to recompile postgresql with Locale off. Note thatI tried<br /> >to use the RPM that claims to be compiled this way, but it didn't help;<br /> >I had to recompilemyself from the source RPM. Once I did the search <br /> > on 340,000 rows went from 20 seconds to 0.1 seconds.<br/> ><br /> >7.0 supposedly fixes this, but I haven't tried it.<br /> > <br /><div>--</div><div>Mr Grumpyis now a virtual personality ...</div><div><a eudora="AUTOURL" href="http://www.cyber4.org/members/grumpy/camera/index.html">http://www.cyber4.org/members/grumpy/camera/index.html</a></div>
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.