Re: Like 'name%' is not using index

Поиск
Список
Период
Сортировка
От Mark Kirkwood
Тема Re: Like 'name%' is not using index
Дата
Msg-id 44079BC8.3030209@paradise.net.nz
обсуждение исходный текст
Ответ на Like 'name%' is not using index  ("Jozsef Szalay" <jszalay@storediq.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Jozsef Szalay"
Дата:
Сообщение: Like 'name%' is not using index
Следующее
От: "Jozsef Szalay"
Дата:
Сообщение: Re: Like 'name%' is not using index