Re: Strange (?) Index behavior?

On Fri, Nov 05, 2004 at 09:39:16 -0500,
Allen Landsidel <alandsidel(at)gmail(dot)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.