Queries not always using index on timestamp search

[Mike Nolan]

Here are two queries (under 7.4.1):

'mytime' is a timestamp field that is indexed.

select * from mytable where mytime > '2004-06-21'

select * from mytable where mytime > current_date-3

Looking at an explain on these queries, the first one will use the
index and the second one will not, even though 'current_date-3'
produces the same date as the hard-coded one in the first search.

Is there a way to get the second query to use the index?
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

Mike Nolan wrote:

Here are two queries (under 7.4.1):

'mytime' is a timestamp field that is indexed.

select * from mytable where mytime > '2004-06-21'

select * from mytable where mytime > current_date-3

Looking at an explain on these queries, the first one will use the
index and the second one will not, even though 'current_date-3'
produces the same date as the hard-coded one in the first search.

Is there a way to get the second query to use the index?

Probably the simplest is to define a function ago(int) that does the
calculation for you. Mark the function STABLE and that should do it. See
the archives for more discussion of this.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)