Re: Checking = with timestamp field is slow

On Fri, Nov 05, 2004 at 05:32:49PM +0900, Michael Glaesemann wrote:
>
> On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
>
> >
> >On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
> >>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.
> >
> >I'm sure others out there have better ideas, but you might want to try
> >
> >where current_date = date '2004-11-05'
>
> Ach! just re-read that. today is one of your columns! Try
>
> where today::date = date '2004-11-05'

Casting '2004-11-05' to DATE shouldn't be necessary, at least not
in 7.4.5.

test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE today::DATE = '2004-11-05';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..268.00 rows=50 width=16) (actual time=0.592..50.854 rows=1 loops=1)
Filter: ((today)::date = '2004-11-05'::date)

As you can see, '2004-11-05' is already cast to DATE. The sequential
scan is happening because there's no index on today::DATE.

test=> CREATE INDEX foo_date_idx ON foo (DATE(today));
CREATE INDEX
test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE DATE(today) = '2004-11-05';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using foo_date_idx on foo (cost=0.00..167.83 rows=50 width=16) (actual time=0.051..0.061 rows=1 loops=1)
Index Cond: (date(today) = '2004-11-05'::date)

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