Обсуждение: Like 'name%' is not using index

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

The var_char_pattern_ops operator group has made the difference.

Thanks for the help!
Jozsef

-----Original Message-----
From: Mark Kirkwood [mailto:markir@paradise.net.nz]
Sent: Thursday, March 02, 2006 7:29 PM
To: Jozsef Szalay
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Like 'name%' is not using index

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

"Jozsef Szalay" <jszalay@storediq.com> writes:

> 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%';

There are two possible answers here:

First, what does this output on your database?

db=> show lc_collate;

If it's not "C" then the index can't be used. You would have to make a second
special-purpose index specifically for use with LIKE.

Secondly, please send "explain analyze" output for your query. It will show if
the optimizer is simply estimating that the index won't help enough to be
faster than the full table scan.

--
greg