indecies are not used by '<=' operator on varchar fields

Поиск
Список
Период
Сортировка
От Alex Guryanow
Тема indecies are not used by '<=' operator on varchar fields
Дата
Msg-id 8848.000518@nlr.ru
обсуждение исходный текст
Список pgsql-sql
Hi,

postgresql 7.0, the table with a field of type varchar:
   CREATE TABLE bookmarks (id serial, label varchar);

with an index on label-field:
    CREATE INDEX bm_label_idx ON bookmarks (label);

If I want to select all rows, where field label begins with say 'alex' then I use the query
  SELECT id, label FROM bookmarks WHERE label LIKE 'alex%';

If I want find all rows that are "less" than 'alex' I use the query
  SELECT id, label FROM bookmarks WHERE label < 'alex';

But why by executing the first query postmaster uses the index bm_label_idx and by executing the
second don't? Here is as example:

my-db=$ explain select * from bookmarks where label like 'alex%';
NOTICE:  QUERY PLAN:

Index Scan using bm_label_idx2 on bookmarks  (cost=0.00..2.52 rows=1 width=24)

EXPLAIN
my-db=$ explain select * from bookmarks where label <= 'alex';
NOTICE:  QUERY PLAN:

Seq Scan on bookmarks  (cost=0.00..1488.62 rows=54959 width=24)

EXPLAIN
my-db=$

Best regards,
Alex




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

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Re[2]: lower() for varchar data by creating an index
Следующее
От: Tom Lane
Дата:
Сообщение: Re: LIKE and regex