From: Mark Kirkwood <markir@paradise.net.nz>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Like 'name%' is not using index
Date: Fri, 03 Mar 2006 14:28:40 +1300
Message-ID: <44079BC8.3030209@paradise.net.nz> (raw)
In-Reply-To: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>
References: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>
Jozsef Szalay wrote:
> Hi all,
>
>
>
> I have to provide a pretty standard query that should return every row
> where the NAME attribute begins with a specific string. The type of the
> NAME column is varchar. I do have an index for this column. One would
> think that Postgres will use the index to look up the matches, but
> apparently that is not the case. It performs a full table scan. My
> query looks something like this:
>
>
>
> SELECT * FROM table WHERE name LIKE ‘smith%’;
>
>
>
> Does anyone know a way to “force” the optimizer to utilize the index? Is
> there perhaps another way of doing this?
>
Can you provide an EXPLAIN ANALYZE for the query? This will give us a
hint as to why the index has not been chosen.
The other standard gotcha is that LIKE will not use an index if your
cluster is initialized with locale != C. If it is, then you can try
recreating the index using something like:
CREATE INDEX table_name ON table (name varchar_pattern_ops);
cheers
Mark
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: markir@paradise.net.nz, jszalay@storediq.com
Subject: Re: Like 'name%' is not using index
In-Reply-To: <44079BC8.3030209@paradise.net.nz>
* 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