Re: BUG #1325: like error

On Thu, 18 Nov 2004, PostgreSQL Bugs List wrote:

> select * from tbinvoicerows
> where invoicecd = 'FLOCAA00000025'
>
> result in 0 seconde with 2 records NO BUG
>
> select * from tbinvoicerows
> where invoicecd like '%FLOCAA00000025%'
>
> result in 40 secondes with 2 records NO BUG
>
> select * from tbinvoicerows
> where invoicecd like 'FLOCAA00000025%'
>
> result in 40 secondes with 2 records BUG
> Bug : the result is good but I expect a result faster as the first select
> (like in mssql 7)

To add to the reply already given...

If you're running in "C" locale, the last should be able to use a normal
index on invoicecd. However, since that didn't appear to happen, either
it's estimating a large number of rows which is unlikely or you are in
some other locale.

For other locales, you may want a pattern_ops index, which would be
something like:
create index foo on tbinvoicerows(invoicecd varchar_pattern_ops).