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
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 |