Thread: query planner oddity for data constant vs current_date

I have a table with an index on 'shipdate'.  There is about 1.5Million
records in this table with on the order of 1000 to 2000 for any given date.
The table is vacuum analysed nightly.

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)

explain select count(*) from orders where shipdate = '8/16/2002';
Aggregate  (cost=1760.87..1760.87 rows=1 width=0)
  ->  Index Scan using iordshipdate on orders  (cost=0.00..1756.44 rows=1770
width=0)
------

In the first case the qquery will take about 30 seconds to run.  In the
second case the query takes less than a second.

---------
Bryan White
This email represents the consensus opinion
of the many voices in my head.

"Bryan White" <bryan@arcamax.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