From: Allen Landsidel <alandsidel@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Strange (?) Index behavior?
Date: Fri, 5 Nov 2004 15:36:28 -0500
Message-ID: <88f1825a0411051236497f6bbf@mail.gmail.com> (raw)
In-Reply-To: <10898.1099684660@sss.pgh.pa.us>
References: <88f1825a041105063955367088@mail.gmail.com>
<20041105175159.GB8855@wolff.to>
<88f1825a041105095631064cb0@mail.gmail.com>
<10898.1099684660@sss.pgh.pa.us>
On Fri, 05 Nov 2004 14:57:40 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Allen Landsidel <alandsidel@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alandsidel@gmail.com
Subject: Re: Strange (?) Index behavior?
In-Reply-To: <88f1825a0411051236497f6bbf@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox