Re: cant get an index scan with a LIKE - Mailing list pgsql-performance

That worked great - THANKS!

CREATE INDEX sct_descriptions_k2
  ON sct_descriptions
  USING btree
  (term_index  varchar_pattern_ops);

I noticed I had to keep the original index for the non-like operator
but that is not a big deal

CREATE INDEX sct_descriptions_k1
  ON sct_descriptions
  USING btree
  (term_index );


thanks again

Greg


On Mon, Oct 6, 2008 at 7:59 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>>>> "Greg Caulton" <caultonpos@gmail.com> wrote:
>
>> but I get a sequential scan when I do where term_index like
>>
>> select * from sct_descriptions where term_index like 'CHILLS AND
> FEVER
>> (FINDING)'
>
>> Is there anything else I can do?  Settings below, this is PostgreSQL
> 8.3
>
>> "lc_collate";"English_United States.1252"
>> "lc_ctype";"English_United States.1252"
>> "lc_messages";"English_United States"
>> "lc_monetary";"English_United States"
>> "lc_numeric";"English_United States"
>> "lc_time";"English_United States"
>
> This issue is discussed here:
>
> http://www.postgresql.org/docs/8.2/interactive/locale.html
>
> with a solution to your specific problem mentioned here:
>
> http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html
>
> You can create an index with the appropriate operator type to get LIKE
> to work as you want.  I hope this helps.
>
> -Kevin
>