Re: SELECT speed with LIKE - Mailing list pgsql-general

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