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: 40DAFBB2.4090202@archonet.com (view raw or flat)
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-2014 The PostgreSQL Global Development Group