Received: from double.nlr.ru (double.nlr.ru [212.193.3.3]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA08302 for ; Thu, 18 May 2000 12:22:04 -0400 (EDT) (envelope-from gav@nlr.ru) Received: from GURYANOW ([172.16.2.2]) by double.nlr.ru (8.9.3/8.9.3) with ESMTP id UAA21168 for ; Thu, 18 May 2000 20:22:03 +0400 Date: Thu, 18 May 2000 20:22:01 +0400 From: Alex Guryanow X-Mailer: The Bat! (v1.34a) UNREG / CD5BF9353B3B7091 Reply-To: gav X-Priority: 3 (Normal) Message-ID: <8848.000518@nlr.ru> To: pgsql-sql@postgresql.org Subject: indecies are not used by '<=' operator on varchar fields Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/57 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