Index lookup on > and < criteria

From: David Durham <ddurham(at)vailsys(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Index lookup on > and < criteria
Date: 2005-11-01 18:18:19
Message-ID: 4367B16B.2050003@vailsys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2005-11-01 19:12:00 Re: Index lookup on > and < criteria
Previous Message Jim C. Nasby 2005-11-01 15:15:04 Re: A Not Join