Thread

1. query planner oddity for data constant vs current_date

   Bryan White <bryan@arcamax.com> — 2002-08-16T17:22:08Z

   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.
   
   
   
   

2. Re: query planner oddity for data constant vs current_date

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T18:41:06Z

   "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