Skip site navigation (1) Skip section navigation (2)

Re: Index not used, performance problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andreas Kostyrka <andreas(at)mtg(dot)co(dot)at>
Cc: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Index not used, performance problem
Date: 2003-03-29 23:13:28
Message-ID: 935.1048979608@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Andreas Kostyrka <andreas(at)mtg(dot)co(dot)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


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2003-03-29 23:25:43
Subject: Re: Bad perfomance of pl/pgsql-function on new server
Previous:From: Wil PetersDate: 2003-03-29 21:17:26
Subject: Bad perfomance of pl/pgsql-function on new server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group