Re: Best way to use indexes for partial match at

On 11/11/05, Andrus <eetasoft(at)online(dot)ee> 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'
>
> or in
>
> WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255)
>
> or in
>
> WHERE SUBSTRING( bar FOR 3 )='ABC'
>
> is more simpler than dealing with locale problems in WHERE bar LIKE 'ABC%':
>
> Using LIKE with index optimization in Postgres in non-C locale requires
>
> a. creating non-SQL standard compatible index unsin operator class
>

are indexes in the SQL-STANDARD? i touhgt we can do with indexes what
we think is better... like other database has its own implementation
details about indexes...

> b. requires that primary key column has two indexes
>
> This is too much overhead.
>

it requires two indexes, yes... but one of them can be a normal
(accept duplicates) not unique one... just let the primary key create
its index and create the one you needs without the unique clause

> >Is it that unreasonable that a PRIMARY KEY should use the most natural
> > way to order strings for your locale
>
> This is very reasonable. PRIMARY KEY must use locale order always.
>
> > and that if you want to use LIKE
> > in non-C locales that you need to specify that explicitly?
>
> This is unreasonable.
>

Maybe, but is unfair for your side to said that without actually look
at the code and the problems... maybe, you want to look at the code
and fix what you think is wrong?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)