Re: Strange (?) Index behavior?

On Fri, 05 Nov 2004 14:57:40 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Allen Landsidel <alandsidel(at)gmail(dot)com> writes:
> > With that many rows, and a normal index on the field, postgres figures
> > the best option for say "I%" is not an index scan, but a sequential
> > scan on the table, with a filter -- quite obviously this is slow as
> > heck, and yes, I've run analyze several times and in fact have the
> > vacuum analyze automated.
> > With the partial index the index scan is used and the cost drops from
> > 0..2million to 0..9000 -- a vast improvement.
>
> Hmm. This suggests to me that you're using a non-C locale and so a
> plain index *can't* be used for a LIKE query. Can you force it to use
> an indexscan by setting enable_seqscan = false? If not then you've got
> a locale problem. As someone else pointed out, this can be worked
> around by creating an index with the right operator class.

Tom, disabling seqscan does cause it to use the index.

With seqscan enabled however, "AB%" will use the index, but "A%" will not.

The estimated cost for the query is much higher without the partial
indexes than it is with them, and the actual runtime of the query is
definitely longer without the partial indexes.

The locale is set in the postgresql.conf file as per default, with..

# These settings are initialized by initdb -- they may be changed
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

-Allen