From: David Durham <ddurham@vailsys.com>
To: pgsql-sql@postgresql.org
Subject: Index lookup on > and < criteria
Date: Tue, 01 Nov 2005 12:18:19 -0600
Message-ID: <4367B16B.2050003@vailsys.com> (raw)
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ddurham@vailsys.com
Subject: Re: Index lookup on > and < criteria
In-Reply-To: <4367B16B.2050003@vailsys.com>
* 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