pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Guryanow <gav@nlr.ru>
To: pgsql-sql@postgresql.org
Subject: indecies are not used by '<=' operator on varchar fields
Date: Thu, 18 May 2000 20:22:01 +0400
Message-ID: <8848.000518@nlr.ru> (raw)

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





Message-ID: <8848.000518@nlr.ru>
Permalink:  ../8848.000518@nlr.ru/
Also on:    postgresql.org/message-id/8848.000518@nlr.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gav@nlr.ru
  Subject: Re: indecies are not used by '<=' operator on varchar fields
  In-Reply-To: <8848.000518@nlr.ru>
  References: <8848.000518@nlr.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox