From: Michael Fuhr <mike@fuhr.org>
To: Antony Paul <antonypaul24@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Checking = with timestamp field is slow
Date: Fri, 5 Nov 2004 01:34:01 -0700
Message-ID: <20041105083401.GA44953@winnie.fuhr.org> (raw)
In-Reply-To: <2989532e0411042316566b35f4@mail.gmail.com>
References: <2989532e0411042316566b35f4@mail.gmail.com>
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/
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-performance@postgresql.org
Cc: mike@fuhr.org, antonypaul24@gmail.com
Subject: Re: Checking = with timestamp field is slow
In-Reply-To: <20041105083401.GA44953@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