Re: Strange (?) Index behavior?

Поиск
Список
Период
Сортировка
От Bruno Wolff III
Тема Re: Strange (?) Index behavior?
Дата
Msg-id 20041105175159.GB8855@wolff.to
обсуждение исходный текст
Ответ на Strange (?) Index behavior?  (Allen Landsidel <alandsidel@gmail.com>)
Ответы Re: Strange (?) Index behavior?  (Allen Landsidel <alandsidel@gmail.com>)
Список pgsql-performance
On Fri, Nov 05, 2004 at 09:39:16 -0500,
  Allen Landsidel <alandsidel@gmail.com> wrote:
>
> For some reason it's a requirement that partial wildcard searches are
> done on this field, such as "SELECT ... WHERE field LIKE 'A%'"
>
> I thought an interesting way to do this would be to simply create
> partial indexes for each letter on that field, and it works when the
> query matches the WHERE clause in the index exactly like above.  The
> problem is thus:

That may not help much except for prefixes that have a below average
number of occurences. If you are going to be select 1/26 of the records,
you are probably going to do about as well with a sequential scan as an
index scan.

Just having a normal index on the column will work if the database locale
is C. In 7.4 you can create an index usable by LIKE even in the database
locale isn't C, but I don't remember the exact syntax. You will be better
off having just one index rather than 26 partial indexes.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Allen Landsidel
Дата:
Сообщение: Re: Strange (?) Index behavior?
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: Checking = with timestamp field is slow