Re: Best way to use indexes for partial match at

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Andrus <eetasoft(at)online(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Best way to use indexes for partial match at
Date: 2005-11-11 15:26:59
Message-ID: 20051111072537.W1473@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, 11 Nov 2005, Andrus wrote:

> >> SELECT * FROM foo WHERE bar::CHAR(3)='ABC';
>
> > Your query is the same as using LIKE, so why not express it that way?
>
> I want simply to select by first 3 characters. LIKE is too powerful and
> unnessecary. LIKE requires
> escaping % and ? characters in pattern.
>
> I expected that making planner to use primary key index in case of
>
> WHERE bar::CHAR(3)='ABC'
> WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255)
> WHERE SUBSTRING( bar FOR 3 )='ABC'

Which doesn't necessarily work because of how some collations may work.
The first 3 characters of the string may sort to a fundamentally different
place than the first 4 characters. You can have a collation where
'ABC' < 'ABCR' < 'ABCT' < 'ABD' < 'ABZ' < 'ABCS'. In the above, the ABCS
value shortens so it should be found, however it's sort position in the
index is outside the normal ABC range. AFAIK the interfaces being used
don't give enough information to detect such locales to handle them
differently.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Johnny Ljunggren 2005-11-11 15:31:31 Choosing PostgreSQL as the database for our next project
Previous Message Jaime Casanova 2005-11-11 15:26:13 Re: Best way to use indexes for partial match at