Skip site navigation (1) Skip section navigation (2)

Re: Queries not always using index on timestamp search

From: Richard Huxton <dev(at)archonet(dot)com>
To: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
Cc: pgsql general list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Queries not always using index on timestamp search
Date: 2004-06-24 16:05:06
Message-ID: (view raw or whole thread)
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Dennis GearonDate: 2004-06-24 16:06:44
Subject: Re: unicode and sorting(at least)
Previous:From: lecDate: 2004-06-24 16:01:05
Subject: Dump / restore for optimization?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group