Re: query planner oddity for data constant vs current_date

"Bryan White" <bryan(at)arcamax(dot)com> writes:
> I have noted that selects base on a date literal are perform differently
> than selects based on the CURRENT_DATE. For example:
> ------
> explain select count(*) from orders where shipdate = current_date;
> Aggregate (cost=98881.63..98881.63 rows=1 width=0)
> -> Seq Scan on orders (cost=0.00..98877.02 rows=1843 width=0)

This is fixed in current sources (7.3-to-be). The problem in prior
releases is that current_date is not a constant, and the planner is
not smart enough to understand that it's safe to use an indexscan
anyway.

You can get around this if you need to, using a kluge: make a function
that is marked isCachable and just returns current_date. The isCachable
marking is a complete lie, of course, but as long as you only use the
trick in interactive queries you can get away with it. (Don't try it in
plgsql, unless you use EXECUTE to prevent caching of the query plan.)

There are related examples and more complete discussion in the archives.

regards, tom lane