From: Tom Lane <tgl@sss.pgh.pa.us>
To: Allen Landsidel <alandsidel@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange (?) Index behavior?
Date: Fri, 05 Nov 2004 14:57:40 -0500
Message-ID: <10898.1099684660@sss.pgh.pa.us> (raw)
In-Reply-To: <88f1825a041105095631064cb0@mail.gmail.com>
References: <88f1825a041105063955367088@mail.gmail.com>
<20041105175159.GB8855@wolff.to>
<88f1825a041105095631064cb0@mail.gmail.com>
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, alandsidel@gmail.com
Subject: Re: Strange (?) Index behavior?
In-Reply-To: <10898.1099684660@sss.pgh.pa.us>
* 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