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

Re: Should the optimizer optimize "current_date - interval '1 days'" (fwd)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ron Mayer <ron(at)intervideo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Should the optimizer optimize "current_date - interval '1 days'" (fwd)
Date: 2002-06-03 23:33:41
Message-ID: 12951.1023147221@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Ron Mayer <ron(at)intervideo(dot)com> writes:
>     where dat > (current_date - interval '1 days');
> never uses the index I have on "fact".

I suppose dat is of type date?

> Should it treat my current_dat... expression as a constant and use
> the index?  Or is there a good reason it doesn't?

You will never get an indexscan out of that because the expression
seen by the planner is

	where timestamp(dat) > timestamp-expression

which is not compatible with an index of datatype date.  You should
write something that yields a date, not a timestamp, for example

	where dat > (current_date - 1)

This should be indexable (and is, in current development sources)
but in 7.2 and before you have to do additional pushups because
the planner doesn't understand that current_date can be treated
as a constant for the duration of a single indexscan.  The standard
workaround is to create a function of a signature like
"days_ago(int) returns date" and mark it isCachable.  This is a cheat
but it works fine in interactive queries.  See past discussions in
the archives.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-06-04 23:21:05
Subject: Re: Transaction isolation with concurrent increments
Previous:From: Nico ErichsenDate: 2002-06-03 19:55:30
Subject: Transaction isolation with concurrent increments

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