Re: SELECT speed with LIKE

From: Jim Richards <grumpy(at)cyber4(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: SELECT speed with LIKE
Date: 2000-04-04 08:30:17
Message-ID: 200004040834.EAA91772@hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<html>
<br>
Although I'm using a version for solaris that I built myelf, I found
that<br>
my search on a table with 120,000 rows with indexes didn't use<br>
the indexes ... I'm pretty sure I didn't compile with locale
support<br>
(how does one check?)<br>
<br>
I'm using 6.5.2, haven't bothered to upgrade since it's only a
minor<br>
version and 7 is almost out ... (sorry for the html ...)<br>
<br>
<font face="Courier New, Courier">engine=&gt; \d word<br>
Table&nbsp;&nbsp;&nbsp; = word<br>
+----------------------------------+----------------------------------+-------+<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Field&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| Length|<br>
+----------------------------------+----------------------------------+-------+<br>
|
id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| varchar() not
null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; 255 |<br>
|
lower_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| varchar() not
null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; 255 |<br>
|
soundex&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| char() not
null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp; 4 |<br>
+----------------------------------+----------------------------------+-------+<br>
Indices:&nbsp; idx_word_lower_id<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
idx_word_soundex<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pkey_word<br>
<br>
engine=&gt; \d idx_word_lower_id<br>
Table&nbsp;&nbsp;&nbsp; = idx_word_lower_id<br>
+----------------------------------+----------------------------------+-------+<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Field&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| Length|<br>
+----------------------------------+----------------------------------+-------+<br>
|
lower_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|
varchar()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; 255 |<br>
+----------------------------------+----------------------------------+-------+<br>
engine=&gt; explain select * from word where lower_id like 'cow%';<br>
NOTICE:&nbsp; QUERY PLAN:<br>
<br>
Seq Scan on word&nbsp; (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>
&gt;I had the same problem with 6.5.3. It turns out that there is a
&quot;known&quot;<br>
&gt;(at least to the developers; I haven't seen it documented anywhere)
problem<br>
&gt;in 6.5:<br>
&gt;if your postgresql was compiled with Locale support on, index
searches of<br>
&gt;the form<br>
&gt;LIKE 'foo%' go very, very slow (much slower than deleting the index
and<br>
&gt;forcing a sequential search).<br>
&gt;<br>
&gt;The solution is to recompile postgresql with Locale off. Note that I
tried<br>
&gt;to use the RPM that claims to be compiled this way, but it didn't
help;<br>
&gt;I had to recompile myself from the source RPM. Once I did the search
<br>
&gt; on 340,000 rows went from 20 seconds to 0.1 seconds.<br>
&gt;<br>
&gt;7.0 supposedly fixes this, but I haven't tried it.<br>
&gt; <br>
<div>--</div>
<div>Mr Grumpy is now a virtual personality ...</div>
<div>
<a href="http://www.cyber4.org/members/grumpy/camera/index.html" EUDORA=AUTOURL>http://www.cyber4.org/members/grumpy/camera/index.html</a></div>
</html>

Attachment Content-Type Size
unknown_filename text/html 4.4 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew C.R. Martin 2000-04-04 09:04:20 Re: [ANNOUNCE] PostgreSQL book completed though chapter 10
Previous Message Wim Ceulemans 2000-04-04 07:41:43 Re: word search