Message-ID: <4367B16B.2050003@vailsys.com>
Date: Tue, 01 Nov 2005 12:18:19 -0600
From: David Durham <ddurham@vailsys.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050404)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Index lookup on > and < criteria
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Apologies if this questions is asked often.  I'm doing some select 
statements based on a timestamp field.  I have an index on the field, 
and when I use the '=' operator the index is used.  However, if I use 
the '>' or '<' operators, then it does a full table scan.  I've got 
around 6 million rows, so I would think that an index scan would be more 
appropriate.


Here are the statements I'm looking at:

    select * from myTable where myTimeStamp = '10/1/2005';

uses an index.

    select max(myTimeStamp) from myTable;
    select * from myTable where myTimeStamp < '10/2/2005';
    select * from myTable where myTimeStamp < '10/2/2005' and myTimeStamp
        >= '10/1/2005';

do not use indexes.  Can anyone point me to some info about what's going 
on?  I've started reading through the manual (chapter 13) which I think 
explains query optimizing, index usage etc.  It seems like this would be 
a common enough problem that it would have a relatively simple solution. 
  Thanks.


-Dave