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

From: Alex Guryanow <gav(at)nlr(dot)ru>
To: pgsql-sql(at)postgresql(dot)org
Subject: indecies are not used by '<=' operator on varchar fields
Date: 2000-05-18 16:22:01
Message-ID: 8848.000518@nlr.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2000-05-18 16:35:17 Re: LIKE and regex
Previous Message Mitch Vincent 2000-05-18 15:49:17 LIKE and regex