Re: Checking = with timestamp field is slow

On Fri, Nov 05, 2004 at 12:46:20PM +0530, Antony Paul wrote:

> I have a table which have more than 200000 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today. Is there any way to optimise it.

Is the today column a TIMESTAMP as the subject implies? If so then
your queries probably aren't using the index because you're changing
the type to something that's not indexed. Your queries should speed
up if you create an index on DATE(today):

CREATE INDEX foo_date_today_idx ON foo (DATE(today));

After creating the new index, use WHERE DATE(today) = '2004-11-05'
in your queries. EXPLAIN ANALYZE should show that the index is
being used.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/