Re: Index not used, performance problem

Andreas Kostyrka <andreas@mtg.co.at> writes:
> On Sat, 2003-03-29 at 14:47, Christopher Kings-Lynne wrote:
>> As for the LIKE searches, the only ones that PostgreSQL can index are of
>> the form 'FOO%', which is what you are doing.  However, I believe that
>> PostgreSQL cannot do this if your database encoding is anything other than
>> 'C'.  So, if you are using an Austrian encoding, it might not be able to
>> use the index.

> Well, I use LATIN1. How do I store 8-bit chars else?

You are both confusing locale with encoding.  The LIKE optimization
requires 'C' locale, but it should work with any encoding (or at least
any single-byte encoding; not sure about multibyte).

> And if so, PostgreSQL seems quite strongly broken, because a
> relational database relies by design heavily on indexes.

Some of us would reply that the locales are broken ;-).  The bizarre
sorting rules demanded by so many locales are what make it impossible
to optimize a LIKE prefix into an indexscan.  See the archives for
the reasons why our many tries at this have failed.

            regards, tom lane