Re: Best way to use indexes for partial match at beginning

From: Jaime Casanova <systemguards(at)gmail(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 beginning
Date: 2005-11-09 21:15:32
Message-ID: c2d9e70e0511091315x715c9749k3ca5d316bc5417a3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/9/05, Andrus <eetasoft(at)online(dot)ee> wrote:
> > you can create two indexes:
> >
> > CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops);
> > and
> > CREATE INDEX myindex_normal ON foo(col1);
> >
> > the first one will be used when using LIKE and the other for normal
> > comparisons .
>
> Jaime,
>
> CREATE INDEX myindex_normal ON foo(col1);
>
> Creates btree structure. In other dbm system btree structure can be used for
> searches where only some first characters in index key are known.
>

and the same is true for postgres when you use C LOCALE, but because
some implementation details i don't know so deep when using non-C
LOCALE you need the class operator in order to use the index with LIKE
'pattern%'

> So I see no reason to create second index using text_pattern_ops for this
> purpose.
>

the reason is that you want to use the index in the search... and, at
least you go and solve the problem with code, that's the way to do
it...

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message rm_pg 2005-11-09 21:20:27 set-valued function difference in 8.1.0 vs 8.0.2
Previous Message Martijn van Oosterhout 2005-11-09 21:08:10 Re: Best way to use indexes for partial match at beginning