Re: query for a time interval

On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote:
> On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:
> > SELECT id
> > FROM mq
> > WHERE now - start_date > time_to_live;
>
> The problem is you can't use an index on this, because you'd need to
> index on (now() - start_date), which obviously wouldn't work. Instead,
> re-write the WHERE as:
>
> WHERE start_date < now() - time_to_live

Unless I'm missing something that wouldn't use an index either,
because the planner wouldn't know what value to compare start_date
against without hitting each row to find that row's time_to_live.
But something like this should be able to use an expression index
on (start_date + time_to_live):

WHERE start_date + time_to_live < now()

--
Michael Fuhr