From: Michael Fuhr <mike@fuhr.org>
To: David Durham <ddurham@vailsys.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index lookup on > and < criteria
Date: Tue, 1 Nov 2005 12:21:23 -0700
Message-ID: <20051101192123.GA86877@winnie.fuhr.org> (raw)
In-Reply-To: <4367B16B.2050003@vailsys.com>
References: <4367B16B.2050003@vailsys.com>
On Tue, Nov 01, 2005 at 12:18:19PM -0600, David Durham wrote:
> 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.
No need to guess: run the queries with enable_seqscan disabled and
see if an index scan is indeed faster.
> select max(myTimeStamp) from myTable;
In current releases min() and max() can't use indexes; search the
archives for numerous discussions of the reasons. The workarounds
are, respectively:
SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp LIMIT 1;
SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp DESC LIMIT 1;
In 8.1 min() and max() are optimized to do the above.
> select * from myTable where myTimeStamp < '10/2/2005';
> select * from myTable where myTimeStamp < '10/2/2005' and myTimeStamp
> >= '10/1/2005';
How many rows do these queries return? If they return a significant
portion of the table then the planner might think that a sequential
scan would be faster than an index scan. It would be useful to see
the EXPLAIN ANALYZE output of these queries so we can see how
accurate the planner's row count estimates are.
Has the table been vacuumed and analyzed? If so, and if the planner's
row count estimates aren't close to the actual row counts, then you
might benefit from increasing the statistics target for the myTimeStamp
column.
How much memory do you have and what's your effective_cache_size
setting? That's one of the settings that influences the planner's
decision. Also, what version of PostgreSQL are you running?
BTW, pgsql-performance would be a more appropriate list to discuss
performance issues.
--
Michael Fuhr
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: mike@fuhr.org, ddurham@vailsys.com
Subject: Re: Index lookup on > and < criteria
In-Reply-To: <20051101192123.GA86877@winnie.fuhr.org>
* 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