Re: Index on timestamp field, and now()

Hello,

> > I have quite interesting problem. I have a table with a timestamp field.
> > I have an index on it. When I use constant date in where clause,
> > PostgreSQL uses index. But when I try to use now() there, it uses a
> > sequence scan. As far as I can understand in inside any query now() is a
> > constant. What is the problem here.
>
> You did not specify what version of PostgreSQL you are running, but it
> may be that now() is returning abstime, not timestamp.

Oops. Sorry. 7.2.

> Use the constant "timestamp 'now'" instead; seems to work for me in
> PgSQL 7.1.

No luck.

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE:  QUERY PLAN:

Seq Scan on queue  (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

Although exact search uses index scan:

webmailstation=> explain select * from queue where send_date = timestamp
'now';
NOTICE:  QUERY PLAN:

Index Scan using queue_senddate_key on queue  (cost=0.00..5.95 rows=1
width=190)

EXPLAIN

--
Denis